Finding out is excel is running and if a certain workbook is open?
worki...@news.postalias
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!
anon...@discussions.microsoft.com
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.
>.
>
worki...@news.postalias
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
<anon...@discussions.microsoft.com> wrote in message
news:0e8601c48ba7$f64ff110$a301...@phx.gbl...
Graham Mandeno
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"
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
<worki...@news.postalias> wrote in message
news:%233R0JS6...@TK2MSFTNGP09.phx.gbl...
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.
--
worki...@news.postalias
I have some issues with the code ...
See comments in line ...
"Graham Mandeno" <Graham....@nomail.please> wrote in message
news:ee1vZO7i...@TK2MSFTNGP15.phx.gbl...
> 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 !
worki...@news.postalias
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!
<worki...@news.postalias> wrote in message
news:%23P%23xBnFj...@TK2MSFTNGP10.phx.gbl...