Workbook already open Error

palmerk

Board Regular
Joined
Dec 6, 2005
Messages
121
I am creating a new workbook that would enable a user other than myself to open the workbook and a macro to receive info based on an automatic update. They will need to "reopen it" several times prior to being done for the day. I am using formulas to take out info particular accounts to provide a better information but they will not need to save it.

I am running into this error message

"Workbook.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen Workbook.xls?" with yes and no as options.

Is there a way that I can write VBA to say yes? :confused:
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Just ahead of the command to open the worksheet use:
Code:
Application.DisplayAlerts = False
which will suppress the warning and open the file. Keep in mind that you will have to live with the consequences!

Best programming practice is to add a line immediately after the single command you want to blow through:
Code:
Application.DisplayAlerts = True
so you don't miss any more warnings.

Chris
 
Upvote 0
My code show this...

Application.DisplayAlerts = False

However I still get the error message.

Is there VBA code in which I can select yes on an alert box? :wink:
 
Upvote 0
If you have included code such as:

Code:
Sub DoSomething()
Application.DisplayAlerts = False
Workbooks.Open "MyFile.xls"
Application.DisplayAlerts = True
...more code
End Sub

Then you will not get a warning or an error message. MyFile.xls will just open.

What does your code look like?

Chris
 
Upvote 0
One solution would be to check each time if the workbook is open, if it is just carry on, if not then open it. Here's code I got somewhere on this Board a while ago which I often add as a separate function in the same module as my main code to do just that:

Code:
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
wbname = WorkBookName
WorkbookOpen = False
On Error GoTo WorkBookNotOpen
If Len(Application.Workbooks(wbname).Name) > 0 Then
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function
Then in my main sub I use something like:

Code:
If WorkbookOpen("Workbook Name Here") Then
    Else
    Workbooks.Open Filename:="Workbook Name Here"
End If

HTH
 
Upvote 0
One way without using error traps:


Sub Test1()
Dim myPath$, myFile$
myPath = "C:\Your\File\Path\"
myFile = "YourFileName.xls"

If Workbooks.Count = 1 Then
Workbooks.Open Filename:=myPath & myFile

Else

Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name And wb.Name = myFile Then
Workbooks(myFile).Activate
Exit For
Exit Sub
End If
Next wb
Workbooks.Open Filename:=myPath & myFile
End If

End Sub



Modify for the path and workbook names.
 
Upvote 0
I'm sorry, but I tried both and couldn't get it to work on the auto open. How would the full VBA look with either code/function on the Auto Open?

Here is my code that needs to go within the function/code.

Sub Auto_Open()

'Paste Collection Macro
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Host Screen Paste").Select
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Next.Select
Range("B19").Select
Selection.Copy
Application.WindowState = xlMinimized
Application.DisplayAlerts = False

End Sub

Thanks
 
Upvote 0
I'm sorry, but I tried both and couldn't get it to work on the auto open. How would the full VBA look with either code/function on the Auto Open?
If you are using Auto_Open, that means you have that code in a standard VBA module. In that same module, paste in the code I posted. Amend your AutoOpen procedure with this before the End Sub line:

Run "Test1"


That way, all your code in the Auto_Open macro will execute, and then the workbook of interest (remember, you need to modify my code for workbook and path name) will be activated if it is already open in that same instance of Excel, or it will be opened.

The code from Fergus should work for you as well. try them again, modifying them as you need to, and post back with what exactly went right or wrong.
 
Upvote 0
So here is what I have tried...

Sorry about the delay... been off of this project for awhile
Here is what I have tried for the function. The error came up on the code version.

Sub Auto_Open()
If WorkbookOpen("Collections Notepad 7.20 Buttons check open2.xls") Then
Else
Workbooks.Open Filename:="S:\CRS tools\SPG\Collections Notepad 7.20 Buttons check open2.xls"
End If
'Paste Collection Macro
ThisWorkbook.Saved = True
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Host Screen Paste").Select
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Next.Select
Range("B19").Select
Selection.Copy
Application.WindowState = xlMinimized
Application.DisplayAlerts = False
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
End Sub


Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
wbname = WorkBookName
WorkbookOpen = False
On Error GoTo WorkBookNotOpen
If Len(Application.Workbooks(wbname).Name) > 0 Then
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function

Am I missing something simple?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top