Maintain text qualifier while creating a Web Query on MAC

[MoshiM]

There's a Web Query in my workbook created on Windows that doesn't work if the workbook is opened on MAC and results in an error if trying to refresh it via vba and does nothing otherwise.

I came across a variant of the following code on stack overflow which works to import the data from the Web into excel while on MAC. The problem is that the Queried Webpage uses quotation marks to denote text and then delimits with a comma to separate values. However there are values like "My, String" within the data but the code below doesn't maintain those Quotation marks when importing, so some of the fields end up in the wrong columns.


Note: I have a function to replace commas not within quotation marks for use as a delimiter.

Sub Web_Query_MAC()


Dim TS As Worksheet


Application.EnableEvents = False


Const URL As String = "https://www.cftc.gov/dea/newcot/deacom.txt"


Set TS = ThisWorkbook.Worksheets.Add


With TS.QueryTables.Add(Connection:="URL;" & URL, Destination:=TS.Range("A1"))


        .RefreshStyle = xlOverwriteCells
        
        .BackgroundQuery = False


'
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebSelectionType = xlEntirePage
        
        .Refresh
        
        .SaveData = False


End With


Application.EnableEvents = True


End Sub

Formula for the Query on Windows

let
    Source = Csv.Document(Web.Contents("http://www.cftc.gov/dea/newcot/deacom.txt"),[Delimiter=",", Columns=129, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type date}, {"Column4", type text}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", Int64.Type}, {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", Int64.Type}, {"Column32", Int64.Type}, {"Column33", Int64.Type}, {"Column34", Int64.Type}, {"Column35", Int64.Type}, {"Column36", Int64.Type}, {"Column37", Int64.Type}, {"Column38", type
 text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type number}, {"Column49", type number}, {"Column50", type number}, {"Column51", type number}, {"Column52", type number}, {"Column53", type number}, {"Column54", type number}, {"Column55", type number}, {"Column56", type number}, {"Column57", type number}, {"Column58", type number}, {"Column59", type number}, {"Column60", type number}, {"Column61", type number}, {"Column62", type number}, {"Column63", type number}, {"Column64", type number}, {"Column65", type number}, {"Column66", type number}, {"Column67", type number}, {"Column68", type number}, {"Column69", type number}, {"Column70", type number}, {"Column71", type number}, {"Column72", type number}, {"Column73", type number}, {"Column74", type number}, {"Column75", type number}, {"Column76", type number}, {"Colum
n77", type number}, {"Column78", Int64.Type}, {"Column79", Int64.Type}, {"Column80", Int64.Type}, {"Column81", Int64.Type}, {"Column82", Int64.Type}, {"Column83", Int64.Type}, {"Column84", Int64.Type}, {"Column85", Int64.Type}, {"Column86", Int64.Type}, {"Column87", Int64.Type}, {"Column88", Int64.Type}, {"Column89", Int64.Type}, {"Column90", Int64.Type}, {"Column91", Int64.Type}, {"Column92", Int64.Type}, {"Column93", Int64.Type}, {"Column94", Int64.Type}, {"Column95", Int64.Type}, {"Column96", Int64.Type}, {"Column97", Int64.Type}, {"Column98", Int64.Type}, {"Column99", Int64.Type}, {"Column100", Int64.Type}, {"Column101", Int64.Type}, {"Column102", type number}, {"Column103", type number}, {"Column104", type number}, {"Column105", type number}, {"Column106", type number}, {"Column107", type number}, {"Column108", type number}, {"Column109", type number}, {"Column110", type number}, {"Column111", type number}, {"Column112", type number}, {"Column113", type number}, {"Column114", type number}, {"Column115",
 type number}, {"Column116", type number}, {"Column117", type number}, {"Column118", type number}, {"Column119", type number}, {"Column120", type number}, {"Column121", type number}, {"Column122", type number}, {"Column123", type number}, {"Column124", type number}, {"Column125", type number}, {"Column126", type text}, {"Column127", type text}, {"Column128", type text}, {"Column129", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1", "Column3", "Column4", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Column3", "Column1", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column4"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Column1", Order.Ascending}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Column38", Int64.Type}, {"Column39", Int64.Type}, {"Column40", Int64.Type}, {"Column41", Int64.Type}, {"Column42", Int64.Type}, {"Column43", Int64.Type}, {"Column44", Int64.Type}, {"Column45", Int64.Type}, {"Column46", Int64.Type}, {"Column47", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column3", "As of Date in Form YYYY-MM-DD"}, {"Column1", "Market and Exchange Names"}, {"Column8", "Open Interest (All)"}, {"Column9", "Noncommercial Positions-Long (All)"}, {"Column10", "Noncommercial Positions-Short (All)"}, {"Column11", "Noncommercial Positions-Spreading (All)"}, {"Column12", "Commercial Positions-Long (All)"}, {"Column13", "Commercial Positions-Short (All)"}, {"Column14", "Total Reportable Positions-Long (All)"}, {"Column15", "Total Reportable Positions-Short (All)"}, {"Column16", "Nonreportable Positions-Long (All)"}, {"Column17", "Nonreportable Positions-Short (All)"}, {"Column38", "Change in Open Interest (All)"}, {"Column39", "Change in Noncommercial-Long (All)"}, {"Column40", "Change in Noncommercial-Short (All)"}, {"Column41", "Change in Noncommercial-Spreading (All)"}, {"Column42", "Change in Commercial-Long (All)"}, {"Column43", "Change in Commercial-Short (All)"}, {"Column44", "Change in Total Reportable-Long (All)"
}, {"Column45", "Change in Total Reportable-Short (All)"}, {"Column46", "Change in Nonreportable-Long (All)"}, {"Column47", "Change in Nonreportable-Short (All)"}, {"Column48", "% of Open Interest (OI) (All)"}, {"Column49", "% of OI-Noncommercial-Long (All)"}, {"Column50", "% of OI-Noncommercial-Short (All)"}, {"Column51", "% of OI-Noncommercial-Spreading (All)"}, {"Column52", "% of OI-Commercial-Long (All)"}, {"Column53", "% of OI-Commercial-Short (All)"}, {"Column54", "% of OI-Total Reportable-Long (All)"}, {"Column55", "% of OI-Total Reportable-Short (All)"}, {"Column56", "% of OI-Nonreportable-Long (All)"}, {"Column57", "% of OI-Nonreportable-Short (All)"}, {"Column4", "CFTC_Contract_Market_Code"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"CFTC_Contract_Market_Code"}, Table_WSN, {"CFTC_Contract_Market_Code"}, "Table_WSN", JoinKind.Inner)
in
    #"Merged Queries"