PDA

View Full Version : Need copied web workbook to overwrite to the same worksheet



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&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("Start")
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "ISRaw"


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


End Sub

大灰狼1976
01-30-2019, 06:42 PM
Hi cdtouchberry!
If replace the whole contents of worksheets("ISRaw") ...

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&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.Cells.Copy wkbMyWorkbook.Sheets("ISRaw").[a1]

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