Maintain text qualifier while creating a Web Query on MAC

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
274
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.

Code:
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
Code:
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"
 

Forum statistics

Threads
1,078,462
Messages
5,340,456
Members
399,376
Latest member
Tresfjording

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top