Ms Word macros to Excel (more...)

[acjeff]

Hi,

I use Word macro to check if an Excel file is alreay opened.

If Not FileOpened(ActiveDocument.Path & "\myFile.xls") Then
Set objExcel = Nothing
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.workbooks.Open(ActiveDocument.Path & "\myFile.xls", 0, True)
Set objWorksheet = objWorkbook.sheets("Sheet1")
objExcel.Visible = True
End If

Function FileOpened(strFileName As String) As Boolean
On Error Resume Next
FileOpened = False
Open strFileName For Binary Access Read Write Lock Read Write As #1
Close #1
If Err.Number <> 0 Then
FileOpened = True
Err.Clear
End If
End Function

The problem are:

1. if myFile is not opened, the code will open it. However, if the code is run again and again, it fails to see the file is already opened so it creates a new Excel object and open the file again and again. How is that?

2. if the file is already opened, how to set the Excel, workbook and worksheet objects to the file and worksheet?

Please help.

Jeff

 

[fumei]

If Not FileOpened(ActiveDocument.Path & "\myFile.xls") Then
    Set objExcel = Nothing
    Set objExcel = CreateObject("Excel.Application")
   Set objWorkbook = objExcel.workbooks.Open(ActiveDocument.Path & "\myFile.xls", 0, True)
    Set objWorksheet = objWorkbook.sheets("Sheet1")
    objExcel.Visible = True
End If

1. You don't have an ELSE.
2. As you have found out, it will never detect if the excel file is open.

Gerry

 

[acjeff]

Ok. Maybe my question should be:

I have an Excel file myFile.xls already opened. Then in Word macro code, how do I set an object to that opened workbook?

I tried

Set objWorkbook=Workbooks(myFile.xls")

but I got an error of Run-time error '9': Subscript out of range.

And I tried Msgbox Workbooks.Count and it returns 0. Why can't it see the opened file?

Please help.

 

[PHV]

Have a look at the GetObject function (as Excel is already running ...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886