batch opening CSV and saving CSV

[ssbbg]

Hello,
I am a complete novice to excel macros, and I would appreciate some help with a simple problem.

An instrument in our lab saves its data as CSV files. These are openable in MS Excel, but not other programs we use for data analysis (such as Matlab or Origin). If I open the file in excel, then save it (still as a csv) (though excel seems to default to unicode text when you go to resave it) it magically becomes readable by our other programs. I would like write a macro to open all files and resave them as CSV. All my data files have the format 1letters#.csv. So I can tell if the files have been resaved, I would like to save the new files as "A*.csv". So the original file might be 1abcA1.csv. After it has been resaved, it should be A1abcA1.csv.

I found a thread here: http://www.excelforum.com/excel-gene...rnal-data.html

which looks like it should be relevant. But, I think the loop is using the extension as the condition- since I am saving the file under the same extension, I think maybe it will cause an endless loop. However, I don't get that far. The modified code won't even compile.

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim sFile As String
    Dim spath As String
    Dim wB As Workbook
    Dim wS As Worksheet
    
    spath$ = "C:\Users\User\Desktop\1LES95\"
    Set wB = Workbook.Add
    Set wS = wB.Sheets(1)
    sFile$ = Dir(spath & "1*.csv")
    
    Do Until sFile = ""
           Do Until sFile = ""
      If Right(sFile, 4) <> ".csv" Then
         With wB.Sheets(1).QueryTables.Add(Connection:= _
            "TEXT;" & spath & sFile _
            , Destination:=Range("A1"))
            .Name = sFile
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileFixedColumnWidths = Array(16, 16)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
         End With
      
         wB.SaveAs Filename:=spath & & 'A' & sFile & ".csv" _
            , FileFormat:=xlCSV, CreateBackup:=False
         
         Application.DisplayAlerts = False
         wS.Cells.ClearContents
         Application.DisplayAlerts = True
      End If
      sFile$ = Dir
      Loop
   End Sub

When I record a macro to open and resave a single file, it looks very different from the sample code given in the previous thread. This is what I get:

Sub Macro3()
'
' Macro3 Macro
'

'
    Workbooks.OpenText Filename:="C:\Users\User\Desktop\1LES95\1LES95B1.CSV", _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    ActiveWorkbook.SaveAs Filename:="C:\Users\User\Desktop\1LES95\A1LES95B1.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False
End Sub

I'm not sure what the difference is between OpenText and QueryTables.Add. I'm using excel 2007. The code in the thread above was for 2003. Is that the difference?

Thanks for your help.
ssbbg