CODEWITHSUNDEEP
excelvba
Griffin
Griffin

Reputation: 21

Import data from closed excel File

I know this question has been answered on the site in a few examples however using any of the examples on here I've still been unable to get them to work. Here is what I have, I get an issue when it comes to Set rng1 = b1.sht1.Range("A1"). I'm new to coding and this looks logical but can't figure it out any help would be appreciated. Is all I need to do is in an open excel file is open another and copy data from sheet1 to the data tab in the open workbook.

Sub OpenWorkbookToPullData()
    Dim path As String
    path = "S:\GAM\Middle Office\Equity Admin\aa_RawDataFiles\CollateralUtilisation.xls"

    Dim currentWb As Workbook
    Set currentWb = ThisWorkbook

    Dim openWb As Workbook
    Set openWb = Workbooks.Open(path)

    Dim openWs As Worksheet
    Set openWs = openWb.Sheets("Sheet1")

    Dim b1 As Workbook
    Dim b2 As Workbook

    Set b1 = Workbooks("CollateralUtilisation.xls")
    Set b2 = Workbooks("Collateral Management.xlsm")

    Dim sht1 As Worksheet
    Dim sht2 As Worksheet

    Set sht1 = b1.Sheets(1)
    Set sht2 = b2.Sheets("DATA")

    Dim rng1 As Range
    Dim rng2 As Range

    Set rng1 = b1.sht1.Range("A1")
    Set rng2 = sht2.Range("A1")

    rng1 = rng2

    Debug.Print "rng1 holds: "; rng1 & vbNewLine & "rng2 holds: " & rng2


    'openWb.Close (False)
End Sub

Upvotes: 2

Views: 143

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Change

Set rng1 = b1.sht1.Range("A1")

to

Set rng1 = sht1.Range("A1")

You don't need the b1. Sht1 has already been defined using b1 in Set sht1 = b1.Sheets(1)

It's like what you did for Set rng2 = sht2.Range("A1")

Use ADO to import from closed Excel Files. You may also want to see Import data from a closed workbook (ADO) using VBA in Microsoft Excel

Upvotes: 2

Related Questions