CODEWITHSUNDEEP
excelvbaloopsfor-loop
Matt G
Matt G

Reputation: 77

Convert multiple csv files to tab delimited

I need to save a selection of .csv files, under the same name, as tab delimited files.

I have the following code to select the files, specify the delimiter, and then iterate through the files, opening and saving them one by one.

The issue is the iteration.

Sub sandbox2()

    Dim xFilesToOpen As Variant
    Dim i As Integer
    Dim xWb As Workbook
    Dim xTempWb As Workbook
    Dim xDelimiter As String
    Dim xScreen As Boolean
    Dim strFileFullName As String

    On Error GoTo ErrHandler
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False

    xFilesToOpen = Application.GetOpenFilename("Text Files (*.*), *.*", , , , True)
    xDelimiter = Application.InputBox("Delimiter: ", , ",")

    If TypeName(xFilesToOpen) = "Boolean" Then
        MsgBox "No files were selected", , "Sorry dude"
        GoTo ExitHandler
    End If

    For i = 1 To UBound(xFilesToOpen)
        Set xTempWb = Workbooks.Open(xFilesToOpen(i))
        strFileFullName = Replace(ActiveWorkbook.FullName, ".csv", "")
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=strFileFullName + ".txt", FileFormat:=xlText, CreateBackup:=False
        xTempWb.Close False
        Application.DisplayAlerts = True
        ActiveWorkbook.Close savechanges:=False
        Set xTempWb = Nothing
    Next i

ExitHandler:
    Application.ScreenUpdating = xScreen
    Set xWb = Nothing
    Set xTempWb = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, , "error"
    Resume ExitHandler
End Sub

This code will work for the first file, but then I get the error

"Object variable or With block variable not set"

When looking at the folder, the first file is saved as a .txt file, but only the first.

Upvotes: 0

Views: 809

Answers (1)

DisplayName
DisplayName

Reputation: 13386

You'd better not rely on any active object (Workbook, in this case) and reference a proper object instance, instead

also, you should handle Application.DisplayAlerts by setting it to False before entering files loop and moving its setting back to True in the ExitHandler block and make sure you always set it should any error arise

so I'd go as follows:

Sub sandbox2()

    Dim xFilesToOpen As Variant
    Dim i As Long
    Dim xScreen As Boolean

    On Error GoTo ErrHandler
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False

    xFilesToOpen = Application.GetOpenFilename("Text Files (*.*), *.*", , , , True)
    xDelimiter = Application.InputBox("Delimiter: ", , ",")

    If TypeName(xFilesToOpen) = "Boolean" Then
        MsgBox "No files were selected", , "Sorry dude"
    Else
        Application.DisplayAlerts = False ' set it to 'False' only once before entering the loop
        For i = 1 To UBound(xFilesToOpen)
            With Workbooks.Open(xFilesToOpen(i)) ' open a workbook with current name and reference it
                .SaveAs Filename:=Replace(.FullName, ".csv", "") + ".txt", FileFormat:=xlText, CreateBackup:=False ' act on referenced workbook properties
                .Close False ' close referenced workbook
            End With
        Next i
    End If

ExitHandler:
    Application.DisplayAlerts = True ' be sure to set it back to 'True' should any error take you out of your loop
    Application.ScreenUpdating = xScreen
    Exit Sub
ErrHandler:
    MsgBox Err.Description, , "error"
    Resume ExitHandler
End Sub

please note I got rid of

Dim xTempWb As Workbook ' no longer used

Dim xWb As Workbook ' never used
....
Set xWb = Nothing ' never set

Dim xDelimiter As String ' never used
....
xDelimiter = Application.InputBox("Delimiter: ", , ",") ' never used

and used Dim i as Long, as a general rule not to incur in any overflow error since an integers can reach up to some 32000 value or so

Upvotes: 2

Related Questions