none
Need copied web workbook to overwrite to a worksheet RRS feed

  • Question

  • 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 ("website//stockrow dot 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

    Wednesday, January 30, 2019 2:34 PM

Answers

  • You may try something like this...

    Public Sub OpenStockRowIS() ' ************************************************* ' Define Workbook and Worksheet Variables ' ************************************************* Dim wkbMyWorkbook As Workbook Dim wkbWebWorkbook As Workbook Dim wksWebWorkSheet As Worksheet Dim wsDest As Worksheet Set wkbMyWorkbook = ActiveWorkbook On Error Resume Next Set wsDest = wkbMyWorkbook.Worksheets("ISRaw")

    wsDest.Cells.ClearContents On Error GoTo 0 ' ************************************************* ' Open The Web Workbook From Stockrow (Income Statement) ' ************************************************* Workbooks.Open ("website//stockrow dot 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 ' ************************************************* If wsDest Is Nothing Then wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets("Start") wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "ISRaw" Else wksWebWorkSheet.Cells.Copy wsDest.Range("A1") End If ' ************************************************* ' Close the Web Workbook ' ************************************************* wkbMyWorkbook.Activate wkbWebWorkbook.Close End Sub



    Subodh Tiwari (Neeraj) sktneer


    Wednesday, January 30, 2019 3:26 PM
  • You're welcome! Glad it worked as desired.

    Please take a minute to accept the answer and don't forget to upvote. :)


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by cdtouchberry Wednesday, January 30, 2019 6:48 PM
    Wednesday, January 30, 2019 4:53 PM

All replies

  • You may try something like this...

    Public Sub OpenStockRowIS() ' ************************************************* ' Define Workbook and Worksheet Variables ' ************************************************* Dim wkbMyWorkbook As Workbook Dim wkbWebWorkbook As Workbook Dim wksWebWorkSheet As Worksheet Dim wsDest As Worksheet Set wkbMyWorkbook = ActiveWorkbook On Error Resume Next Set wsDest = wkbMyWorkbook.Worksheets("ISRaw")

    wsDest.Cells.ClearContents On Error GoTo 0 ' ************************************************* ' Open The Web Workbook From Stockrow (Income Statement) ' ************************************************* Workbooks.Open ("website//stockrow dot 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 ' ************************************************* If wsDest Is Nothing Then wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets("Start") wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "ISRaw" Else wksWebWorkSheet.Cells.Copy wsDest.Range("A1") End If ' ************************************************* ' Close the Web Workbook ' ************************************************* wkbMyWorkbook.Activate wkbWebWorkbook.Close End Sub



    Subodh Tiwari (Neeraj) sktneer


    Wednesday, January 30, 2019 3:26 PM
  • Subodh,

    That works magnificently! Thank you so much, I really do appreciate the help!

    Wednesday, January 30, 2019 4:44 PM
  • You're welcome! Glad it worked as desired.

    Please take a minute to accept the answer and don't forget to upvote. :)


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by cdtouchberry Wednesday, January 30, 2019 6:48 PM
    Wednesday, January 30, 2019 4:53 PM