VBA to get data from closed workbook

[Vladgs]

Hi all, I know this is a topic discussed on many formus, I just can't seem to get the right solution. My VBA experience is very limited but I am looking forward to learn.
I am looking for a piece of code to copy certain columns from one closed workbook in a specific folder, over to the active workbook in a specific worksheet.
Seen dozens of examples but I can't personalize them unfortunattely

Thank you for your interest!

 

[Fishboy]

Hi all, I know this is a topic discussed on many formus, I just can't seem to get the right solution. My VBA experience is very limited but I am looking forward to learn.
I am looking for a piece of code to copy certain columns from one closed workbook in a specific folder, over to the active workbook in a specific worksheet.
Seen dozens of examples but I can't personalize them unfortunattely

Thank you for your interest!

Hi Vladgs, welcome to the boards.

This can be done using VBA by opening the source document, copying the data, closing the source document and pasting in your desired location. By Disabling screen updating all of this happens pretty much instantly behind the scenes without it showing on the screen. In order to help you write this code we will need to know the following:

- The name of the source workbook to be copied from
- The exact filepath as to where that document can be found
- What range(s) from the source workbook you want to copy
- Where in the active workbook you are expecting to paste this data

With the above information we can probably piece together some code that will do the job.

 

[Vladgs]

Hi Fishboy,

Let's say the the source workbook, by default changes name every month, I could manually change it to a constant.
I was planning on a different aproach: the file will be alone in a folder "K:\TEAM FOLDER\Vlad\DASHBOARD\DB 5\PO Compliance\Months 2\Data\Latest" , if I must I will call the file "PO data".
I need to copy collumns A B C D E F G H I J K, retreive it in my workbook called "Template" on the "data" worksheet starting from cells A1.

I'd like the simplest code possibe, so I can understand it and maybe change it because I want to automate this task for more files.

Thank you !

 

[Fishboy]

Hi Fishboy,

Let's say the the source workbook, by default changes name every month, I could manually change it to a constant.
I was planning on a different aproach: the file will be alone in a folder "K:\TEAM FOLDER\Vlad\DASHBOARD\DB 5\PO Compliance\Months 2\Data\Latest" , if I must I will call the file "PO data".
I need to copy collumns A B C D E F G H I J K, retreive it in my workbook called "Template" on the "data" worksheet starting from cells A1.

I'd like the simplest code possibe, so I can understand it and maybe change it because I want to automate this task for more files.

Thank you !

Hi again Vladgs, try out the following code in a COPY of your Template workbook. You may need to change the file extensions I have used from .xlsx to whatever is the correct format for your workbooks as well as define the sheet name from the workbook being copied from (I have called it Sheet1).

Sub CopyPasteWrkBk()
' Defines variables
Dim InputFile As Workbook
Dim OutputFile As Workbook


' Open input / output workbooks:
Set InputFile = Workbooks.Open("K:\TEAM FOLDER\Vlad\DASHBOARD\DB 5\PO Compliance\Months 2\Data\Latest\PO Data.xlsx")
Set OutputFile = Workbooks("Template.xlsx")


' Copy what you want from InputFile:
InputFile.Sheets("Sheet1").Activate
LastRow = InputFile.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
InputFile.Sheets("Sheet1").Range("A1:K" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Data").Activate
OutputFile.Sheets("Data").Range("A1").Paste


' Close InputFile
InputFile.Close




End Sub

 

[Vladgs]

Thank you !

Works great, i've added a few more things to it like delete columns and a timer function to keep track of total macro time.

I am currently working on changing the macro so it looks for the imput file adress in a cell, say "Z1" in the Template workbook in the Data sheed, any suggestions for this?

Thank you again for your time and wisdom.

 

[Fishboy]

Thank you !

Works great, i've added a few more things to it like delete columns and a timer function to keep track of total macro time.

I am currently working on changing the macro so it looks for the imput file adress in a cell, say "Z1" in the Template workbook in the Data sheed, any suggestions for this?

Thank you again for your time and wisdom.

Hi again Vladgs, sorry for the delay in my reply (I tend not to check the forums much at night / over the weekend due to the joys of parenthood).

Glad to hear the original code worked for you. With regards to your newer query, if in cell Z1 of the Template workbook you had the following:

K:\TEAM FOLDER\Vlad\DASHBOARD\DB 5\PO Compliance\Months 2\Data\Latest\PO Data.xlsx

In theory you could update the macro as follows:

Sub CopyPasteWrkBk()
' Defines variables
Dim InputFile As Workbook
Dim OutputFile As Workbook


' Open input / output workbooks:
Set InputFile = Workbooks.Open(Sheets("Data").Range("Z1").Value)
Set OutputFile = Workbooks("Template.xlsx")


' Copy what you want from InputFile:
InputFile.Sheets("Sheet1").Activate
LastRow = InputFile.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
InputFile.Sheets("Sheet1").Range("A1:K" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Data").Activate
OutputFile.Sheets("Data").Range("A1").Paste


' Close InputFile
InputFile.Close

End Sub

As always, make sure to test it out in a COPY of your workbook to prevent any data loss if anything goes wrong.

 

[Vladgs]

That works like a charm!

I've tried a few modifications myself looking at other threads, but I was unsuccessful.
I belive this macro will help me save a lot of time over the months, not to mention prevent human error.

Thank you so much for helping out !

 

[Fishboy]

That works like a charm!

I've tried a few modifications myself looking at other threads, but I was unsuccessful.
I belive this macro will help me save a lot of time over the months, not to mention prevent human error.

Thank you so much for helping out !

No problem mate, happy to help.

 

[Vladgs]

Hello again,

Another thing I'm having a hard time getting is a way of copyright from 2 imput files.

The ideea is: second paste must start from the first empty cell of row A in the Outputfile.

I tried to do this by duplicating the macro rather than making the 2 operations in the same one.

Any help is appreciated!

 

[Fishboy]

Hello again,

Another thing I'm having a hard time getting is a way of copyright from 2 imput files.

The ideea is: second paste must start from the first empty cell of row A in the Outputfile.

I tried to do this by duplicating the macro rather than making the 2 operations in the same one.

Any help is appreciated!

Hi again,

I think you would be better trying to have these in a single function. Untested as I am not at work, but try this in a COPY of your workbook:

Cell Z1 = K:\TEAM FOLDER\Vlad\DASHBOARD\DB 5\PO Compliance\Months 2\Data\Latest\First File.xlsx
Cell Z2 = K:\TEAM FOLDER\Vlad\DASHBOARD\DB 5\PO Compliance\Months 2\Data\Latest\Second File.xlsx

Sub CopyPasteWrkBk()
' Defines variables
Dim InputFile1 As Workbook
Dim InputFile2 As Workbook
Dim OutputFile As Workbook

' LastRow of Template Data sheet
LR = Workbooks(Template.xlsx").Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row +1
' Open input / output workbooks:
Set InputFile1 = Workbooks.Open(Sheets("Data").Range("Z1").Value)
Set InputFile2 = Workbooks.Open(Sheets("Data").Range("Z2").Value)
Set OutputFile = Workbooks("Template.xlsx")


' Copy what you want from InputFile1:
InputFile1.Sheets("Sheet1").Activate
LastRow = InputFile.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
InputFile1.Sheets("Sheet1").Range("A1:K" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Data").Activate
OutputFile.Sheets("Data").Range("A" & LR).Paste

' Increase LR by 1 to account for paste
LR = LR +1

' Copy what you want from InputFile2:
InputFile2.Sheets("Sheet1").Activate
LastRow = InputFile.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
InputFile2.Sheets("Sheet1").Range("A1:K" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Data").Activate
OutputFile.Sheets("Data").Range("A" & LR).Paste

' Increase LR by 1 to account for paste
LR = LR +1

' Close InputFiles
InputFile1.Close
InputFile2.Close

End Sub