Hi everybody. I am using MS Office 2007 (Access and Excel).
Back on Sept 26/12 I posted a request for help. I wanted to create a macro (or VBA code) that would help test whether a particular spreadsheet has been opened.
Trevor G came to my rescue and supplied the following VBA code.
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\6448 - 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\6448 - 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
I'm a newbie to VBA. Please bear with me here. I copied this code into the proper place within event procedure for the On Load property. I tested it by running it, and then saved it.
However, everytime I open the database it tells me "Yes it is open". But I am the only one on the network and no one has the spreadsheet open! Can someone please help me trouble-shoot this?
Any input/suggestions would be greatly appreciated?
Back on Sept 26/12 I posted a request for help. I wanted to create a macro (or VBA code) that would help test whether a particular spreadsheet has been opened.
Trevor G came to my rescue and supplied the following VBA code.
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\6448 - 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\6448 - 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
I'm a newbie to VBA. Please bear with me here. I copied this code into the proper place within event procedure for the On Load property. I tested it by running it, and then saved it.
However, everytime I open the database it tells me "Yes it is open". But I am the only one on the network and no one has the spreadsheet open! Can someone please help me trouble-shoot this?
Any input/suggestions would be greatly appreciated?
