Check if Excel workbook is open via Word VBA, if open how to close it?


Joined
Mar 21, 2012
Messages
2
Reaction score
0
Hi everyone,
I am stucked with the issue like the title. Any one help me?
it always pop an error "subscript out of range"
Here is my code in Word VBA:

Sub OpenExcel()
'Checking if Appendix data source Excel file is already in open state
If IsExcelOpen("a.xls") Then
Workbooks("a.xls").Close 'The issue is here
Else
'Opening Excel workbook with Appendix Tables
Set wbWorkBook = appExcel.Workbooks.Open("a.xls")
End If
End Sub

'Checking if Excel file is open
Function IsExcelOpen(strFileName As String) As Boolean
Dim lFilenum As Long
Dim lErr As Long

On Error Resume Next
lFilenum = FreeFile()
Open strFileName For Input Lock Read As #iFilenum
Close iFilenum
lErr = Err
On Error GoTo 0

Select Case lErr
Case 0: IsExcelOpen = False
Case 70: IsExcelOpen = True
Case Else: Error lErr
End Select
End Function
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top