excelvbscript

No table to run vbs script to convert xlsx to tab delimited


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

Solution

  • Convert Single-Worksheet Excel Files to Tab Delimited

    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