Excel VBA Workbooks.open with optional parameters

Question

I'm sure this is a very basic question that has a very basic answer but so far I can't make my Excel macro work.

I am trying to open an Excel workbook and input the optional password parameter. However, whenever I do this, I get the error Compile error expected: Named Parameter.

This is my current code

Sub password_opening_test() Workbooks.Open Filename:="E:\password protecting macrotest.xlsx",,,,Password:="test" Range("G7").Select ActiveCell.FormulaR1C1 = "hello" ActiveWorkbook.Save ActiveWindow.Close End Sub

I've also tried it by adding square brakets in between the commas but I still recieve the error (which is highlighted by the first comma).

Thanks very much for reading

try without the extras commas Workbooks.Open Filename:="E:\password protecting macrotest.xlsx", Password:="test" As long as you specify which argument you are passing (i.e. - Password:=) the extra commas for the other arguments are not necessary. — Scott Holtzman
– Scott Holtzman, Commented Feb 16, 2016 at 14:03

whytheq · Accepted Answer · 2016-02-16 15:55:36Z

I think there are two options available:

As Scott details in his comment - get rid of your commas

2. Just use the arguments in their correct positions

Sub password_opening_test() Workbooks.Open "E:\password protecting macrotest.xlsx",,,,"test" Range("G7").Select ActiveCell.FormulaR1C1 = "hello" ActiveWorkbook.Save ActiveWindow.Close End Sub

For the record I prefer Scott's approach as it is much more readable in future re-visits to the code. I like to use line seperators so it looks like this:

Sub password_opening_test() Workbooks.Open _ Filename:="E:\password protecting macrotest.xlsx", _ Password:="test" Range("G7").Select ActiveCell.FormulaR1C1 = "hello" ActiveWorkbook.Save ActiveWindow.Close End Sub

So I have to ask. If both are options that work, which one is the better practice: 1) Workbooks.Open "E:\password protecting macrotest.xlsx", Password:="test" or 2) Workbooks.Open "E:\password protecting macrotest.xlsx",,,,"test"? Option 1 seems to be the better practice.
@IronMan upto you - I prefer spelling out arguments in a particular layout - I'll add to answer.
your response is dead on. I guess it would be up to the individual coder.
Thanks Whytheq, both options worked! And as you say I agree i prefer the second option, definitely improves the readability of the code

Collectives™ on Stack Overflow

Excel VBA Workbooks.open with optional parameters

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related