How to open and set a workbook in one statement?

Question

I want to open a file by selecting it from a dialog box and set the workbook. Selecting the file and storing the file location in wbCombinedName works fine.

However, wbCombined is not always set properly. E.g. if I have the file open and save it, then run the marco it does not work. When I reopen the file it does work. When I check the variable wbCombined fullname I believe the name of the latest file opened is set.

Is there a way to adjust the below Set statement or should I include a check to see if the to be opened workbook is the already open workbook? Thanks

Set FileO = Application.FileDialog(msoFileDialogFilePicker)
With FileO
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode1
        wbCombinedName = .SelectedItems(1)
End With

Set wbCombined = Workbooks.Open(Filename:=wbCombinedName, UpdateLinks:=0)

`ThisWorkbook` or loop through the `workbooks` collection to see if is — Nathan_Sav, Feb 12 '19 at 14:48
https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open Check whether excel workbook is already opened before opening it again. — Stupid_Intern, Feb 12 '19 at 14:50
Thanks guys, so it does not work well with open workbooks.Will use a workaround — Mr Watt, Feb 12 '19 at 22:04

score 1 · Accepted Answer · answered Feb 12 '19 at 14:53

1

Function WORKBOOK_OPEN(strWorkbookname As String, _
                        Optional strCheckPath As String = "") As Boolean

Dim w As Excel.Workbook

For Each w In Workbooks

    If w.Name = strWorkbookname Then

        WORKBOOK_OPEN = True

        If strCheckPath <> "" Then
            WORKBOOK_OPEN = w.Path = strCheckPath
        End If

    End If

Next w

End Function

answered Feb 12 '19 at 14:53

Nathan_Sav

8,466
2
13
20

Thanks Nathan for the proposed workbook open check function – Mr Watt Feb 12 '19 at 22:05

How to open and set a workbook in one statement?

1 Answers1