0

I have a script which downloads and creates/opens a new Excel .csv workbook of historical stock data from Yahoo Finance. I then copy the contents of worksheet(1) from that .csv workbook and create a new worksheet (at the end) in ThisWorkbook (the one containing my macro/script) and paste the data into it. I have configured my browser (Chrome) to always open .csv file downloads. Here's some sample code:

Sub Macro1()

    Dim urlLink As String
    Dim csvWorkbook As Workbook
    
    urlLink = "https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1592179200&period2=1623715200&interval=1d&events=history&includeAdjustedClose=true"
    ActiveWorkbook.FollowHyperlink Address:=urlLink, NewWindow:=True
    Set csvWorkbook = ActiveWorkbook
    csvWorkbook.Sheets(1).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

End Sub

urlLink is an example query to download historical data from Yahoo Finance in .csv format. Set your default browser to always open files of type .csv. When ActiveWorkbook.FollowHyperlink is executed, another instance of Excel will open and AAPL.csv will open. This new AAPL.csv will be the active workbook, and then the next line of code will create a new sheet in ThisWorkbook (the workbook containing your script) and copy the contents of AAPL.csv Sheet(1) into it.

My problem is that this works fine when I single step through the code but it fails when run at full speed (i.e. not in debug mode). To clarify, assuming you start with a new blank workbook, if you single step through the above code you'll get a new sheet named AAPL added to your workbook with 253 rows of data (my desired ourcome), but if you run it full speed you'll get a new blank sheet named Sheet1(2) added.

I've figured out that this is because at full speed, the new .csv file isn't getting created/open so when Set csvWorkbook = ActiveWorkbook is run there's still only 1 workbook open (ThisWorkbook) so the next code line just copies Sheet(1) of ThisWorkbook into a new sheet, instead of copying Sheet(1) from AAPL.csv into it (because it doesn't exist when that line of code is run).

Note that the name of the newly created workbook isn't always known, because if there was already an APPL.csv in that download folder it'll name the next one as APPL(1).csv and so forth, so I can't just use the name of the newly created .csv workbook to reference it as I don't know what it'll be.

So I'm asking for a way to emulate single stepping through the code in debug mode when or after ActiveWorkbook.FollowHyperlink is executed. I've tried simply putting a MsgBox after it thinking that requiring the user to click OK to continue execution would help but it didn't, and from what I read I don't think a Wait or similar thing would help either? This is similar to the issue from Pause execution of VBA script until another specific excel workbook opens? but I don't understand the answer given in that thread and not sure if it really applies? Thanks for any help on this!

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
Thrashercharged
  • 3
  • 3

1 Answers1

1

You can pass a URL directly to Workbooks.Open():

Dim wb, ws
Set wb = Workbooks.Open("https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1592179200&period2=1623715200&interval=1d&events=history&includeAdjustedClose=true")
Set ws = wb.Sheets(1)
'run text to columns if only one column of data
If ws.UsedRange.Columns.Count = 1 Then
    ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True
End If
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Not going to lie, I had my doubts this would work simply because the url didn't appear to be one that linked directly to the file. But it does work, so this is certainly hands down the way to go. Well done. – K.Dᴀᴠɪs Jun 19 '21 at 05:27
  • Thanks Tim & K. Davis for the responses! This works, it solves my initial issue of not opening my file before the code continues, but instead of reading it in as a csv (comma separated values) and putting each value into a different column, it's reading the entire line as a string (complete with commas) and putting it all in Column A. I suppose I could parse it out but if you (someone) knew of a simple change that could read it as a .csv and parse each value out into it's own column that'll be appreciated! I'll note that when I single stepped my initial code out it did parse the csv correct – Thrashercharged Jun 19 '21 at 05:55
  • Thanks for editing your initial response Tim, this works and parses the data into columns perfectly! I was not aware of the UsedRange in ws.UsedRange.Columns.Count feature, or that we could pass a URL directly to workbooks.Open(), thanks! – Thrashercharged Jun 20 '21 at 15:52