VBA Script - Can't figure out one error...

[jaihawk8]

I'm writing a VBA Script within an Excel Sheet to automate the opening of our Earnings Statements, changing a date in one of the cells, refreshing the links, using a specific file path, closing and saving. I've been building and testing it one step at a time.

I got it to successfully open the files in the specific directory, update (without the edit links part), save an close, so I'm part way there. The issue I'm having right is with changing a date in one of the cells. I'm stepping through things one line at a time to validate first. It's failing on this step:

'Set the value of cell C2 in opened workbook
ThisWorkbook.Sheets("Sheet1").Range("C2").Value = originalValue

For reference, in the code below, I'm asking it to take the value in field C3 from the file that my Macro is in and write that to cell C2 in the file that it just opened to refresh.

Here is the code I have so far:

Sub UpdateFilesInPath()
Dim folderPath As String
Dim fileName As String
Dim originalValue As Variant
Dim wb As Workbook


' Get the folder path from cell B21
folderPath = ThisWorkbook.Sheets("Main").Range("B21").Value

' Check if the folder path exists
If Dir(folderPath, vbDirectory) = "" Then
MsgBox "Folder path does not exist.", vbExclamation
Exit Sub
End If

' Disable screen updating to speed up the process
Application.ScreenUpdating = False

' Loop through all files in the folder
fileName = Dir(folderPath & "\*.xls*")
Do While fileName <> ""
' Open the current file
Workbooks.Open folderPath & fileName, UpdateLinks:=3

'Retrieve the original value from cell C3 of the original workbook
originalValue = ThisWorkbook.Sheets("Main").Range("C3").Value

'Set the value of cell C2 in opened workbook
ThisWorkbook.Sheets("Sheet1").Range("C2").Value = originalValue

' Update the workbook (replace this line with your update code)
' For example:
' wb.Sheets("Sheet1").Range("A1").Value = "Updated Value"

' Save and close the current file
ActiveWorkbook.Save
ActiveWorkbook.Close

' Move to the next file
fileName = Dir()
Loop

' Re-enable screen updating
Application.ScreenUpdating = True

MsgBox "Files updated successfully.", vbInformation
End Sub

 

[Alex Blakenburg]

@Joe4 It doesn't do any harm but I am sure I have seen you saying not to use activate or select
Updating a not active workbook works fine for me.

 

[Alex Blakenburg]

@jaihawk8, I have logged out for the night but I think this might give you what you need to update the links.

Workbook.ChangeLink method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Joe4]

It doesn't do any harm but I am sure I have seen you saying not to use activate or select

Yes, I usually say to avoid selecting ranges, when it is not necessary, as that could slow your code down.
But in this case, it should have zero impact/effect on the speed of your code, because if this workbook is already the active workbook, running the "newWb.Activate" line essentially does nothing!
But if it is NOT the active workbook, then it DOES need selecting/activating before you can update it (there are times when it is necessary!)

 

[Alex Blakenburg]

if it is NOT the active workbook, then it DOES need selecting/activating before you can update it

I had no problem with this line even after I made the original workbook the Active Workbook
newWB.Sheets("Sheet1").Range("C2").Value = originalValue

 

[Joe4]

I had no problem with this line even after I made the original workbook the Active Workbook
newWB.Sheets("Sheet1").Range("C2").Value = originalValue

Interesting.
I may have been confusing it with Copy/Paste methodology, which does require you to be on the workbook/sheet you want to update.
But it then appears that the direct assignment method does not share that requirement.
Good to know...

 

[Peter_SSs]

I have updated my profile ..

Great, thanks for doing that.