Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a document is open
Hi,
I have a lot of Excel (Microsoft office XP) files that are all linked to a main source document. I need to open that source document each time I open one of the files so they can update. I hide the source document when it opens. I have a functional code in VBA, but I am not the real user of the documents and I need it to be more user-friendly. I need to prevent the alerts asking if I want to re-open the the source document each time and warning that any unsaved information will be discarded. I always click "no" because I never want to reopen the source document if it is already open, and then it gives an error '1004' and asks to end or debug. Is there a way to check if the source document is already open and take no action if it is, but then if it is not already open, open it? Here is the code: 'In the "This Workbook" object of each VBA Project Private Sub Workbook_Open() Application.ScreenUpdating = False 'Opening the main document: Workbooks.Open "Workbook.xls" 'Hiding it: ActiveWindow.Visible = False Application.ScreenUpdating = True End Sub Can someone help me out? Thanks -- AnthonyJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a document is open
Private Sub Workbook_Open()
Dim wb As Workbook On Error Resume Next Set wb = Workbooks("Workbook.xls") If Not wb Is Nothing Then Exit Sub On Error GoTo 0 Application.ScreenUpdating = False 'Opening the main document: Workbooks.Open "Workbook.xls" 'Hiding it: ActiveWindow.Visible = False Application.ScreenUpdating = True End Sub -- Charles Chickering "A good example is twice the value of good advice." "AnthonyJ" wrote: Hi, I have a lot of Excel (Microsoft office XP) files that are all linked to a main source document. I need to open that source document each time I open one of the files so they can update. I hide the source document when it opens. I have a functional code in VBA, but I am not the real user of the documents and I need it to be more user-friendly. I need to prevent the alerts asking if I want to re-open the the source document each time and warning that any unsaved information will be discarded. I always click "no" because I never want to reopen the source document if it is already open, and then it gives an error '1004' and asks to end or debug. Is there a way to check if the source document is already open and take no action if it is, but then if it is not already open, open it? Here is the code: 'In the "This Workbook" object of each VBA Project Private Sub Workbook_Open() Application.ScreenUpdating = False 'Opening the main document: Workbooks.Open "Workbook.xls" 'Hiding it: ActiveWindow.Visible = False Application.ScreenUpdating = True End Sub Can someone help me out? Thanks -- AnthonyJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a document is open
This will return TRUE if the workbook name in s is open:
Function IsOpen(s As String) As Boolean Dim wb As Workbook For Each wb In Workbooks If wb.Name = s Then IsOpen = True Exit Function End If Next IsOpen = False End Function -- Gary''s Student "AnthonyJ" wrote: Hi, I have a lot of Excel (Microsoft office XP) files that are all linked to a main source document. I need to open that source document each time I open one of the files so they can update. I hide the source document when it opens. I have a functional code in VBA, but I am not the real user of the documents and I need it to be more user-friendly. I need to prevent the alerts asking if I want to re-open the the source document each time and warning that any unsaved information will be discarded. I always click "no" because I never want to reopen the source document if it is already open, and then it gives an error '1004' and asks to end or debug. Is there a way to check if the source document is already open and take no action if it is, but then if it is not already open, open it? Here is the code: 'In the "This Workbook" object of each VBA Project Private Sub Workbook_Open() Application.ScreenUpdating = False 'Opening the main document: Workbooks.Open "Workbook.xls" 'Hiding it: ActiveWindow.Visible = False Application.ScreenUpdating = True End Sub Can someone help me out? Thanks -- AnthonyJ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a document is open
Thanks a lot for the help. It works great. You responded so fast...
-- AnthonyJ "Charles Chickering" wrote: Private Sub Workbook_Open() Dim wb As Workbook On Error Resume Next Set wb = Workbooks("Workbook.xls") If Not wb Is Nothing Then Exit Sub On Error GoTo 0 Application.ScreenUpdating = False 'Opening the main document: Workbooks.Open "Workbook.xls" 'Hiding it: ActiveWindow.Visible = False Application.ScreenUpdating = True End Sub -- Charles Chickering "A good example is twice the value of good advice." "AnthonyJ" wrote: Hi, I have a lot of Excel (Microsoft office XP) files that are all linked to a main source document. I need to open that source document each time I open one of the files so they can update. I hide the source document when it opens. I have a functional code in VBA, but I am not the real user of the documents and I need it to be more user-friendly. I need to prevent the alerts asking if I want to re-open the the source document each time and warning that any unsaved information will be discarded. I always click "no" because I never want to reopen the source document if it is already open, and then it gives an error '1004' and asks to end or debug. Is there a way to check if the source document is already open and take no action if it is, but then if it is not already open, open it? Here is the code: 'In the "This Workbook" object of each VBA Project Private Sub Workbook_Open() Application.ScreenUpdating = False 'Opening the main document: Workbooks.Open "Workbook.xls" 'Hiding it: ActiveWindow.Visible = False Application.ScreenUpdating = True End Sub Can someone help me out? Thanks -- AnthonyJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spell Check on Excel document | Excel Discussion (Misc queries) | |||
how do i check if i have printed a document in excel and when | New Users to Excel | |||
I forgot my password to open an excel document, how do I open it? | Excel Discussion (Misc queries) | |||
my excel document won't open unless i go file, then open | Excel Discussion (Misc queries) | |||
spell check protected document | Excel Discussion (Misc queries) |