VBA to find a file specific to a cell value(s), open that file, copy data.

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a bit of a tough one on my hands. I have a template that employees will be completing, and saving in a specific location. I have another matrix that opens, selects,copies, and pastes data from these employee files. The file names are their employeeid_year. Once all employees submit their files, managers will open the employee file, and pull data from the matrix, then build distribution charts within the employee file.

The issue I am having is the macros is written to activate the specific employee file name, but this will differ from each employee save file.
How do I write into my code below, to look for a file name specific to cell values. The employee name and year are coded to save as "123_2020". I need the macros to look for the specific employee name file, and activate this workbook and worksheet to copy data.

'Valid Macros, Do Not Remove
Sub DataPullFromMatrix()

'VBA check if file exists

Dim FilePath As String
Dim TestStr As String

'Indicate the file location below. Ensure the employee number is listed, this will change based on where the Matrix is saved

FilePath = "Z:\Employee Matrix Txt Files\Employee Matrix.xlsm"
'Error handler if file is not located

TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
MsgBox " Employee 235 File does not exist."
Else
'if file is located then opens the file
Workbooks.Open "Z:\Employee Matrix Txt Files\Employee Matrix.xlsm"
ActiveWorkbook.Save
'Copies the data table from the Matrix, onto a hidden locked sheet

Windows("123_2020.xlsm").Activate

ActiveWindow.SmallScroll Down:=-123
ActiveWindow.SmallScroll ToRight:=-21
ActiveWindow.SmallScroll Down:=-75
Range("A5:AG253").Select
Selection.ClearContents

Windows("Employee Matrix.xlsm").Activate
Sheets("Total").Select
Range("C52:AH302").Select
Application.CutCopyMode = False
Selection.Copy
Windows("123_2020.xlsm").Activate
Sheets("DataHelperSheet").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Workbooks("Employee Matrix.xlsm").Close SaveChanges:=True

'Ends use of Employee Matrix Data
End If

End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You could make a list of the employee IDs in a columhn and then loop through the range in that column to open each employee's file one by one. For example, if you list their ID is column F in range F2:10 Then you could use a variable to create the file name like:
VBA Code:
Dim fName As String, c As Range
    For Each c In Range(("F2:F10")
        fName = c.Value & "-" & Year(Date) & ".xlsx"
       Workbooks.Open fName
        'Do stuff here
        ActiveWorkbook.Close '(True, False)
    Next
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm sorry I do not understand.
You can comment the following:
In your macro you have 2 files:
- "Employee Matrix.xlsm"
- "123_2020.xlsm"
1. Which of them is the fixed name and what is the variable?
2. The variable file name where will I get it from? Do you comment that it is in a cell, that cell in which book it is, on which sheet and in which cell?
3. The range to copy is always the same?
4. The range to hit is always the same?
 

lbradbury

New Member
Joined
May 14, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Thank you, I was able to figure this one out.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,483
Messages
5,636,601
Members
416,927
Latest member
BNM8V6

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
Top