Cell reference changes to make a change in URL

[cdtouchberry]

I have a spreadsheet that will pull financial data into excel via VBA. But I want to be able to change the ticker, and create a corresponding change in the web address. Such that if I changed my spreadsheet (Sheet1) from COST to another ticker, say MSFT, this would update in the URL of the VBA code. Below is my current coding, and help would be greatly appreciated!







Option Explicit


Public Sub OpenWebXLS()
' *************************************************
' Define Workbook and Worksheet Variables
' *************************************************
Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet


Set wkbMyWorkbook = ActiveWorkbook


' *************************************************
' Open The Web Workbook From Stockrow (Income Statement)
' *************************************************
Workbooks.Open ("https://stockrow.com/api/companies/COST/financials.xlsx?dimension=MRY&section=Income%20Statement&sort=asc")


' *************************************************
' Set the Web Workbook and Worksheet Variables
' *************************************************
Set wkbWebWorkbook = ActiveWorkbook
Set wksWebWorkSheet = ActiveSheet


' *************************************************
' Copy The Web Worksheet To My Workbook and Rename
' *************************************************
wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "StockrowDataIS"


' *************************************************
' Close the Web Workbook
' *************************************************
wkbMyWorkbook.Activate
wkbWebWorkbook.Close


End Sub







Kind Regards,


CDT

[Kenneth Hobs]

Welcome to the forum!

It is unclear where COST comes from. If from activesheet.name then:

Workbooks.Open ("https://stockrow.com/api/companies/" & ActiveSheet.Name & _
"/financials.xlsx?dimension=MRY&section=Income%20Statement&sort=asc")

[cdtouchberry]

Hi Kenneth,

Thanks. Sorry for being unclear, but COST is the ticker symbol I was looking up. So if you go to:
https://stockrow.com/COST/financials/income/annual
You can see the data I am trying to download. I copied the link address from the export to excel link.

I have the ticker on the Sheet1, and the data downloads to the active sheet named "StockrowDataIS"

[Kenneth Hobs]

IT is still unclear. It is many cells on Sheet1 with different Ticker values? What cells? e.g. A2


Workbooks.Open ("https://stockrow.com/api/companies/" & ThisWorkbook.Worksheets("Sheet1").Range("A2") & _
"/financials.xlsx?dimension=MRY&section=Income%20Statement&sort=asc")

[cdtouchberry]

Hi Ken. No it is not many cells. It is one cell (Sheet1, A2). I enter 1 ticker at a time and download the companies information. So the COST or MSFT would be entered into Sheet1, A2 and the code opens the data in a new sheet, in this case called "StockRowDataIS".

I tried your code and it seems to be working very well. Thank you for your assistance, I am most appreciative!