VBA syntax error for opening up a file and refreshing data connections

Question

Not sure what is going on here... making a subroutine in excel vba that opens and activates a file by the values that are passed to it. Obviously I'm doing something wrong... not sure what though.

Sub openBook(ByVal fName As String, ByVal activate As Boolean)
    Application.Workbooks.Open(fName, 0, False) '= Required here?
End Sub

Edit Got that working, just want to check to make sure this is the proper syntax below See updated code:

Sub openBook(ByVal fileName As String, ByVal refresh As Boolean)
    Dim wb As Workbook

    Set wb = Workbooks.Open(fileName, 0, False)

    If refresh = True Then
        wb.RefreshAll
    End If

End Sub

score 4 · Accepted Answer · edited May 23 '17 at 11:49

4

The syntax of Workbooks.Open is

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

Is this what you are trying?

Sub Sample()
    openBook "C:\MyFile.xlsx", False, True
End Sub

Sub openBook(fileName As String, UpdtLink As Boolean, RdOnly As Boolean)
    Application.Workbooks.Open fileName, UpdtLink, RdOnly
End Sub

EDIT

If you want to pass 0/False and 1/True then you will have to change

Sub openBook(fileName As String, UpdtLink As Boolean, RdOnly As Boolean)

to

Sub openBook(fileName As String, UpdtLink As Variant, RdOnly As Variant)

FOLLOWUP FROM COMMENTS

is there anyway to also activate that workbook in the same line or would another line of code be required? – metsales 1 min ago

Why do you want to activate it? .Activate should be avoided as much as possible. You might want to see THIS

Having said that, if you want to activate it then you have to use a code like this

Sub Sample()
    openBook "C:\MyFile.xlsx", False, True
End Sub

Sub openBook(fileName As String, UpdtLink As Boolean, RdOnly As Boolean)
    Dim wb As Workbook
    Set wb = Application.Workbooks.Open(fileName, UpdtLink, RdOnly)
    wb.Activate
End Sub

However the below is what I would suggest based on my earlier advice about not to use .Activate

Dim wb As Workbook

Sub Sample()
    openBook "C:\MyFile.xlsx", False, True

    DoEvents

    With wb
        '
        '~~> Do something with the workbook here
        '
    End With
End Sub

Sub openBook(fileName As String, UpdtLink As Boolean, RdOnly As Boolean)
    Set wb = Application.Workbooks.Open(fileName, UpdtLink, RdOnly)
End Sub

edited May 23 '17 at 11:49

Community

1
1

answered Nov 26 '13 at 20:13

Siddharth Rout

147,039
17
206
250

Ahh yes it is! Just out of curiosity if my workbook had a live data connection that I wanted to refresh is that what `UpdateLinks` is for? or would I have to activate that workbook and then do `RefreshAll`? – Adjit Nov 26 '13 at 20:18
I can give you the answer But let's see what do you find out if you press the magic key `F1` in Excel ;) Where do you think I picked the exact syntax from ;) – Siddharth Rout Nov 26 '13 at 20:19
so it's just for formulas? is there anyway to also activate that workbook in the same line or would another line of code be required? – Adjit Nov 26 '13 at 20:22
Updating my post with the 2nd question in the above comment. – Siddharth Rout Nov 26 '13 at 20:24
Thanks for the comprehensive follow up! check my original post, I am trying to avoid globals, so I am going to try and set up a return value for openBook that returns the opened workbook. Not a question for here though. Going to see what I can do and if I have issues I will post a new question. Thanks for all the help Siddharth. Very much appreciated. – Adjit Nov 26 '13 at 20:32
That's a neat little trick! thanks for that! Again out of some more curiosity... if I wanted to set up a return value, I wouldn't be able to do so on a `sub` right? I would have to change it to a function – Adjit Nov 26 '13 at 20:34
Not necessary. Yes you can use `Function` to return values or you can also use a Public variable/Object in a `Sub` :) – Siddharth Rout Nov 26 '13 at 20:36
are public variables (globals) frowned upon in VBA because I come from `c` programming where I generally try to avoid it, but would this be considered bad for this situation? – Adjit Nov 26 '13 at 20:39
1

It's a myth that using Public variables is bad. It's the same logic for using `Go To` Yes it is bad if used unnecessarily – Siddharth Rout Nov 26 '13 at 20:42
1

Interesting read: http://c2.com/cgi/wiki?GlobalVariablesAreBad Funny this link also mentiones about `GoTo` :P – Siddharth Rout Nov 26 '13 at 20:44
Btw My earlier comment `Not necessary. Yes you can use Function to return values or you can also use a Public variable/Object in a Sub :)` Please read it as `Not necessary. Yes you can use Function to return values or you can also use a Public/Private variable/Object in a Sub :)` – Siddharth Rout Nov 26 '13 at 20:46
That is actually really interesting. I've always figured that but everyone has always been adamant about not using globals. So if this is the case (and if you remember what I am trying to do from yesterday)... I really only want to have open 1 workbook at a time. So when I close it should I `Set wb = NULL`? – Adjit Nov 26 '13 at 20:50
2

`So when I close it should I Set wb = NULL` Excel is kind enough to release it's objects but try automating Excel from .Net :D So Yes you should always cleanup. One should flush the toilet after use... Right? ;) – Siddharth Rout Nov 26 '13 at 20:53
I generally try and see how much I can fill it up before I flush :P (totally kidding... it's a fine point you make there!) – Adjit Nov 26 '13 at 20:58
I always try and relate to real life scenarios and hence such a comment about the toilet... :P – Siddharth Rout Nov 26 '13 at 21:00
Is there some statement that I can use to check to see whether or not a saveAs was successful? – Adjit Nov 26 '13 at 21:16
@metsales: Not to be rude but you can't ask all questions here in the comment :D – Siddharth Rout Nov 26 '13 at 21:27
I know... I am ashamed http://renegadecinema.com/wp-content/uploads/2013/07/findingnemo2003m1080pbd3.jpg didn't want to have to make small questions. – Adjit Nov 26 '13 at 21:30
http://stackoverflow.com/questions/20228409/checking-to-see-if-saveas-was-successful-vba – Adjit Nov 26 '13 at 21:34

score 0 · Answer 2 · answered Nov 26 '13 at 20:06

0

try something like this (Untested)

Sub openBook(ByVal fileName As String, ByVal activate As Boolean)
    Application.Workbooks.Open fileName:=filename
End Sub

answered Nov 26 '13 at 20:06

bto.rdz

6,636
4
35
52

no Syntax error, but now what if I wanted to pass the other variables to the open function? like the `0` and `false`? I also changed the variable to `fName` to avoid confusion – Adjit Nov 26 '13 at 20:09
1

try something like this: `Application.Workbooks.Open fileName:="", UpdateLinks:=0, ReadOnly:=False` – bto.rdz Nov 26 '13 at 20:14

VBA syntax error for opening up a file and refreshing data connections

2 Answers2