VBA to Open workbook and run macro

Justplainj

Board Regular
Joined
Apr 15, 2021
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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.

VBA 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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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
VBA Code:
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
VBA Code:
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.

VBA 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
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
 
Last edited:
Upvote 0
I have fixed the formula by using the direct cell references instead of useing the macro recorded references
Instead of
VBA Code:
        "=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
VBA Code:
    "=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
 
Upvote 0
Solution
Glad you were able to arrange your project.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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
Back
Top