Import XML as text on Mac version

[jlduerr]

Hey all,

My team has an Excel report that we generate from a macro that is importing an XML file. However, my team also works on Macs and the macro does not run properly without opening the file in a virtual emulator with Windows. They have been creating reports via this method for years..

Anyways, I've been searching for a better way and noticed the Mac version of Excel does not have the Import XML functionality. I did, however, notice that the Mac version of Excel will treat an XML file as a text file and the data could still be imported accordingly. I've created a brand new macro to attempt this method (code below). This newly created macro runs properly on my Mac and virtual Windows and the others' virtual Windows versions. However, this macro does not run properly on the others' Mac versions. It will come up with an error and the debug highlights the .Refresh BackgroundQuery:=False line.

Does anyone know what might be causing this? It would be great have this working on everyone's Macs.

Sub ImportText()

Dim FilePath As String
Dim FolderPath As String
FolderPath = ThisWorkbook.Path
FilePath = FolderPath & "/ReportInfo.xml"

Worksheets("Data").Activate
 Range("B1").Select
    Application.CutCopyMode = False
    With Sheets("Data").QueryTables.Add(Connection:= _
        "TEXT;" & FilePath, Destination:=Range( _
        "$B$1"))
        .Name = "PressReportInfo_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileFixedColumnWidths = Array(100)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("C1").Select
End Sub

 

[jlduerr]

I wonder if the "ThisWorkbook.Path" command functions differently between Windows versus Mac?