ANOTHER DILEMMA FOR ANY ACCESS/EXCEL EXPERT!!"HELP"

[DIVINEDAR0956]

HELP!!!

I have the following code to open an excel file from access.

Private Sub NewFile()
Dim XLApp As Object
Dim xlWB As Object
Dim xlWB2 As Object
Dim xlWB3 As Object
Set XLApp = CreateObject("Excel.Application&quot
XLApp.AskToUpdateLinks = False
XLApp.DisplayAlerts = False

Set xlWB = XLApp.Workbooks.Open("C:\COST\WBS_EXCEL50.xls", False)
Set xlWB2 = XLApp.Workbooks.Open("C:\COST\WBS_EXCEL50_2.xls", False)
Set xlWB3 = XLApp.Workbooks.Open("C:\COST\WBS_EXCEL50_3.xls", False)

If xlWB.IsOpen = False Then
xlWB
If xlWB = True Then
xlWB2
xlWB2.Sheets("PROJECT_INFORMATION&quot.Select
If xlWB2.IsOpen = True Then
xlWB3
xlWB3.Sheets("PROJECT_INFORMATION&quot.Select
If xlWB3.IsOpen = True Then
MsgBox "All Available files are In use, Try Later", vbOKCancel
Else
MsgBox "Your File Is Now Open"
End If
End If
End If
End If

xlWB.Sheets("PROJECT_INFORMATION&quot.Select
XLApp.Visible = True 'use this to view excel file

xlWB.Saved = True
XLApp.AskToUpdateLinks = True
XLApp.DisplayAlerts = True
Set XLApp = Nothing
End Sub

If one file is in use I want it to go to the next file and check to see if it is open. If so go to the next file and open it. This works with just one file to open.

So how do I use the IF STATEMENTto choose a file that is not open.

Thank you. Darlene Sippio
dsippio@comtechsystems.com

 

[kevinclark]

Something like this maybe ?


If xlWB.IsOpen = True Then
If xlWB2.IsOpen = True Then
If xlWB3.IsOpen = True Then
MsgBox "All Available files are In use, Try Later", vbOKCancel
Else
'xlWB3
xlWB3.Sheets("PROJECT_INFORMATION&quot.Select
End If
Else
'xlWB2
xlWB2.Sheets("PROJECT_INFORMATION&quot.Select
End If
Else
'xlWB
xlWB.Sheets("PROJECT_INFORMATION&quot.Select
End If

 

[DIVINEDAR0956]

Thank you Kevin.

I adjusted the following:

Private Sub NewFile()
Dim XLApp As Object
Dim xlWB As Object
Dim xlWB2 As Object
Dim xlWB3 As Object
Set XLApp = CreateObject("Excel.Application&quot
XLApp.AskToUpdateLinks = False
XLApp.DisplayAlerts = False

Set xlWB = XLApp.Workbooks.Open("C:\COST\WBS_EXCEL50.xls", False)
xlWB.Sheets("PROJECT_INFORMATION&quot.Select
XLApp.Visible = True 'use this to view excel file


If xlWB.IsOpen = True Then
If xlWB2.IsOpen = True Then
If xlWB3.IsOpen = True Then
MsgBox "All Available files are In use, Try Later", vbOKCancel
Else
'xlWB3
Set xlWB3 = XLApp.Workbooks.Open("C:\COST\WBS_EXCEL50_3.xls", False)
xlWB3.Sheets("PROJECT_INFORMATION&quot.Select
XLApp.Visible = True
End If
Else
'xlWB2
Set xlWB2 = XLApp.Workbooks.Open("C:\COST\WBS_EXCEL50_2.xls", False)
xlWB2.Sheets("PROJECT_INFORMATION&quot.Select
XLApp.Visible = True
End If
Else
'xlWB
xlWB.Sheets("PROJECT_INFORMATION&quot.Select
XLApp.Visible = True
End If


xlWB.Saved = True
XLApp.AskToUpdateLinks = True
XLApp.DisplayAlerts = True
Set XLApp = Nothing
End Sub

My problem is when I get to the first IF statement it gives me an error OBJECT DOESN'T SUPPORT THIS PROPERTY METHOD. What did I do wrong?

Thank you. Darlene Sippio
dsippio@comtechsystems.com

 

[kevinclark]

Hi Darlene

I have not checked into it but from the description of the error it sounds like the IsOpen method is not available to the Workbook object. The sample I gave was geared more for highlighting a possible structure for your "If" statement.
I am not sure if the workbooks will be opened exclusively by a number of workstations on a server or by a single workstation / user. The piece of code that you included shows that you were opening the Workbooks but I am not sure if that was just for testing purposes. Anyway perhaps u can look at doing something like this:

Since you are only referencing 3 workbooks is it possible for you to add a piece of code to each workbook Workbook_Open and Workbook_BeforeClose event.
For example:

In the "ThisWorkbook" module of each targeted Workbook

Private Sub Workbook_Open()
If Application.Visible = True Then
CustomDocumentProperties.Item("Busy&quot = True
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.Visible = True Then
CustomDocumentProperties.Item("Busy&quot = False
End If
End Sub


Then adjust your original code to check for that property ?
Example:


If xlWB.CustomDocumentProperties.Item("Busy&quot = True Then
If xlWB2.CustomDocumentProperties.Item("Busy&quot = True Then
If xlWB3.CustomDocumentProperties.Item("Busy&quot = True Then
etc .....