CODEWITHSUNDEEP
vbaexcel
user2054167
user2054167

Reputation: 3

VBA Workbooks.open "Compile error: Argument not optional"

I'm trying to use the 

Workbooks.Open Filename:= [...]

method to access some data within a for each loop using a reference to the Microsoft Scripting Routine. I've done this successfully in a few other Macros but for some reason, in all modules within this file (I've tried rebooting the machine etc.), I receive the message "Compile error: Argument not optional" for the code on that line. Other workbooks don't throw up errors there.

My research into the problem hasn't yielded anything tailored to this particular situation - I think that maybe I need to use Set somewhere to force VBE to recognize the object? Hopefully a more experienced user will be able to recognize some classic situations where this might occur. The only clue I have for now is that the VBE doesn't seem to recognize "workbooks" as a keyword, as it's not capitalizing the first letter.

Any help would be much appreciated.

C

Upvotes: 0

Views: 2651

Answers (1)

GSerg
GSerg

Reputation: 78155

Apparently there is something else in the workbook called Workbooks (variable, class, function), and because it has more local scope it gets found before the the actual Workbooks object.

You can either rename the offending object or access Workbooks in a more qualified way:

Application.Workbooks.Open Filename:= [...]

Upvotes: 1

Related Questions