Setting Close without Open

[rlarkin]

Hello,

I'm using VBA in Access to open Excel workbooks with the following code:

If Not FileLocked(strFileName) Then
    Set objApp = New Excel.Application
    With objApp        
         Set objBook = .Workbooks.Open(strFileName, , False)
    End With
End If

The If statement checks if the file is already open, for possible occasions where the workbook wasn't closed previously. In an instance where that does happen, how would I call a close for the previously opened file?

Thanks,

Russ

 

[CBrighton]

I don't think you can remotely close someone elses connection to a spreadsheet via VBA.

If it's closing a copy which you have open on your own PC then you may be able to find a way to close it.

 

[rlarkin]

Sorry, yeah should have mentioned, it's a local file.

The file stays open while a form is open, which could be for some time. Just in case there is a problem that means Access doesn't close properly, and the Excel workbook is stuck open in the background, I'm looking for a way to close the workbook when Access is reopened and then closed properly.

I've tried to find ways to set objBook to the open workbook, without opening it again, but haven't been able to figure out the combination to make it work.

 

[vbaInet]

If you know the full path to the file, you might be able to GetObject(), change UserControl and Close the object.

 

[CBrighton]

A google search found this in another forum thread:

Set xlApp = GetObject(, "Excel.Application")

It's not something I've used but it looks like it trys to get an open spreadsheet (taken from here: http://www.tek-tips.com/viewthread.cfm?qid=90756&page=1)

Equally, you could easily kill and Excel tasks.

However I'm not sure how you would capture a specific open instance Excel (capture may not be the right word, but you know what I mean).

 

[vbaInet]

However I'm not sure how you would capture a specific open instance Excel (capture may not be the right word, but you know what I mean).

Set xlApp = GetObject("Path to xls file")

 

[CBrighton]

Set xlApp = GetObject("Path to xls file")

Will it work on an open file though, rather than providing the location of the file to open?

I guess if GetObject (as opposed to CreateObject) accepts the location of a spreadsheet it must work, otherwise why would it accept a location.

 

[vbaInet]

It should, which was why I mentioned changing the UserControl before attempting to close it.

 

[rlarkin]

Great! Easy when you know how. Thanks everyone.

 

[vbaInet]

The fundamental difference between GetObject() and CreateObject() is GetObject() sets a ref to an already running instance of an object whilst CreateObject() simply creates a new one.

Good job rlarkin!