Test for already Opened Excel Spreadsheet Fails 2

[DianeA]

I have an Access Form which passes a value to an excel spreadsheet. I want to test to ensure that the spreadsheet is not already open prior to me giving the OPEN cmd. If it is OPEN then proceed to select the sheet and move data in.

I have two issues but lets start with this one....

1. If I start with the excel file open and then perform this macro the Function ISOPEN does not perform "For Each wb in Excel.Workbooks" and instead goes directly to IsOpen=False and hence proceeds to open a "read-only" copy of the spreadsheet again.

See code below

____________________________________________
Function IsOpen(FILE As String) As Boolean
Dim wb As Excel.Workbook
For Each wb In Excel.Workbooks
If wb.Name = FILE Then
IsOpen = True
GoTo exitSub
End If
Next wb
IsOpen = False
exitSub:
End Function

______________________________________________

Private Sub ToExcel_Click()
Dim app As New Excel.Application
Dim sht As Excel.Worksheet
Dim wbk As Excel.Workbook

Set app = CreateObject("Excel.Application")

With app

If IsOpen(FILE) Then
GoTo skip
End If

.Visible = True
Set wbk = app.Workbooks.Open(PATH & FILE)
skip:
Set sht = .Worksheets("calcs")
sht.Select
.Range("P20").Select
.Range("P20") = TTL

End With
Set app = Nothing
Set wbk = Nothing

EndSub

 

[DianeA]

Correction: It does execute the "For Each wb In Excel.Workbooks" but then skips directly to IsOpen=False

The Value to wb = Nothing at this stage and the value of FILE is equal to the correct file name.

 

[Bong]

Is FILE a fully qualified file name? I think it has to be as that is what wb.Name will be.

_________________
Bob Rashkin

 

[DianeA]

FILE shows "IncrementalTEST2.xls"

But wb=Nothing

 

[DianeA]

It appears that it does not recognize that there is already an instance of excel running with the specific file open. It will create another instance of excel with a read-only copy of the file in it. How do I re-gain focus on an already open instance of excel?

 

[DianeA]

Sorry for all my postings but i am actively working on it and find different things which may help u to help me..

I changed my code around to first look to see if Excel is open and then test for the file: It does see Excel open and skips to "IF IsOpen(FILE) Then"

wb is still "nothing" and file is "IncrementalTEST2.xls at the end of this routine


______________________________________________
Set app = GetObject(, "Excel.Application")
If app Is Nothing Then
' Excel is not running, create new instance
Set app = CreateObject("Excel.Application")
app.Visible = True
End If


If IsOpen(FILE) Then
GoTo skip
Else
End If

 

[xlbo]

try this:

For Each wb In Excel.Workbooks
msgbox wb.name
next

That may shed some illumination on the issue...

You may also wish to reconsider using a variable named "FILE". Not sure if it is a reserved name but worth a try..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rmikesmith]

Diane,

I think you were on the right track but didn't go quite far enough. The main issue with your IsOpen function is that no actual reference to an instance of Excel is set. I was able to duplicate that execution skips the entire loop. I have rewritten your procedure to streamline things (mainly to eliminate the goto's) and include an explicit Excel instance as a parameter to the function. I converted your IsOpen function to return a reference to the indicated workbook, if open, rather than simply TRUE/FALSE. Just saves a step.

Sub SendToExcel()
Dim XLApp As Excel.Application
Dim wkbTarget As Workbook
Dim bNewExcelInstance As Boolean

   'For testing/demonstration:
   sPATH = ThisWorkbook.Path & "\"
   sFILE = "IncrementalTEST2.xls"

   Set XLApp = GetObject(, "Excel.Application")
   If XLApp Is Nothing Then
     ' Excel is not running, create new instance
     Set XLApp = CreateObject("Excel.Application")
     XLApp.Visible = True
     bNewExcelInstance = True
   End If
   
   Set wkbTarget = GetWorkbookReference(sFILE, XLApp)
   If wkbTarget Is Nothing Then
     Set wkbTarget = XLApp.Workbooks.Open(Filename:=sPATH & sFILE)
   End If

   With wkbTarget.Worksheets("calcs")
     .Range("P20") = TTL
   End With
   
   wkbTarget.Close SaveChanges:=True
   
'Clean up:
   If Not wkbTarget Is Nothing Then Set wkbTarget = Nothing
   If Not XLApp Is Nothing Then
     If bNewExcelInstance Then
       XLApp.Quit
     End If
     Set XLApp = Nothing
   End If
   
End Sub


Function GetWorkbookReference(ByVal WkbFile As String, XLInstance As Excel.Application) As Excel.Workbook
Dim wkbTest As Excel.Workbook

   For Each wkbTest In XLInstance.Workbooks
     If wkbTest.Name = WkbFile Then
       Set GetWorkbookReference = wkbTest
       Exit For
     End If
   Next wkbTest
   
End Function

Notes:
I have assumed that your procedure would close the workbook after updating. If that's not the case just remove the wkbTarget.Close statement.

I changed the name of the 'FILE' (and 'PATH') variables slightly in line with Geoff's suggestion. Also, for my testing purposes, I assigned values to these within the main procedure. In your app, it looks these are global vars set elsewhere. Just remove those assignments.

The code has been tested in Access.

Regards,
Mike

 

[DianeA]

Hi Mike,

I first want to thank u for your time in cleaning this up... as a VBA newbie this certainly has taught me plenty..

I have dumped your code in, ran it once, then ran it a second time and have experienced this error:
(Excel is not open)

Run-Time Error 429:
ActiveX component can't create object

This happens on:
"Set XLApp = GetObject(, "Excel.Application")

First run through (Excel was not open).. XPApp must have NOT been equal to "Nothing" because it did not execute the "Set XLApp = CreateObject("Excel.Application")" etc.


This seemed to be working yesterday when I ran these statements so I'm not sure if there is corruption in the Registered components or what:

I found Microsoft article to reregister the DAO dll which I did.

I checked my registered components and think I have everything I need:

MS Excel 11.0 Object Library
MS DAO 3.6 Object Library
MS ActiveX Data Object Library 2.1
MS ActiveX Data Object (Multidimensional) 2.8 Library
MS Access 11.0 Object Libary

 

[DianeA]

Mike... I found this which answers the question i think

http://support.microsoft.com/kb/288902

 

[rmikesmith]

DianeA,

Just add an On Error Resume Next statement before the GetObject line. I only tested my code with Excel already running, so missed it. If you are using other error trapping be sure to reinstate this after the GetObject/CreateObject section.


Regards,
Mike

 

[DianeA]

Thanks Mike... I have added the On Error Resume Next and it takes care of the problem... will do some testing now and let you know..

 

[JerryKlmns]

If you want to find if a file is in use not only by the user trying to check this but from any1, when you try to move it with the NAME statement, you 'll get an error.

Scroll down this thread222-1417879 to see a similar solution

 

[DianeA]

I think we have it running pretty smooth right now... I'm sure I'll run into instances I haven't thought of until we put it into production...

One kink i'm trying to iron out is the message i get when a spreadsheet is opened... i.e. "This workbook contains links to other data sources"... and requies a YES to update or NO to keep...

... how do you response to these specific questions when you can't trap them?


Thanks again
diane

 

[DianeA]

Mike... one change I did make what the because I was getting and error
sPATH = ThisWorkbook.Path & "\"

I replaced this with the actual path to the file.

Can u help me understand how this would understand where the path to the file is if it is not open to begin with.

thanks
Diane

 

[rmikesmith]

I changed the name of the 'FILE' (and 'PATH') variables slightly in line with Geoff's suggestion. Also, for my testing purposes, I assigned values to these within the main procedure. In your app, it looks these are global vars set elsewhere. Just remove those assignments.

I added these for my testing only since I didn't have access to your environment. I had to assume your code was assigning values elsewhere, hence my instruction to remove my assignments in the procedure.

Can u help me understand how this would understand where the path to the file is if it is not open to begin with.

I'm not exactly sure what you mean by this question. The path is not used to detect an open workbook, only to open one otherwise. You or your code obviously must determine the correct path to the workbook file in order to open it. Maybe I'm not understanding what you're asking.


Regards,
Mike

 

[DianeA]

you answered my question in the 1st part and hence second part is void!!! .. thanks

Any comment on posting 28 Nov 07 11:04??

thanks

 

[rmikesmith]

Diane,

Try

Set wkbTarget = XLApp.Workbooks.Open(Filename:=sPATH & sFILE[COLOR=red], UpdateLinks:=True[/color])

or set UpdateLinks to False, as the case may be.


Regards,
Mike

 

[DianeA]

cool... where can i find those different parameters for the open stmt?

 

[rmikesmith]

Diane,

In the VB Editor, press F2 to display the Object Browser (or View|Object Browser). Select the object (left pane) then a property, method or event (right pane) then right-click and Help for help on that item.


Regards,
Mike