Finding out is excel is running and if a certain workbook is open?

[Guest]

Hello,

Can anyone guide me to a solution for this issue? I need to export data to
excel and I need to inform the user about the possibilty that they have the
workbook they want to write to opened in Excel.
A way to find out if a certain worksheet exists in that excel workbook is
also welcome as the oCmd.TransferSpreadsheet overwrites existing sheets.

Thx a lot!

 

[Guest]

hi,
I would re-think my actions. assumeing that you are
transfering a query, i would set up excel with msquery
(connect to external) and have the user pull the data into
excel instead of you trying to push the data to excel.
that way you don't have to worry if the file is open or
not. and if you put a button up on the excel sheet to
refresh the query, this would make it easy for the user to
refresh. and they would be working with real time data.
in essence you would be transfer the job to updating the
excel file to the user.
anyway, base on the situation you described, that would be
how i would handle it.

 

[Guest]

Hello,

Not an option I'm afraid, this is how the client wants it. All done from
Access ... an Excel file is one of the options, not the only one (csv, dbf
etc ...)

Thx for your taughts

 

[Graham Mandeno]

Hi Workinghard

You to find out whether a certain worksheet exists in a file, you will have
to open the file using automation, and if the file is already open then that
attempt will fail, so you can kill two birds with one stone:

Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oSht As Excel.Worksheet
On Error GoTo ProcErr
Set oXL = CreateObject("Excel.Application")
Set oWkb = oXL.Workbooks.Open(strExcelFile)
' this will fail if someone had the file open - trap the error
Set oSht = oWkb.Worksheets(strSheetName)
' this will fail if the sheet does not exist - trap the error
MsgBox "WorkSheet already exists"

 

[Jamie Collins]

You to find out whether a certain worksheet exists in a file, you will have
to open the file using automation

Have to? There are various ways of doing so using VBA and I've found
the following to be very fast:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q209189

In comparison, automating Excel and opening the workbook in its native
app is very slow.

Jamie.

--

 

[Guest]

Hello,

I have some issues with the code ...

See comments in line ...

Hi Workinghard

You to find out whether a certain worksheet exists in a file, you will
have
to open the file using automation, and if the file is already open then
that
attempt will fail, so you can kill two birds with one stone:

Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oSht As Excel.Worksheet
On Error GoTo ProcErr
Set oXL = CreateObject("Excel.Application")
Set oWkb = oXL.Workbooks.Open(strExcelFile)

This line does not generate an error ... even if the file is open
.... it is a test to see if the workbook exists, not if it is open as far as
I can find.

' this will fail if someone had the file open - trap the error

Set oSht = oWkb.Worksheets(strSheetName)
' this will fail if the sheet does not exist - trap the error
MsgBox "WorkSheet already exists"

Second problem: Set oXL.Nothing does not shut down EXCEL !

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello,

Can anyone guide me to a solution for this issue? I need to export data to
excel and I need to inform the user about the possibilty that they have the
workbook they want to write to opened in Excel.
A way to find out if a certain worksheet exists in that excel workbook is
also welcome as the oCmd.TransferSpreadsheet overwrites existing sheets.

Thx a lot!

 

[Guest]

Hello,

I found the quit method on the excel app object!

I'm using your code to check for a worksheet exists and the code in the MS
knowledge article for cheking if the workbook is opend.

Thx to both you!

Hello,

I have some issues with the code ...

See comments in line ...

Hi Workinghard

You to find out whether a certain worksheet exists in a file, you will
have
to open the file using automation, and if the file is already open then
that
attempt will fail, so you can kill two birds with one stone:

Dim oXL As Excel.Application
Dim oWkb As Excel.Workbook
Dim oSht As Excel.Worksheet
On Error GoTo ProcErr
Set oXL = CreateObject("Excel.Application")
Set oWkb = oXL.Workbooks.Open(strExcelFile)

This line does not generate an error ... even if the file is open
... it is a test to see if the workbook exists, not if it is open as far
as I can find.

' this will fail if someone had the file open - trap the error

Set oSht = oWkb.Worksheets(strSheetName)
' this will fail if the sheet does not exist - trap the error
MsgBox "WorkSheet already exists"

Second problem: Set oXL.Nothing does not shut down EXCEL !

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello,

Can anyone guide me to a solution for this issue? I need to export data to
excel and I need to inform the user about the possibilty that they have the
workbook they want to write to opened in Excel.
A way to find out if a certain worksheet exists in that excel workbook
is
also welcome as the oCmd.TransferSpreadsheet overwrites existing sheets.

Thx a lot!