Need VBA to open files based on file name

[tonyjyoo]

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.

 

[Nine Zero]

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!

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

 

[tonyjyoo]

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.

 

[jayesh25]

tony,

Perhaps something like this should fulfill your needs.

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.

 

[Nine Zero]

^^^^^^^^^^

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!

 

[tonyjyoo]

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.

 

[jayesh25]

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

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.

 

[tonyjyoo]

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:

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

 

[tonyjyoo]

Sorry, this is what I have:

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

 

[jayesh25]

change this block of code

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