I'm attempting to set up automated functions to automatically download daily text files received by email (done), and then import them into a very basic database. Unfortunately the txt files are tab delimited and after fruitless research into how to automate importing multiple tab delimited text files into Access, I have decided it will be easier to convert all the txt files to csv files and then import them. It makes sense to code this in Access rather than having an Excel file just to hold the code for opening and converting multiple text files, but I have some issues.
The following code works fine in Excel and does exactly what I need it to do. It opens every text file in a specified folder, one by one, converts them to csv files and saves them as such in the same folder with its same name:
Sub ConvertFiles()
Dim sName As String, sPath As String
sPath = "P:\Shared Works\Catch Reports" '<== change to reflect your folder. Make sure it ends with a "" character
sName = Dir(sPath)
Do While sName <> ""
If LCase(Right(sName, 3)) = "txt" Then
Workbooks.OpenText Filename:=sPath & sName, _
Origin:=437, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
TrailingMinusNumbers:=True[/COLOR]
ActiveWorkbook.SaveAs Filename:=sPath & Left(sName, Len(sName) - 4) & ".csv", _
FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
End If
sName = Dir()
Loop
Kill "P:\Shared Works\Catch Reports\*.txt"
End Sub
However when I tried to adapt it to work with Access, it didn't work. The issue is in the formatting "sub" commands, because if I removed all of them the code runs without error. It opens each file and saves it with a csv extension but they haven't been converted:
Public Function runExcelMacro2()
Dim MyXL As Object
Dim sName As String, sPath As String
Set MyXL = CreateObject("Excel.Application")
sPath = "P:\Shared Works\Catch Reports" '<== change to reflect your folder. Make sure it ends with a "" character
sName = Dir(sPath)
With MyXL
Do While sName <> ""
If LCase(Right(sName, 3)) = "txt" Then
.Workbooks.Open FileName:=sPath & sName, _
Origin:=437, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
TrailingMinusNumbers:=True
.ActiveWorkbook.SaveAs FileName:=sPath & Left(sName, Len(sName) - 4) & ".csv", _
FileFormat:=xlCSV, _
CreateBackup:=False
.ActiveWorkbook.Close SaveChanges:=False
End If
sName = Dir()
Loop
End With
Kill ("P:\Shared Works\Catch Reports\*.txt")
End Function
Can anyone advise what the issue might be and how to fix it? Would appreciate any advice!