VBA to Open workbook and run macro

[Justplainj]

Good day,

I have the following code which runs VBA from an already open workbook (Workbook1) to open several workbooks (Workbook2) one by one and paste a formula into cell Z2. I also need the formula to flash fill down.
I am getting 2 problems in the code.

Option Explicit
Sub OpenRunCode() 'Open files run Excel VBA macro
Const sPath = "File\Path\" 'Real Path hidden
Dim sFil As String
Dim owb As Workbook

sFil = Dir(sPath & "*.xl*") 'Captures all XL files in a directory.

Do While sFil <> "" 'Loop through all files in Folder
Application.AskToUpdateLinks = False 'Stops update links from showing
Application.DisplayAlerts = False 'Stop alerts from showing

Set owb = Workbooks.Open(sPath & sFil, Password:="12345") 
'Error 1

Range("Z2").Select ' Error 2
ActiveCell.FormulaR1C1 = _
    "=IFERROR(LET(" & Chr(10) & "A,VLOOKUP(RC[-2],'Data Tab'!R[1]C[-14]:R[8]C[-10],2,FALSE)," & Chr(10) & "B,VLOOKUP(RC[-2],'Data Tab'!R[1]C[-8]:R[8]C[-4],2,FALSE)," & Chr(10) & "C,VLOOKUP(RC[-2],'Data Tab'!R[1]C[-2]:R[8]C[2],2,FALSE)," & Chr(10) & "IF(OR(RC[-3]=""A"",RC[-3]=""B Lower"",RC[-3]=""B Upper""),A," & Chr(10) & "IF(OR(RC[-3]=""C Lower"",RC[-3]=""C Upper"",RC[-3]=""D Lower""),B,C))" & Chr(10) & "),"""")"

Application.DisplayAlerts = True
Application.AskToUpdateLinks = True


sFil = Dir
Loop

End Sub

In the code above which is in workbook 1, there are two points causing errors at Error 1 and Error 2.
Error 1 occurs after the workbook in the file path is opened (Workbook2), it enters the password as the workbook2 is encrypted.
The update links and update alerts as false correctly removes some of the dialog boxes however, because the links within workbook2 points to a different workbook it requests a encryption password of the workbook where the links are

I do not need to add a second password as the links will be automatically replaced with the code that will point to ranges within workbook2 itself.

The second error is when the macro in workbook1 tries to select the range("Z2") in workbook 2, it gives a runtime 1004 error.

I am also struggling with the code to flash fill the formula down after it is placed in range"Z2")

Any assistance will be appreciated.

Thanks
J

 

[rollis13]

Your macro is in working condition. The only reason you get Errore: 1004 is because the fetched file didn't open (error 1) and conseguently can't select cell Z2 (error 2).
You could try without using Application.DisplayAlerts = False to detect the name of the offending file or when the macro stop in debug see the name in the variable sFil.

 

[Justplainj]

Hi Rollis13

Thank you for your reply.
I am assuming where you write "because the fetched file didn't open (error 1) you are referring to the original workbook that contains the functions that workbook2 is linking to?
If that is the case then that has fixed the error but that presented a different problem.

I have done the following.

Just for clarity
Workbook1 is the workbook I run the macro from
Workbook2 is the workbook that is opened by the macro
Workbook3 is the original workbook that as the functions that workbook2 links to.

The ultimate what I want to achieve is the break the links in workbook 2 and replace it with a function that refers to a sheet within workbook 2.

Workbook2 will change dependent on the Dir populated by

sFil = Dir(sPath & "*.xl*")

Workbook2 is a split of 64 different workbooks that was originally all combined data from Workbook3

I use power automate desktop to split Workbook3 into several workbooks and tried to run a macro before it saves but could not get that to work.
Therefore I tried to resort to the above code to open each split workbook run the macro to break the links and paste the formula then close it again but that brought with, a new struggle.

With your advice above. I moved the VBA code to the original workbook that was used to create the several split workbooks.
That way it will remain open when I run the macro and essentially makes workbook1 and workbook3 as clarified above the same workbook.

Everything works find now, however for some reason it is giving me an error when processing the line

ActiveCell.FormulaR1C1

I also recorded this inserting of the insert of the function with the macro recorder to make sure that the code is correct but it give the same Runtime 1004 application defined or object defined error.


Below is the amended code. It is essentially exactly the same with the added line to flash fill the function down after inserting in cell Z2
I ran inserting formula codes many times before, and just can't figure out what the problem is.

Option Explicit
Sub OpenRunCode() 'Open files run Excel VBA macro
Const sPath = "File\Path\" 'Real Path hidden
Dim sFil As String
Dim owb As Workbook

sFil = Dir(sPath & "*.xl*") 'Captures all XL files in a directory.

Do While sFil <> "" 'Loop through all files in Folder
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False

Set owb = Workbooks.Open(sPath & sFil, Password:="12345")

Range("Z2").Select 'Error after this line is processed
ActiveCell.FormulaR1C1 = _
        "=IFERROR(LET(A,VLOOKUP(RC[-3],'Data Tab'!R3C11:R10C15,2,FALSE),B,VLOOKUP(RC[-3],'Data Tab'!R3C17:R10C21,2,FALSE),C,VLOOKUP(RC[-3],'Data Tab'!R3C23:R10C27,2,FALSE),IF(OR(RC[-4]=""A"",RC[-4]=""B Lower"",RC[-4]=""B Upper""),A,IF(OR(RC[-4]=""C Lower"",RC[-4]=""C Upper"",RC[-4]=""D Lower""),B,C))),"""")"
    Range("Z2").Select
    Selection.AutoFill Destination:=Range("table1[sheet1]")


Application.DisplayAlerts = True
Application.AskToUpdateLinks = True


sFil = Dir
Loop

End Sub

 

[Justplainj]

I have fixed the formula by using the direct cell references instead of useing the macro recorded references
Instead of

        "=IFERROR(LET(A,VLOOKUP(RC[-3],'Data Tab'!R3C11:R10C15,2,FALSE),B,VLOOKUP(RC[-3],'Data Tab'!R3C17:R10C21,2,FALSE),C,VLOOKUP(RC[-3],'Data Tab'!R3C23:R10C27,2,FALSE),IF(OR(RC[-4]=""A"",RC[-4]=""B Lower"",RC[-4]=""B Upper""),A,IF(OR(RC[-4]=""C Lower"",RC[-4]=""C Upper"",RC[-4]=""D Lower""),B,C))),"""")"

I used

    "=IFERROR(LET(A,VLOOKUP(W2,'Data Tab'!$K$3:$O$10,2,FALSE),B,VLOOKUP(W2,'Data Tab'!$Q$3:$U$10,2,FALSE),C,VLOOKUP(W2,'Data Tab'!$W$3:$AA$10,2,FALSE),IF(OR(V2=""A"",V2=""B Lower"",V2=""B Upper""),A,IF(OR(V2=""C Lower"",V2=""C Upper"",V2=""D Lower""),B,C))),"""")"

This has resolved the issue with the solution from rillis13 to keep the original workbook open, the code is now working.
Thanks
J

 

[rollis13]

Glad you were able to arrange your project.