Import data from closed excel File

Question

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

Siddharth Rout · Accepted Answer · 2015-06-16 01:20:34Z

2

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

edited Jun 16, 2015 at 1:20

answered Jun 16, 2015 at 1:15

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Griffin Over a year ago

I thought I had written it that way as well and it still didn't work but I have tried again and it works fine, the problem now is I get an error on the debug line. When I remove this the data is cut from the closed excel sheet but doesn't paste in the open sheet.

Siddharth Rout Over a year ago

Is your open sheet openWs?

Griffin Over a year ago

Not sure I understand your question?

Siddharth Rout Over a year ago

I asked, which is your open sheet? openWs?

Griffin Over a year ago

open.Ws is the closed sheet in workbook Set b1 = Workbooks("CollateralUtilisation.xls")

|

Collectives™ on Stack Overflow

Import data from closed excel File

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related