Detect if a Certain Excel Spreadsheet is open before exporting data 1

[Jayz]

Hi,
Using VBA, On a button click, How can I detect if a Certain Excel Spreadsheet is open, before exporting data to this particular file.

I'd like it to bring up a msgbox to "close the spreadsheet" before exporting.

Any help would be much appreciated,

Jay

 

[UncleCake]

Jay,

I am having a hard time following what you are asking. Let my try to review. You are need to make sure that in your Excel workbook you need a certain sheet activated/visible.

I am not really sure on the next part about the msgbox.

-Uncle Cake

 

[Jayz]

I've created a code which exports queried data to a specified excel spreadsheet:

'*******************************************************
Private Sub Export_Click()

DoCmd.TransferSpreadsheet acExport,_ 8,"OM_Search_query1","C:\Genie_Export\OM_Search1.xls", True

Set xlApp = CreateObject("excel.application&quot
xlApp.Visible = True
xlApp.Workbooks.open "c:\GENIE_Export\OM_SEArch1.xls"
Set xlApp = Nothing ' the application, then release the reference.

End Sub
'*****************************

if I requery the data and try to export it to the spreadhsheet that is currently opened, the export procedure hangs, which is expected.

I want to avoid this happening by prompting the user that "the spreadsheet is currently opened", please close it first.

So I'm thinking that I need to detect first whether this spreadsheet is opened. If so, prompt the user. If not continue with the export of data.

Hope this helps.

Thanks in advance,
Jay

 

[Jayz]

UncleCake are you there?

Can someone please help.

Regards,
Jay

 

[dsi]

You may want to try using GetObject to attach to the active instance of Excel. En error will occur if Excel is not running.

On Error Resume Next
Set xlApp = GetObject(,"Excel.Application")
If Err.Number <> 0
   'Excel is not open - file can not be open
   Err.Clear
   'Now, Open Excel
   Set xlApp = CreateObject(&quot;Excel.Application&quot;)
Else
   'Excel is Open
   'Traverse through active workbooks and check 
   '  to see if the file is already opened
   'If Open - tell user and quit
End If

 

[Jayz]

Thanks dsi for the response.

My apologies as my VBA skills are very limited.
My understanding is that the above code will run when there is an error.
The problem is that because the data is trying to export to a currently opened spreadsheet, an error will not be detected by Access untill I close the spreadsheet. In the meantime it will just hang.

Any other ideas?

 

[dsi]

Here is a little function that will check to see if the workbook is open. Just call the IsWorkbookOpen function, passing the name of the file you are checking, and it will return True or False accordingly. I put some code in so that the case does not matter.

Option Explicit

Private Sub Command1_Click()
    'Check if Workbook is Open
    If IsWorkbookOpen("OM_Search1.xls") = False Then
        MsgBox "workbook is not open"
    Else
        MsgBox "workbook is open"
    End If
    Unload Me
    End
End Sub
Public Function IsWorkbookOpen(sName As String) As Boolean
    Dim xlApp As Object
    Dim wb As Object
    IsWorkbookOpen = False
    'See if Excel is Running
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then 'Excel not running
        Err.Clear
        IsWorkbookOpen = False
    Else    'Excel is running - check for WB
        For Each wb In xlApp.Workbooks
            If UCase(wb.Name) Like UCase(sName) Then
                IsWorkbookOpen = True
                Exit Function
            End If
        Next wb
    End If
    Set wb = Nothing
    Set xlApp = Nothing
End Function

Hope this helps...

 

[Jayz]

Brilliant job dsi!!. A few tweaks to your code gave me exactly what I wanted. Thanks for all you help.

Regards,
Jay

Here is the modified code for those whom may benefit from it:

Dim xlApp As Object

'Check if Workbook is Open
If IsWorkbookOpen("OM_Search2.xls&quot = False Then

DoCmd.TransferSpreadsheet acExport, 8, "OM_Search_query2", "C:\Genie_Export\OM_Search2.xls", True

Set xlApp = CreateObject("excel.application&quot
xlApp.Visible = True
xlApp.Workbooks.open "c:\GENIE_Export\OM_Search2.xls" 'Path to your file

Else
MsgBox "OM_Search2.xls is currently open. Please close the spreadsheet before exporting_ data. Note: Existing data will be overwritten", vbExclamation
End If