How to close specific excel file from access? (1 Viewer)

frankt68

Registered User.
Local time
Today, 21:53
Joined
Mar 14, 2012
Messages
90
I export data from Access database to an excel file using a macro. It works fine if the excel file is not opened, but it gives an error when it is. I would like to close the excel file before the data export. How can I do that?

I'm using this function
Code:
Public Function IsWorkbookOpen(ByVal strWorkBookName As String) As Boolean     

Dim objExcel As Object    

Dim varWorkbook As Variant On Error GoTo ExitFunction     

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

For Each varWorkbook In objExcel.Workbooks         

If varWorkbook.Name = strWorkBookName Then             

IsWorkbookOpen = True             

Exit For         

End If     

 Next 

Exit Function:

 Set objExcel = Nothing 
End Function
and call it like

Code:
If IsWorkbookOpen("Text.xlsx") = True Then ......
to check if the file is open, but I'm unable to find a way to close it if it is.
I would appreciate some advice on how to close the excel file.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:53
Joined
Feb 28, 2001
Messages
27,001
The question to be considered is to determine how the file got opened if your code wasn't the way it got opened.

Normally, if YOU opened the file, you could try workbook(n).Close to do it.

https://www.excel-easy.com/vba/examples/close-open.html

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.close

If the workbook is open from another process, though, you might not be able to close it from your process because in that case, your process doesn't own the file handle that is associated with the open file. In the simplest case, if it is open because you opened it, there should be no problem using the .Close method as shown in the two links I posted.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:53
Joined
Oct 29, 2018
Messages
21,358
Hi. It may not be what you want, but I'll post this link anyway, just in case it helps. Good luck!
 

frankt68

Registered User.
Local time
Today, 21:53
Joined
Mar 14, 2012
Messages
90
The question to be considered is to determine how the file got opened if your code wasn't the way it got opened.

Normally, if YOU opened the file, you could try workbook(n).Close to do it.

https://www.excel-easy.com/vba/examples/close-open.html

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.close

If the workbook is open from another process, though, you might not be able to close it from your process because in that case, your process doesn't own the file handle that is associated with the open file. In the simplest case, if it is open because you opened it, there should be no problem using the .Close method as shown in the two links I posted.


Thanx, I tried with workbook(n).Close method but it gives me Run-time 9 subscript out of range error.



I guess I'll just add a warning for the users that the file is open and must be closed before proceeding.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:53
Joined
Sep 12, 2006
Messages
15,614
Thanx, I tried with workbook(n).Close method but it gives me Run-time 9 subscript out of range error.

that might well be because the workbook index is zero based.

try
workbook(n-1).Close or
workbook(0).Close or
workbook("name").Close
 

frankt68

Registered User.
Local time
Today, 21:53
Joined
Mar 14, 2012
Messages
90
that might well be because the workbook index is zero based.

try
workbook(n-1).Close or
workbook(0).Close or
workbook("name").Close


Thanks, Dave, I tried but it still gives me Run-time 9 subscript out of range error.
 

frankt68

Registered User.
Local time
Today, 21:53
Joined
Mar 14, 2012
Messages
90
I guess I find a solution - this piece of the code does the trick:


Code:
Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Dim wk As Excel.Worksheet

    On Error Resume Next
    Set xl = GetObject(, "Excel.Application")
    On Error GoTo 0

    xl.Visible = True

    With xl
        .Workbooks("TestFile.xlsx").Close SaveChanges:=False  ' Insert Excel code here
    End With

    Set wk = Nothing
    Set wkbk = Nothing
    Set xl = Nothing
It closes only the TestFile.xlsx.


:)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:53
Joined
Oct 29, 2018
Messages
21,358
I guess I find a solution - this piece of the code does the trick:

Code:
Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Dim wk As Excel.Worksheet

    On Error Resume Next
    Set xl = GetObject(, "Excel.Application")
    On Error GoTo 0

    xl.Visible = True

    With xl
        .Workbooks("TestFile.xlsx").Close SaveChanges:=False  ' Insert Excel code here
    End With

    Set wk = Nothing
    Set wkbk = Nothing
    Set xl = Nothing
It closes only the TestFile.xlsx.

:)
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:53
Joined
Sep 12, 2006
Messages
15,614
out of interest, you used workbooks (plural) in the working code. - In #4, you had workbook (singular)
 

frankt68

Registered User.
Local time
Today, 21:53
Joined
Mar 14, 2012
Messages
90
out of interest, you used workbooks (plural) in the working code. - In #4, you had workbook (singular)


Good observation. It must be workbooks, otherwise, it does not work. I did not pay attention to this fact when I read it, but I used workbooks when I tried it.
 

Users who are viewing this thread

Top Bottom