When attempting to open a Workbook using msoFileDialogFilePicker I get a Microsoft Excel Message:- “Book is already open. Reopening will cause changes you made to be discarded – Do you want to continue” Yes/No
If choose “Yes” – that’s fine.
But if I selected the “No” option it would through a ‘Runtime Error 1004 – Method Open of Object “Workbook” failed’ message at the line highlighted due to the Workbook I am attempting to open being already open but UNSAVED.
I have inserted a Error Handler to deal with the issue of selecting the “No” option.
But of course I am still getting the Microsoft Excel Message:- “Book is already open. Reopening will cause changes you made to be discarded – Do you want to continue” Yes/No; is there any way I can suppress this message or deal with the Runtime Error 1004 in a different way.
Dim ButtonChosen As Integer
Dim ws As Worksheet
Dim fd As FileDialog, fn As String, fp As String
'Starting this procedure from UserForm1 on Sheet in
‘ "Time Book.xlsm"
Application.ScreenUpdating = False
'Change initial directory path "fp" to your desired folder'
fp = "C:\Users\Me\Documents\My Documents\"
'Looks for files ending only in "*Invoice Book.xlsm"'
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = fp & "*Invoice Book.xlsm"
.AllowMultiSelect = False
'Lists only "*Invoice Book,xlsm" files.(Slight flaw with _
'this is it also displays ALL "File Folders" as well)
.Show
If .SelectedItems.Count = 0 Then
ThisWorkbook.Activate
Unload Me
Else
fn = .SelectedItems(1)
On Error GoTo ErrHandler1:
Set wbInvoice = Workbooks.Open(fn)
'label on UserForm displaying name of "InvoiceBook.xlsm" just opened
Label1.Caption = Dir(wbInvoice.FullName)
'List on UserForm of all Sheets IN the "*Invoice Book" just opened
For Each ws In wbInvoice.Worksheets
ListBox1.AddItem ws.Name
Next ws
End If
Application.ScreenUpdating = True
End With
Exit Sub
ErrHandler1:
MsgBox "File is open but UNSAVED, Exit and go save it!"
Unload Me
End Sub
Bookmarks