There is an online spreadsheet that has the option to "Download Spreadsheet". When you select it, it downloads the file as a .csv to the downloads folder. I want to download this spreadsheet as part of a macro and save it to a specific folder. This is meant for multiple users, so I don't want to assume the download will go to the downloads folder for every user. I've previously used code similar to the code below (found online) with success. For this link, though, it downloads a .csv file that appears to have about 1500 lines of html code instead of the actual spreadsheet. Do you know if there is either some way to tweak the actual code to get the spreadsheet itself or any other suggestions for possibly getting a different url? There is some sensitive information, so I'm just sharing part of the "html" .csv and the relevant code I've used, but please let me know if there's any other info you need.
Sub ...()
Dim ...
DT_Url = "https://.......com/db/bpmcgi93a?a=q&qid=501&dlta=xs%7E&ridlist=697"
DTFile = "Deal_Tracker_Que_Report" & Range("Today") & ".csv"
DT_Save = ThisWorkbook.Path & "\MF Historical Queues\" & DTFile
DTFilePath = Range("Newest_Queue_Path") & DTFile
Download_Queue DT_Url, DT_Save
Set DT_Q = Workbooks.Open(DTFilePath)
ActiveSheet.Name = "DT_Que"
End Sub
Sub Download_Queue(myURL, saveFile)
Dim HttpReq As Object
Set HttpReq = CreateObject("Microsoft.XMLHTTP")
HttpReq.Open "GET", myURL, False, "username", "password"
HttpReq.Send
myURL = HttpReq.responseBody
If HttpReq.Status = 200 Then
Set oStrm = CreateObject("ADODB.Stream")
oStrm.Open
oStrm.Type = 1
oStrm.Write HttpReq.responseBody
oStrm.SaveToFile saveFile, 2 ' 1 = no overwrite, 2 = overwrite
oStrm.Close
End If
End Sub
