Pulling data from csv and place on worksheet without using clipboard?

[DavMet]

Have a file for work that we need to try and be able to have automatically update even if the computer it is running on is locked which means we have to bypass the clipboard for pretty much everything. Most of the data sources are urls that generate a csv for us using variables we pass to it. The code I have works under normal use (ie unlocked computer) but I am unsure if it is possible to bypass the clipboard when pulling from a csv file.

Any help would be appreciated.

Private FC As String
Private StartTime As Date, EndTime As Date
Private sheet As Worksheet
Private Dte As String
Option Explicit


Sub GetLPDensity()
        Dim H As XMLHTTP60
        Dim DataObj As New MSForms.DataObject
        Set PerfTrack = ThisWorkbook.Worksheets("Psheet")
        StartTime = starttime
        EndTime = endtime
        Dte = Format(date from cell, "YYYY-MM-DD")
        FC = ID of building

        Dim URL As String

        URL = "" 'link to pull csv from, not a directlink to csv file
        
Debug.Print URL
        launchLPD (URL)

End Sub


Sub launchLPD(ByRef URL As String)
Dim httpRequest As XMLHTTP60
Dim DataObj As New MSForms.DataObject

    Set httpRequest = New XMLHTTP60
        httpRequest.Open "GET", URL, False
        httpRequest.setRequestHeader "Content-Type", "application/x-w&ww-form-urlencoded"
        httpRequest.Send ""

    DataObj.SetText httpRequest.ResponseText
    DataObj.PutInClipboard

    With PasteRange
        .Activate
        .UsedRange.ClearContents
        .Cells(1, 1).Select
        .PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
        If .Range("A1") = "X" Then GoTo skipT2C
        .Range("A:A").Select
    End With

    Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
        Tab:=True, Semicolon:=False, comma:=True, Space:=False, Other:=False, OtherChar:="-"
   Exit Sub

skipT2C:

End Sub

 

[alansidman]

You could also use Power Query to bring the csv file into PQ Editor, clean the file so only the data you want. (No coding as can be done in UI). Close and load your output to your existing Excel file.