I have a script that is supposed to convert xlsx files to tab delimited files in a folder. When I run it gives me a "excepted ')' at the following line by FileFormat
Call oWSH.SaveAs (oFile.Path & ".txt", FileFormat:=xlText)
I am very new to scripting and would like to some help on solving this issue if possible:
Full code is below:
Option Explicit
Dim oFSO, myFolder
Dim xlText
myFolder="C:\Projects\scripts\test"
Set oFSO = CreateObject("Scripting.FileSystemObject")
xlText = -4158 'Excel txt format enum
Call ConvertAllExcelFiles(myFolder)
Set oFSO = Nothing
Call MsgBox ("Done!")
Sub ConvertAllExcelFiles(ByVal oFolder)
Dim targetF, oFileList, oFile
Dim oExcel, oWB, oWSH
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = False
Set targetF = oFSO.GetFolder(oFolder)
Set oFileList = targetF.Files
For Each oFile in oFileList
If (Right(oFile.Name, 4) = "xlsx") Then
Set oWB = oExcel.Workbooks.Open(oFile.Path)
For Each oWSH in oWB.Sheets
Call oWSH.SaveAs (oFile.Path & ".txt", FileFormat:=xlText)
Next
Set oWSH = Nothing
Call oWB.Close
Set oWB = Nothing
End If
Next
Call oExcel.Quit
Set oExcel = Nothing
End Sub
Updated code after suggestions:
Option Explicit
Dim oFSO, myFolder
Dim xlText
myFolder="\\10.0.254.21\sftp\Amazon\Outbound"
Set oFSO = CreateObject("Scripting.FileSystemObject")
xlText = -4158 'Excel txt format enum
ConvertAllExcelFiles(myFolder)
Set oFSO = Nothing
MsgBox ("Done!")
Sub ConvertAllExcelFiles(ByVal oFolder)
Dim targetF, oFileList, oFile
Dim oExcel, oWB, oWSH
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = False
Set targetF = oFSO.GetFolder(oFolder)
Set oFileList = targetF.Files
For Each oFile in oFileList
If (Right(oFile.Name, 4) = "xlsx") Then
Set oWB = oExcel.Workbooks.Open(oFile.Path)
For Each oWSH in oWB.Sheets
oWSH.SaveAs oFile.Path & ".txt", xlText
Next
Set oWSH = Nothing
oWB.Close
Set oWB = Nothing
End If
Next
oExcel.Quit
Set oExcel = Nothing
End Sub
Const FOLDER_PATH = "C:\Projects\scripts\test\" ' requires trailing path del.
Const SRC_FILE_EXTENSION = "xlsx"
Const TGT_FILE_EXTENSION = "txt"
Const TGT_FILE_FORMAT = -4158 ' xlCurrentPlatformText, xlText
ConvertAllExcelFiles FOLDER_PATH, SRC_FILE_EXTENSION, _
TGT_FILE_EXTENSION, TGT_FILE_FORMAT
Sub ConvertAllExcelFiles(FolderPath, SourceFileExtension, _
TargetFileExtension, TargetFileFormat)
Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(FolderPath) Then
MsgBox "The folder """ & FolderPath & """ doesn't exist!", vbExclamation
Exit Sub
End If
Dim fsoFolder: Set fsoFolder = fso.GetFolder(FolderPath)
Dim fsoFiles: Set fsoFiles = fsoFolder.Files
Dim xlApp: Set xlApp = CreateObject("Excel.Application") ' a new instance
'xlApp.Visible = True ' default is 'False'; uncomment when developing
Dim wb, fsoFile, FilesCount
Dim SourceFileName, TargetFilePath, FileExtension, FileBaseName
For Each fsoFile In fsoFiles
SourceFileName = fsoFile.Name
FileExtension = fso.GetExtensionName(SourceFileName)
If StrComp(FileExtension, SourceFileExtension, vbTextCompare) = 0 Then
Set wb = xlApp.Workbooks.Open(fsoFile.Path)
FileBaseName = fso.GetBaseName(SourceFileName)
TargetFilePath = FolderPath & FileBaseName _
& "." & TargetFileExtension
xlApp.DisplayAlerts = False ' overwrite without confirmation
wb.SaveAs TargetFilePath, TargetFileFormat
xlApp.DisplayAlerts = True
wb.Close False ' close without saving changes; just got saved
FilesCount = FilesCount + 1
End If
Next
xlApp.Quit
MsgBox FilesCount & " worksheets exported.", vbInformation
End Sub