Hi y'all!
long time reader, finally setup an account, first post (hope I'm doing this correctly). Excited to be here!
I'm a beginner with VBA, so please let me know if there are simple things I'm missing.
My current issue: I have a list of hyperlinks to .csv files that need to be opened in a browser, which will download and open the csv files. Then I need to save the csv files.
The links are to bureau of labor statistics data for 9 naics codes, by quarter, from 2017 Q1 through 2021 Q2 (most recent available).
I tried this code to open the hyperlinks in batch, and all the tabs opened in my browser, but the files didn't open.
Sub BatchOpenHyperLinks_Workbook()
Dim objWorksheet As Excel.Worksheet
Dim objSelectedRange As Excel.Range
Dim objHyperlink As Excel.Hyperlink
'Process each worksheet
For Each objWorksheet In ThisWorkbook.Sheets
For Each objHyperlink In objWorksheet.Hyperlinks
objHyperlink.Follow
Next
Next
End Sub
The next wrinkle is that a number of the files are going to open with the same name, which is the naics code. The period isn't contained in the file name of the read only csv that opens when the link is clicked.
Ideally, I would like to make a code that opens all the files and saves them with the appropriate names (naics-year-quarter) as excel workbooks, though automating any part of this process would be beneficial.
Any help would be greatly appreciated!
TIA!
Dan
Bookmarks