Thanks! Ha! Ha! Funny mistake- That was Matlab slipping through- strings are denoted with 'string' instead of "string".
I did catch that when the forum was down, but the CSV file is not opening correctly. There was also an extra loop, and workbook.add instead of workbooks.add
I have commented out the loop part, and am just trying to get the open and close part to work (from the original thread)
I now have this:
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\small loop test\"
Set wB = Workbooks.Add
Set wS = wB.Sheets(1)
' sFile$ = Dir(spath & "1*.csv")
sFile = "1les95A1.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
So, it does open and save a file, but it completely mangles it. I cannot find out how to tell it to understand that the file is comma delimited. I looked up "QueryTables.Add" and "connection" in the help, and it is pretty opaque. Nothing seems related to the setting a delimiter. All the subsequent .text stuff below the width also does not seem related.
Thanks again.
ssbbg
Bookmarks