Import data from a closed workbook into an existing worksheet using VBA?

Question

I've got a piece of VBA code that will import a piece of data from another workbook into my open excel spreadsheet however, it won't import if into the sheet that I have already created. It wants to open a new sheet every time.

This is the code:

Sub ImportCurrentMonthData()
' Get workbook...
Dim ws As Worksheet
Dim filter As String
Dim targetWorkbook As Workbook, wb As Workbook
Dim Ret As Variant

Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Excel files (*.xlsb),*.xlsb"
Caption = "Please Select an input file "
Ret = Application.GetOpenFilename(filter, , Caption)

If Ret = False Then Exit Sub

Set wb = Workbooks.Open(Ret)

wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)

ActiveSheet.Name = "CurrentMonth"

I have a sheet called "CurrentMonth" already and I want to data to go into that spreadsheet. What do I need to change in the VBA code for this happen?

Thanks in advance.

You can activate the "CurrentMonth" Sheet: wb.Worksheets("CurrentMonth").Activate The question is how do you import the data? — Tomasz Paluch
– Tomasz Paluch, Commented Jun 15, 2020 at 13:48
Is "CurrentMonth" worksheet in your active workbook, or in the one you've just opened? If in your active workbook, before opening the second one, you should move the code immediately after Set targetWorkbook = Application.ActiveWorkbook. But where is your problem? Your code does not do anything. You practically name the active sheet of the opened workbook like "CurrentMonth". This is what you want doing? — FaneDuru
– FaneDuru, Commented Jun 15, 2020 at 13:49
@TomaszPaluch The "CurrentMonth" sheet is where I want the data to go. This VBA currently imports a selected spreadsheet into my active workbook but creates a new sheet called "CurrentMonth", I want it to import into a sheet already there instead of opening a new one. — Hatton90
– Hatton90, Commented Jun 15, 2020 at 13:54
@FaneDuru The code currently will open a closed workbook and import the data into a new sheet in the active workbook and call it "CurrentMonth". Instead I want it to import the data into the sheet that is already there. Does that make sense? — Hatton90
– Hatton90, Commented Jun 15, 2020 at 13:55
It doesn't... Try, please answer my questions. "Is "CurrentMonth" worksheet in your active workbook, or in the one you've just opened?". "But where is your problem?" Do you receive an error? The above code does not do anything, except opening the new workbook and rename its active sheet as "CurrentMonth", "Is this what you want doing?" — FaneDuru
– FaneDuru, Commented Jun 15, 2020 at 13:59

Tim Williams · Accepted Answer · 2020-06-15 15:58:24Z

1

If you just want top copy the data to an existing sheet....

Sub ImportCurrentMonthData()

    ' Get workbook...
    Dim ws As Worksheet
    Dim filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant

    Set targetWorkbook = Application.ActiveWorkbook 'ThisWorkbook?

    ' get the customer workbook
    filter = "Excel files (*.xlsb),*.xlsb"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)

    If Ret = False Then Exit Sub

    Set wb = Workbooks.Open(Ret)

    wb.Sheets(1).Usedrange.Copy targetWorkbook.Sheets("CurrentMonth").Range("A1")

edited Jun 15, 2020 at 15:58

answered Jun 15, 2020 at 15:48

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

6 Comments

Hatton90 Over a year ago

Thanks but this is now coming up with the error code '1004' - Application-defined or object-defined error. When i push debug, it highlights the last sentence.

Tim Williams Over a year ago

Sorry bad copy/paste from your posted code. Fixed now

Hatton90 Over a year ago

That works! Thank you, last request if you can be so kind. Is there anyway to do it so that it doesn't open the closed workbook? So if it imports the data only? It's probably part of my own code that needs to be changed but I can't think of it.

Tim Williams Over a year ago

You have to open the file if you want to copy the data. If your data is structured like a table then you could maybe use SQL to query the worksheet but that's more complex than just open/copy/close.

Hatton90 Over a year ago

Thank for your answer, is there any way to close the spreadsheet once it is copied as part of the VBA?

|

Collectives™ on Stack Overflow

Import data from a closed workbook into an existing worksheet using VBA?

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