Need VBA to open files based on file name

tonyjyoo

Board Regular
Joined
Aug 5, 2016
Messages
167
Hello,

I need a VBA code to open a file in a specific file path, copy the set of the data, paste into another workbook then close that file.

Here's the following criteria:

File path: S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation

- The files in this folder vary month over month (for example "01 Jan Employee Allocations 2016", "02 Feb Employee Allocations 2016", etc)

How can I code this to open the latest file and paste it into my workbook we will call "workbook A"?

Thank you in advance.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hello,

I need a VBA code to open a file in a specific file path, copy the set of the data, paste into another workbook then close that file.

Here's the following criteria:

File path: S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation

- The files in this folder vary month over month (for example "01 Jan Employee Allocations 2016", "02 Feb Employee Allocations 2016", etc)

How can I code this to open the latest file and paste it into my workbook we will call "workbook A"?

Thank you in advance.

Hey try this out!

Code:
Sub Data()

Dim Main As Workbook
Dim Month As Workbook






R = "A1:J10000"






Set Main = ActiveWorkbook
file = Range("A1")
Set Month = Workbooks.Open("S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation\" & file & " Employee Allocations 2016.xlsx")


Sheet2.Cells.Clear
Main.Sheets("Sheet2").Range(R).Value = Month.Sheets("Sheet1").Range(R).Value
Month.Close False

End Sub

Alright so this is what going on......
In your Workbook A in Sheet one in Cell A1 you would nee to input the first part of your file name (ex. 01 Jan, 02 Feb, 03 Mar, etc.)

Then run the code from sheet 1

Change the range "R" to what ever your specifications are of your data... i usually used 10000 as a safety net to make sure i capture all data available

And that should do it.... let me know if you need any further assistance
 
Last edited:
Upvote 0
Hey Nine Zero,

Quick question. Is it possible for VBA to search in the folder and open files with changing names month to month, or do I NEED to manually set the first part of the file name (01 Jan etc) in cell A1?

Essentially I want something where once I have this workbook open, I run the macro, it goes into that file path folder, opens the latest month file, copy/paste data into my sheet. And every month I do this I want to delete the data and basically run the macro by spitting in the latest data.
 
Upvote 0
tony,

Perhaps something like this should fulfill your needs.

Code:
Sub GrabData()


Sheets("Sheet1").Cells.Select


Dim Month
Dim MonthNo
Dim FileP As String
Dim SourceWb As Object
Month = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
MonthNo = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12")
FileP = "S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation\" & _
MonthNo(CInt(VBA.Month(VBA.Date)) - 1) & " " & Month(CInt(VBA.Month(VBA.Date)) - 1) & " " & "Employee Allocations" & " " & VBA.Year(VBA.Date) & ".xlsm"
Set SourceWb = Workbooks.Open(FileP)
SourceWb.Activate
SourceWb.Sheets("Main").Cells.Select
Selection.Copy
ThisWorkbook.Activate
ThisWorkbook.Sheets("Main").Cells.Select
Selection.Paste
SourceWb.Close False




End Sub

I've kept arrays since you're well aware what format your files are in. Just adjust the array and it should work month on month.
 
Upvote 0
^^^^^^^^^^

That genius i would have never thought about that one!

i myself am new to VBA and i learn here and there but what @jayesh25 is saying should work perfectly!
 
Upvote 0
The workbook I want the data pasted into has sheet name "Employee Allocation List". (In vba it is Sheet 5).

Do I need to have the other file open for this to work? Code is not working.
 
Upvote 0
Hi tony,

you need to place that macro in the master workbook i.e. Employee Allocation List and run it by assigning it to a button. I've made some modifications for you

Code:
Sub GrabData()


Dim Month
Dim MonthNo
Dim FileP As String
Dim SourceWb As Object
Month = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
MonthNo = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12")
FileP = "S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation\" & _
MonthNo(CInt(<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym>.Month(<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym>.Date)) - 1) & " " & Month(CInt(<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym>.Month(<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym>.Date)) - 1) & " " & "Employee Allocations" & " " & <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym>.Year(<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym>.Date) & ".xlsm"
Set SourceWb = Workbooks.Open(FileP)
SourceWb.Activate
SourceWb.Sheets(1).Cells.Select
Selection.Copy
ThisWorkbook.Activate
ThisWorkbook.Sheets("Sheet5").Cells.Select
Selection.Paste
SourceWb.Close False

End Sub

Be sure to check the file extension i've placed .xlsm check your extension of the monthly files and replace it with whatever is relevant.
 
Last edited:
Upvote 0
One more question.

If I want to incorporate what Nine Zero did and have the VBA open the file based on the date I enter cell A1, how could I write this? I just realized that I won't always only need to open the latest one for the purpose of this macro.

I have this down but it isn't working:

Code:
Sub UpdateEmployeeAllocationList()


Dim Main As Workbook
Dim Month As Workbook




R = "A2:K1000"




Set Main = ActiveWorkbook
file = Range("A1")
Set Month = Workbooks.Open("S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation\" & file & " Employee Allocations 2016.xlsx")


Month.Activate
Selection.Copy
ThisWorkbook.Activate
ThisWorkbook.Sheets("Sheet5").Cells.Select
Selection.Paste


Month.Close False


End Sub
 
Upvote 0
Sorry, this is what I have:


Code:
Sub UpdateEmployeeAllocationList()


Dim Main As Workbook
Dim Month As Workbook




R = "A2:K1000"




Set Main = ActiveWorkbook
file = Range("A1")
Set Month = Workbooks.Open("S:\NPH Accounting\NPH Accounting Department\Allocations\2016\Employee Allocation\" & file & " Employee Allocations 2016.xlsx")


Month.Activate
Selection.Copy
Main.Activate
Month.Sheets("Sheet5").Cells.Select
Main.Selection.Paste


Month.Close False


End Sub
 
Upvote 0
change this block of code

Code:
Month.Activate
Activesheet.cells.select
Selection.Copy
Main.Activate
Month.Sheets("Sheet5").Cells.Select
Main.Selection.Paste
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top