Test for an Open Spreadsheet

[JoeyB_99]

I am using Access 2007, and have inherited a database from another person, who has left our company.

When the Main Menu is loaded a macro is run that opens a table, deletes its contents, then imports the contents of a spreadsheet into this table. It does not make sense to link this spreadsheet because sometimes it needs to be directly used/accessed for input.

My problem is that sometimes a user will open the spreadsheet - for whatever purpose - but if a user opens the database it will give an error message stating that it cannot find this spreadsheet. So the current workaround is that a user must let the other database users (five users total) know that they plan to open/use the spreadsheet.

Can someone please help me to add something to the Main Menu (On Load) macro so that it tests if the spreadsheet is open? If it is open then it will let the user know and will not execute the import process.

Any other ideas or suggestions are also welcome.

 

[Trevor G]

You will need to adapt this but it does work

Sub IsWorkBookOpen()
Dim xlsApp As Object
Set xlsApp = CreateObject("Excel.Application")
Set xlsApp = CreateObject("L:\Excel VBA\HR Trev Sample.xls")
If xlsApp Is Nothing Then
'Not open
MsgBox "Workbook is not open", vbCritical, "Test"
Set xlsApp = Nothing
On Error GoTo 0
Else 'It is open
MsgBox "Yes it is open", vbInformation, "Test"
Set xlsApp = Nothing
On Error GoTo 0
End If
End Sub

 

[JoeyB_99]

Thanks Trevor.

I am fairly new to VBA and know very little about it.

Currently there is an embedded macro in the On Load property of the Main Menu form. This opens the necessary spreadsheet and imports its contents to a table.

Where exactly do I enter your VBA code to test for an open spreadsheet?

 

[Trevor G]

If you have an embedded MACRO you may have to convert it so you can use the code. Post back the code once you have converted the MACRO into VBA.

 

[JoeyB_99]

Okay then Trevor, thanks for the help. I appreciate it.

I have converted the existing embedded macro to VBA, and placed the code in a Word document.

 

[Trevor G]

Try replacing the On Load code to this to see if it works.

  Private Sub Form_Load()
  On Error GoTo Form_Load_Err
   
  Dim xlsApp As Object
Set xlsApp = CreateObject("Excel.Application")
Set xlsApp = CreateObject("M:\CONSTRUCTION JOB FILES\631 - STEEL\6414 - US Steel, Lake Erie\15.0 Project Budget and Cost Control\Lake Erie Open Service\Aecon Open Service PO.xls ")
If xlsApp Is Nothing Then
'Not open
    DoCmd.DeleteObject acTable, "AeconOpenServicePO"
      DoCmd.TransferSpreadsheet acImport, 10, "AeconOpenServicePO", "M:\CONSTRUCTION JOB FILES\631 - STEEL\6414 - US Steel, Lake Erie\15.0 Project Budget and Cost Control\Lake Erie Open Service\Aecon Open Service PO.xls", True, ""
      DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors"
  Set xlsApp = Nothing
On Error GoTo 0
Else 'It is open
MsgBox "Yes it is open", vbInformation, "Test"
Set xlsApp = Nothing
On Error GoTo 0
End IF
   
   
  Form_Load_Exit:
      Exit Sub
   
  Form_Load_Err:
      MsgBox Error$
      Resume Form_Load_Exit
   
  End Sub

 

[JoeyB_99]

Thanks Trevor.

I will give it a try and let you know how it works.

 

[JoeyB_99]

Trevor, two questions. How does this relate/connect up with the VBA code that I supplied you previously?

In your recent code what do you mean by "On Error Go To 0"?

 

[Trevor G]

Take a look at this link it will explain

http://www.cpearson.com/excel/errorhandling.htm