cdtouchberry
01-28-2019, 01:52 PM
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§ion=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
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§ion=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