excel .open method parameters in VBA

Question

I'm having problems assigning parameters to my .open methon in VBA for Excel. Below code is what I have, all I need is to open specified workbook as read-only and not editable.

How do I set up the parameters to work, Please and Thank You!

Dim source As Workbook Dim projekt, BOM As String Dim count As Integer Dim LastBOM As Long LastBOM = Ark1.Range("I" & Rows.count).End(xlUp).Row For count = 16 To LastBOM BOM = Range("I" & count) 'List of Excel workbooks in active workbook projekt = "[Path to file on drive]\" & BOM & ".xlsx" Workbooks.Open (projekt) '<= This works and open above specified workbook ' but should be read only and not editable Workbooks.Open(projekt,ReadOnly:=True,Editable:=False) '<= None of these work Workbooks.Open(projekt,,True,,,,,,,False,,,,,) Workbooks.Open(projekt,True,False) Workbooks.Open("projekt",True,False) Workbooks.Open(Filename:="projekt",True,False) Workbooks.Open(Filename:=projekt,ReadOnly:=True,Editable:=False) Workbooks.Open(Filename:="projekt",ReadOnly:=True,Editable:=False) Next count

Please not that Dim projekt, BOM As String only declares BOM As String but projekt As Variant you need to specify a type for every variable: Dim projekt As String, BOM As String — Pᴇʜ
– Pᴇʜ, Commented Nov 6, 2018 at 10:35
No it is possible to declare multiple variables in one statement. learn.microsoft.com/en-us/dotnet/visual-basic/… — HKG
– HKG, Commented Nov 6, 2018 at 10:53
@HKG: yes, it is possible, but what PEH is saying that, if you declare more than one variable, you have to specify the type for every variable. In your case, project is declared as Variant, not as String. No a big deal here, but in other cases this can lead to errors. — FunThomas
– FunThomas, Commented Nov 6, 2018 at 11:23
@HKG Please note that the link you provide is for VB.NET and not for VBA! These are totally different languages. In VB.NET it is possible but not in VBA. In VBA you need to specify a type for each variable otherwise VBA automatically assumes Variant. — Pᴇʜ
– Pᴇʜ, Commented Nov 6, 2018 at 13:35

Pᴇʜ · Accepted Answer · 2018-11-06 10:33:58Z

Reading the documentation is always a good idea: Workbooks.Open Method (Excel) and use the ReadOnly parameter:

Workbooks.Open FileName:=projekt, ReadOnly:=True, Editable:=False

Also remove the parentheses if you don't need to return a value. Or return the workbook into a variable using parentheses:

Dim OpenedWorkbook As Workbook Set OpenedWorkbook = Workbooks.Open(FileName:=projekt, ReadOnly:=True, Editable:=False)

I recommend the last option because you then can easily access the opened workbook eg by using:

OpenedWorkbook.Worksheets("Sheet1").Range("A1")

Thank you this works great. I did visit the Excel .open method page and found it to be lacking in spicific details for a newbie like me.

Collectives™ on Stack Overflow

excel .open method parameters in VBA

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related