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

 

[Jim Gordon]

Any chance you can provide a download link to an example data set .xml file?

 

[jlduerr]

Any chance you can provide a download link to an example data set .xml file?

Josh D
Test
Pilot
AE10001_001_Test Pilot
37.5
100
GC-2 POLYLITH
CMYK
AE10001

Test Pilot
Tag
Pantone 186 C, Pantone Black C
1
0
1
0
1
0
2000_S_F
2000_S_B

 

[RoryA]

Which versions of macOS and Office are on the Macs where it doesn't work?

 

[Jim Gordon]

Josh D
Test
Pilot
AE10001_001_Test Pilot
37.5
100
GC-2 POLYLITH
CMYK
AE10001

Test Pilot
Tag
Pantone 186 C, Pantone Black C
1
0
1
0
1
0
2000_S_F
2000_S_B

Hi

Thanks for providing the end result. I was hoping for an actual .xml file to test with. You may have to put it onto a sharing service such as OneDrive or DropBox and provide the share link in a reply here. Thank you,

 

[jlduerr]

Hi

Thanks for providing the end result. I was hoping for an actual .xml file to test with. You may have to put it onto a sharing service such as OneDrive or DropBox and provide the share link in a reply here. Thank you,

Try this

Microsoft OneDrive

1drv.ms

 

[jlduerr]

Which versions of macOS and Office are on the Macs where it doesn't work?

Same OS currently on Catalina. Same Excel version 16.52

 

[Jim Gordon]

Hi again,

I am able to confirm the error is generated at the RefreshBackground command.

When adding the querytable on the Mac, this works:

Sheets("Data").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/JBG/Desktop/ReportInfo.xml", Destination:=Range("$B$1"))
        .Name = "ReportInfo_2"

 

[Jim Gordon]

Try changing the way you add the querytable, and adjust the Fixed Column Widths accordingly.

This example works:

Sub Macro3()


    Sheets("Data").Select
    Range("B1").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/JBG/Desktop/ReportInfo.xml", Destination:=Range("$B$1"))
        .Name = "ReportInfo_4"
        .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(33)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[jlduerr]

Try changing the way you add the querytable, and adjust the Fixed Column Widths accordingly.

This example works:

Sub Macro3()


    Sheets("Data").Select
    Range("B1").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/JBG/Desktop/ReportInfo.xml", Destination:=Range("$B$1"))
        .Name = "ReportInfo_4"
        .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(33)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

This works, however, the issue that I am having is that I would need the folder path to be dynamic rather than static. I feel like the issue is related to that. Once I change the folder path to "ThisWorkbook.Path" & "/ReportInfo.xml" is where I have the issue. And the thing is I don't even have the issue. It's when another user other than me that attempts the macro.