cdtouchberry
01-29-2019, 04:47 PM
I am downloading an excel file from the web, and copying it into a workbook for financial analysis. My VBA code below creates a worksheet of the copy and names it "ISRaw". I then need to use formulas in different sheets that link to "ISRaw" for some calculations.
Currently, when I update my stock ticker and download a new web workbook, a new file is copied but it does not overwrite the data on "ISRaw" and my links between the pages are useless. If I delete "ISRaw" prior to the copy, and then use the VBA code - the links give a REF error. It's like the links are tied to the original ISRaw (sheet2), but the new download ISRaw is (sheet3) and the links fail.
So I need some help in creating a code that will ensure than any downloads always go to a pre-existing sheet I will name "ISRaw", and the new incoming data will overwrite there instead of putting in a new sheet. Thanks in advanced for your help!
Option Explicit
Public Sub OpenStockRowIS()
' *************************************************
' 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/" & ThisWorkbook.Worksheets("Start").Range("A2") & _
"/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("Start")
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "ISRaw"
' *************************************************
' Close the Web Workbook
' *************************************************
wkbMyWorkbook.Activate
wkbWebWorkbook.Close
End Sub
Currently, when I update my stock ticker and download a new web workbook, a new file is copied but it does not overwrite the data on "ISRaw" and my links between the pages are useless. If I delete "ISRaw" prior to the copy, and then use the VBA code - the links give a REF error. It's like the links are tied to the original ISRaw (sheet2), but the new download ISRaw is (sheet3) and the links fail.
So I need some help in creating a code that will ensure than any downloads always go to a pre-existing sheet I will name "ISRaw", and the new incoming data will overwrite there instead of putting in a new sheet. Thanks in advanced for your help!
Option Explicit
Public Sub OpenStockRowIS()
' *************************************************
' 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/" & ThisWorkbook.Worksheets("Start").Range("A2") & _
"/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("Start")
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "ISRaw"
' *************************************************
' Close the Web Workbook
' *************************************************
wkbMyWorkbook.Activate
wkbWebWorkbook.Close
End Sub