I have a macro that I'm trying to use to pull .csv data from a bookmarkable link into an Excel sheet. I've managed to get it to pull and paste the correct data. however it doesn't format correctly and instead pastes everything into column A.

Sub Data_Pull()
    Dim myurl As String
    myurl = Worksheets("Settings").Range("Y18").Value
    Sheets("Data_Pull").Visible = True

    Sheets("Data_Pull").Select
    Sheets("Data_pull").UsedRange.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" + myurl, Destination:=Range("$A$1"))
        .Name = "287%2C321%2C362%2C632%2C379%2C426%2C720&osm_ids=&oxm_ids=445%2C442&ofm_ids=&datasource_viz=nvd3Table"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = False
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    'Sheets("Data_Pull").Visible = True
    
End Sub

How can I adjust this to format the pasted data correctly? And separate the .CSV into columns correctly?

I have a macro that I'm trying to use to pull .csv data from a bookmarkable link into an Excel sheet. I've managed to get it to pull and paste the correct data. however it doesn't format correctly and instead pastes everything into column A.

Sub Data_Pull()
    Dim myurl As String
    myurl = Worksheets("Settings").Range("Y18").Value
    Sheets("Data_Pull").Visible = True

    Sheets("Data_Pull").Select
    Sheets("Data_pull").UsedRange.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" + myurl, Destination:=Range("$A$1"))
        .Name = "287%2C321%2C362%2C632%2C379%2C426%2C720&osm_ids=&oxm_ids=445%2C442&ofm_ids=&datasource_viz=nvd3Table"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = False
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    'Sheets("Data_Pull").Visible = True
    
End Sub

How can I adjust this to format the pasted data correctly? And separate the .CSV into columns correctly?

• excel
• vba
• csv
• data-import

• 2 Why you don't use powerQuery to import the csv? Data > From Text/CSV > enter url – ceci Commented Feb 23 at 16:21
• 1 Also you can probably use TextToColumns to split the data. – Evil Blue Monkey Commented Feb 23 at 21:44
• When i try from text/csv excel only lets me choose a file not a url. and when i try from web. it fails to connect. – Benjamin Calvert Commented Feb 24 at 9:19
• The macro above does retrieve and paste the correct data. it just doesn't parse it properly.. the url also will be different for each person using the file which is why i have it reference a concatented cell. because the url is something like this M.corp/reports/… and the paramters change each day , shift and location are dynamic. but that part works as it does pull and paste the data to the correct sheet. i t just doesn't parse. – Benjamin Calvert Commented Feb 24 at 9:35

1 Answer 1

Normally, when you load data from the Web using a manually generated query, it will correctly interpret the input as a CSV and seperate the columns according to the detected delimiter.

when i try from web. it fails to connect

If you have problem with connection, that's something you'd need to investigate further and provide more details if you want help regarding this. A good way to make sure that there won't be any connection error is to navigate manually to that URL.

For instance, I've added a CSV file to a GitHub repo and I was able to import the data using the following code:

Private Sub DataPull()
    
    Dim Wb As Workbook
    Set Wb = ThisWorkbook
    
    Dim CsvUrl As String
    CsvUrl = "https://raw.githubusercontent/DecimalTurn/VBA-StackOverflow-Demos/refs/heads/main/data/csv/sample_data1.csv"
    'CsvUrl = ThisWorkbook.Worksheets("Settings").Range("Y18").Value2

    'Delete query if it exists
    On Error Resume Next
        Wb.Queries.Item("sample_data1").Delete
    On Error GoTo 0
    Wb.Queries.Add Name:="sample_data1", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = Csv.Document(Web.Contents(""" & CsvUrl & """),[Delimiter="","", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & _
        "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & _
        "    #""Promoted Headers"""
    
    'Corresponding M code:
    'let
    '    Source = Csv.Document(Web.Contents("https://raw.githubusercontent/DecimalTurn/VBA-StackOverflow-Demos/refs/heads/main/data/csv/sample_data1.csv"),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    '    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
    'in
    '    #"Promoted Headers"

    Wb.Sheets("Data_Pull").Select
    Wb.Sheets("Data_pull").UsedRange.ClearContents
    
    With ActiveSheet.ListObjects.Add( _
            SourceType:=0, _
            Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=sample_data1;Extended Properties=""""", _
            Destination:=Range("$A$1") _
        ).QueryTable
        
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [sample_data1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "sample_data1"
        .Refresh BackgroundQuery:=False
        
    End With

End Sub

GitHub demo folder