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

 

[Joe4]

"ThisWorkbook" ALWAYS refers to the workbook that the Macro is found in!
So do NOT use that workbook reference when trying to write to the workbook just opened.
I would recommend setting a workbook reference to the new workbook right after you open it, i.e.

Dim newWB as Workbook

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

Then, when you want to assign the value to it, you can do it like:

newWB.Activate
newWB.Sheets("Sheet1").Range("C2").Value = originalValue

 

[jaihawk8]

"ThisWorkbook" ALWAYS refers to the workbook that the Macro is found in!
So do NOT use that workbook reference when trying to write to the workbook just opened.
I would recommend setting a workbook reference to the new workbook right after you open it, i.e.

Dim newWB as Workbook

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

Then, when you want to assign the value to it, you can do it like:

newWB.Activate
newWB.Sheets("Sheet1").Range("C2").Value = originalValue

That worked beautifully! Thank you!

Now, the final step for me would be to set the path for the update links. Would I start out at the top with:

Dim newFolderPath As String
Dim newfileName As String

I'm not sure where I'd put the code for that and what that code would be.

The Update Links Path would be in B22 of the Macro workbook and the Update Links File would be in B23 of the Macro workbook file.

Here's the code right now:

Sub UpdateFilesInPath()
    Dim folderPath As String
    Dim fileName As String
    Dim originalValue As Variant
    Dim newWB 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
       
        ' Set active workbook
        Set newWB = ActiveWorkbook
       
        'Retrieve the original value from cell B3 of the original workbook
        originalValue = ThisWorkbook.Sheets("Main").Range("B3").Value
       
        'Set the value of cell C2 in opened workbook
        newWB.Activate
        newWB.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

 

[Peter_SSs]

@jaihawk8
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you in one of your posts. Compare the two.

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Joe4]

I am afraid I do not have much to offer in regards to your "update links" question. It is something that I have never had to do.
So I don't know that I can offer much assistance with that part of your question.
I was focusing on the main part of your question, where you were talking about the line of code that was failing.

 

[Alex Blakenburg]

@Joe4 - Why did you include this line

newWB.Activate

 

[Alex Blakenburg]

@jaihawk8 maybe remove

newWB.Activate

and change your save and close lines to

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

 

[jaihawk8]

@jaihawk8
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you in one of your posts. Compare the two.

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm so sorry about that. I have updated my profile and will make sure to use the information you gave me in regards to posting script in the future. Thank you for your guidance.

 

[jaihawk8]

@jaihawk8 maybe remove

newWB.Activate

and change your save and close lines to

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

Thank you for that.

 

[Joe4]

@Joe4 - Why did you include this line

newWB.Activate

I usually include that line in case they are bouncing back and forth and may not be on that workbook when they want to update it.
If they are in the other workbook, I don't think you can directly update a cell in the non-active workbook.

In this case, if that workbook is already the active workbook when they hit that line, then it should not be necessary.
But I don't think it shouldn't cause any problems having it in there, right?