Interact with Excel Spreadsheet and insure it is closed.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am setting up code to update data to an Excel spreadsheet through Access.
It works correctly unless the Excel worksheet is already opened. Here is a
sample of the code below issued from Access. Is there a way to test if the
spreadsheet is already opened and if so, issue a message to the user to close
it or close it thorough the program ?

Set xlApp = CreateObject("Excel.Application")
Set wrk = xlApp.Workbooks.Open(Path + file)
Set db = CurrentDb
Set rst = db.OpenRecordset("qryBPSExcel")
rst.MoveFirst
wrk.ActiveSheet.Cells(r, 1) = rst(0)
 
Hi,


Try something like


Do

On Error Resume Next
Set wrk=GetObject(Path+file, "Excel.Application")
If 0=Err.Number then Exit Do

On Error Resume Next
Set xlApp=CreateObject("Excel.Application")
If 0 <> Err.Number then
... ' can't create an Excel object at all
End If

Set wrk=xlApp.Workbooks.Open(Path + file)
if 0 = err.Number then Exit Do

... ' can't open the file (it exists ? )

Loop



Note that I used a Do-Loop structure to avoid GO TO, but in this context, my
feeling is that a GO TO would be quite acceptable too. In fact, I would
probably use a subroutine rather than a Do-Loop structure, personally, in
order to preserve the previous "On Error" structure existing before this
sequence.

Note that the logic is about to try to GET an existing object first, and
only if it fails, to create one.




Hoping it may help,
Vanderghast, Access MVP
 
Michel,

I entered the following:
Set wrk = xlApp.Workbooks.Open(Path + file)

And got this:

Err.Number 432
Err.Description: File name or class name not found during Automation operation
 
Hi,


The following works for me:



========================
Public Sub Testing()
Dim wrk As Object
Dim xlApp As Object

On Error Goto 0

Set xlApp = CreateObject("Excel.Application")
Debug.Assert Not xlApp Is Nothing

Set wrk = xlApp.Workbooks.Open("C:\Book1.xls")
Debug.Assert Not wrk Is Nothing

End Sub
==========================


I suspect you have a problem with the file name.


Vanderghast, Access MVP
 
I think were getting closer. I put in your code and the program was able to
update the Excel spreadsheet. The problem is when the spreadsheet is already
opened, I want to be able to handle the error. In this way if the user has
the spreadsheet opened, I can close it or send a message. Here is the
entire code with your code embedded; the error is listed below the program:

Program
Sub TestIfSpreadSheetOpen()
Dim fpath As String
Dim xlApp As Excel.Application
Dim wrk As Workbook
Dim Path As String
Dim file As String
Set xlApp = CreateObject("Excel.Application")
Path = "C:\Documents and Settings\Ralph Murgia\My Documents\DataExcel\"
file = "AccessTest1.xls"

On Error GoTo 0

Set xlApp = CreateObject("Excel.Application")
Debug.Assert Not xlApp Is Nothing
Set wrk = xlApp.Workbooks.Open(Path + file)
Debug.Assert Not wrk Is Nothing

wrk.ActiveSheet.Cells(1, 4) = "Test"
wrk.Save
wrk.Close
Set xlApp = Nothing

End Sub

Error When File is Opened:
Run-Time error ‘1004’

‘AccessTest1.xls’ is read-only. To save a copy, click OK, then give the
workbook a new name in the Save As dialog box/
 
Hi,


If there is such an error, you may assume that is because the file is
already open, and thus, try




Set wrk = GetObject("C:\book1.xls")



to get your hands on the already open object (instead of creating it with
CreateObject).



Hoping it may help,
Vanderghast, Access MVP
 
Okay great, it seems to work now as far as not getting the error. The only
issue which has been the issue all along is that when the spreadsheet is
opened and you issue command to update it and then save it, it won't let you
save it under the same name. Is there a way to test if its opened and if it
is close it, or save it anyway and override the message ?
 
Hi,


If GetObject does not err, you can assume the file is already opened. Since
your application is not aware of the state of the file, you can't just close
it, in general. You may better ask the end user to close it for you.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top