VBA: Trying to Close Workbook, nothing happens (1 Viewer)

[Bill Bisco]

Dear all,

I apologize for the thread. I have looked up similar threads, but am still having trouble. Any help would be appreciated.

This VBA Code is being executed from Access. I am trying to make a code to check if an Excel workbook is open and if it is open, to close the workbook. The workbook needs to be closed because further VBA code is going to delete the folder containing the Excel workbook. I am using the following code currently:

 'the following function will get the path only (i.e. the folder) from the file's full path:
Function FilePath(strPath As String) As String
    FilePath = Left$(strPath, InStrRev(strPath, "\"))
End Function

Sub test22()
Dim Source As String, Wb As Workbook

Source = FilePath(CurrentProject.FullName) & "Resources\HPUCPUDataEntry.xlsm"

Set Wb = Workbooks.Open(Source)
Workbooks(Wb.Name).Close

End Sub

Currently, no file is closed with the above code. What am I doing wrong?

 

[ByteMyzer]

Why not simply use:

Wb.Close

 

[namliam]

The problem with closing manually opened sheets is that a user can be on a different machine, in which case you are "screwed" cause you cant do anything about that.

Next a user on this computer can have multiple sessions of excel open each with multiple workbooks.... Thus you will need to "get" the excel sessions and "search" them for the workbook. To get existing sessions (if I remember correctly) you need to use GetObject, but at this moment am unsure how it would exactly work.

All in all, try and research that a bit, would be happy to help if you get stuck.

 

[Bill Bisco]

Thanks for your replies. I tried wb.close, but it's possible that there are other instances of excel that are already open. If so, that code doesn't do anything.

I checked up on GetObject, but I'm not sure how to close the workbook I get from GetObject. My code is as follows:

Sub test30()
Dim Source1 As String
Dim ExlObj As Object

Source1 = FilePath(CurrentProject.FullName) & "Resources\HPUCPUDataEntry.xlsm"
Set ExlObj = GetObject(Source1).Application

The above works because I can ask for a message box to give me information from the Excel Spreadsheets cells.

What I need to know now is how could I code something like

If ExlObj Is Open Then
Close ExlObj nosave

Unfortunately, even after using GetObj, the standard Wb.Close method doesn't seem to work because the workbook still shows out of range.

Any help is greatly appreciated,

Bill

 

[DavidAtWork]

Having multiple instances of Excel running will only cause you problems in a programming sense, so a better approach is to test if Excel is running before you open the source file.
Try this recent post
http://www.access-programmers.co.uk/forums/showthread.php?t=253655

Try using this method to open the file

Workbooks.Open Filename:=source

where source is your string variable path and filename also don't forget to check which arguments you may need to include.
When you're finished just use the

ActiveWorkbook.Save
ActiveWorkbook.Close

David

 

[Bill Bisco]

Hi David,

Thanks for the tip. Unfortunately, that method still is incapable of closing it.

My code is as follows:

Sub test30()
Dim Source1 As String
Dim ExlObj As Object

Source1 = FilePath(CurrentProject.FullName) & "Resources\HPUCPUDataEntry.xlsm"
Set ExlObj = GetObject(Source1).Application

Workbooks.Open fileName:=Source1
ActiveWorkbook.save
ActiveWorkbook.Close

End Sub

Strangely, the workbook code below activates after this line:

Set ExlObj = GetObject(Source1).Application

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Saved = True
End Sub

I would think that the workbook opening code below would kick off during the code run, but it doesn't.

Private Sub Workbook_Open()
    ThisWorkbook.Saved = True
End Sub

In fact, running my main code, I never see the workbook open visually at all. When I do have the workbook already open when running the code, it doesn't disappear. :banghead:

Any help is appreciated.