VBA Script to Update Files in a folder, updating Links

[jaihawk8]

I'm trying to simply life for our commissions folks. Currently, they have to open each Sales Person's Earnings Statement, click on Data-> Edit Links, choose the file to update, then when it does, change the date to the current pay period, save the file, and then close it.

I would like to write a VBA script that will use the information in the table below:

Earnings Statement File Path is located in cell B19

When it needs to update the links, I would want it to use the Path in B20 and the File in B21 (wasn't sure if I needed to concatenate that all together or not).

Once that's updated, I would like it to change cell C3 in the earnings statement to the value that's in cell B3 of this worksheet.

Right now, I'm just trying to get it to at least open the file and update it (not changing cell C3, but I'm failing on the following step:

Here's the layout of the Main File:

Earnings Statement Generator - Test CD.xlsm
	A	B	C
1	Payroll Date:	04.12.24	Date in text format "xx.xx.xx"
2
3	Payroll Date:	4/12/2024	Date in short date format "xx/xx/xxxx"
4
5	Payroll Period:	April
6
7	Who is Running the Report:	Jai
8
9	For What District:	CD
10
11	Rep or Analyst:	Analysts
12
13	Main File Path	C:\Users\jbohl\OneDrive - Quadient\Central District\2024 Payroll Files\
14	Month Directory	Apr 24 Earnings Statements\
15	District Folder	CD\
16	Pay Period Folder	04.12.24
17	Log File	FY24 CD Commission Log - Analyst.xlsm
18
19
20
21	File Path	C:\Users\jbohl\OneDrive - Quadient\Central District\2024 Payroll Files\Apr 24 Earnings Statements\CD\04.12.24\Analysts\
22	Update Links Path	C:\Users\jbohl\OneDrive - Quadient\Central District\2024 Payroll Files\Earnings Statements\
23	Update Links File	FY24 CD Commission Log - Analyst.xlsm
24
Main

Cell Formulas
Range	Formula
B13,B15	B13	=VLOOKUP(B7,$E:$F,2,FALSE)
B14	B14	=VLOOKUP(B5,$E:$F,2,FALSE)
B16	B16	=+B1
B17	B17	=VLOOKUP(F34,$E:$F,2,FALSE)
B21	B21	=CONCATENATE(B13,B14,B15,B16,"\",B11,"\")
B22	B22	=CONCATENATE(B13,"Earnings Statements\")
B23	B23	=+B17

Cells with Data Validation
Cell	Allow	Criteria
B7	List	=$E$2:$E$4
B9	List	=$E$21:$E$22
B5	List	=$E$7:$E$18
B11	List	=$E$25:$E$26

Here's the scripting I've used so far:

Sub UpdateAndSaveFiles()    Dim folderPath As String    Dim fileName As String    Dim wb As Workbook    Dim updateLinksFileName As String        ' Get the folder path from cell B19    folderPath = ThisWorkbook.Sheets("Sheet1").Range("B19").Value        ' Check if the folder path ends with a backslash, if not, add one    If Right(folderPath, 1) <> "\" Then        folderPath = folderPath & "\"    End If        ' Get the update links file name from cell B20    updateLinksFileName = ThisWorkbook.Sheets("Sheet1").Range("B20").Value        ' Loop through each file in the directory    fileName = Dir(folderPath & "*.xlsm")    Do While fileName <> ""        ' Open the workbook        Set wb = Workbooks.Open(folderPath & fileName)                ' Update the sheet        wb.RefreshAll                ' Update links        wb.UpdateLinks Name:=folderPath & updateLinksFileName, Type:=xlExcelLinks                ' Save and close the workbook        wb.Close SaveChanges:=True                ' Get the next file name        fileName = Dir    LoopEnd Sub

 

[Micron]

UpdateLinks is a property of the workbook. It looks like you're thinking it is a method. I think you want this

Workbook.UpdateLink method (Excel)

Office VBA reference topic

learn.microsoft.com