Code to format Excel works for first file but not for second

Question

I have three Excel files that are very similar in content and structure. There are a number of formatting steps that I need to apply to the files before I can work with them. Using Access VBA, I have developed some code to delete certain rows and to perform the TextToColumns function on the data.

This code works fine for the first spreadsheet. However, I see this error for the second:

Run-time error '91': Object Variable or With block variable not set

This occurs at the Selection.TextToColumns ... line of the code (i.e. the actions above it perform ok for the second spreadsheet).

Any ideas as to why this is happening?

After reviewing this post, I though maybe I need to add With before the TextToColumns or maybe define the range in a different way. But there my lack of VBA knowledge became very apparent!

Private Sub FormatFile(sPath As String)

    Dim oExcel As Excel.Application
    Dim owb As Workbook
    Dim oWS As Worksheet

    Dim sFile As String
    Dim sDirectory As String
    Dim rng As Range

    Set oExcel = New Excel.Application
    Set owb = oExcel.Workbooks.Open(sPath)
    Set oWS = owb.Sheets(1)
    Debug.Print oWS.Name

    oExcel.Visible = False

    DoCmd.SetWarnings False

    sDirectory = fGetDirectory(sPath)
    sFile = fGetFileName(sPath)

        oWS.Rows(5).Delete
        oWS.Rows(3).Delete
        oWS.Rows(2).Delete
        oWS.Rows(1).Delete
        DeleteLastRow oWS.Columns("A")

          oWS.Columns("A:A").Select
        Selection.TextToColumns Destination:=oWS.Range("A1"), DataType:=xlDelimited, TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True                   


    owb.Save
    owb.Close
    oExcel.Quit
    Set owb = Nothing
    Set oWS = Nothing
    Set rng = Nothing

    Debug.Print "created file " & sFile

    DoCmd.SetWarnings True

    Set oExcel = Nothing

End Sub

It might help - you can remove the need for `.Select` completely (which is good practice) by just using the line `oWS.Columns("A:A").TextToColumn Destination:=oWS.Range("A1")...` That change might help fix the issue. Also, perhaps you could show some sample data from your worksheet that this *does* work on, and then some sample data for the one that *doesn't* work. — BruceWayne, Oct 07 '15 at 17:09
@BruceWayne - thank you. That did the trick. Code works perfectly now. — mccdo, Oct 07 '15 at 17:38
Great! I added as an answer, and included a link on how/why to avoid using `.Select`, which is worth reading through. — BruceWayne, Oct 07 '15 at 17:42

score 1 · Accepted Answer · edited May 23 '17 at 12:22

It could have something to do with the use of .Select (which is recommended you avoid using).

Change your lines

oWS.Columns("A:A").Select
        Selection.TextToColumns Destination:=oWS.Range("A1"), DataType:=xlDelimited, TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

to

oWS.Columns("A:A").TextToColumns Destination:=oWS.Range("A1"), DataType:=xlDelimited, TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

What could be happening is that somehow the selection becomes unselected, so when going to use the selection, it's not "there".

Code to format Excel works for first file but not for second

1 Answers1