While using Excel for Mac, a text import recorded macro fails when not done manually beforehand.

Sanguineo88 1 Reputation point
2021-12-06T23:41:31.467+00:00

I am using Microsoft Excel for Mac. I recorded a macro that imports a text file into a new worksheet.
I varibalized the connection filepath so that I could import multiple files given a filepath input.
I tested this out on the file that I originally used for recording the macro. Works.
I then tested it on another file and it didn't work because excel couldn't find the file I was trying to import.
I then manually imported this second file. It worked fine, so I ran the macro on it again and this time it worked.

I repeated this experiment 5 times and the results were always the same.
If I haven't manually imported or opened the file from within excel prior to trying to run this macro then it can't seem to find the file, but if I do then it works exactly as I want it to.

Is there a way of getting excel for mac to be able to import files without manually importing beforehand?

Any help would be appreciated.

Relevant code snippet:

sub importData () 
Dim filePath As String
Dim fileName As String
'A2 = folder path with the final slash'
'A3 = file name'
fileName = Worksheets("Macro Settings").Range("A3").Value
filePath = "TEXT;" & Worksheets("Macro Settings").Range("A2").Value & fileName
fileName = Replace(fileName, ".", "")
'Import data to new worksheet'
Application.CutCopyMode = False
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        filePath _
        , Destination:=Range("$A$1"))
        .Name = fileName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,311 questions
Office Mac
Office Mac
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Mac: A family of Apple personal computers that run the macOS operating system.
443 questions
0 comments No comments
{count} votes