Get the filename of open Excel workbook in Word VBA

Does anyone know how to get the filename of an open Excel wordbook using 开发者_开发知识库Word VBA, so that I can copy some information to my Word document?

This can get a lot more complicated depending on how sure you need to be, and whether it is for personal or public use:

Set objWithName = GetObject("C:\docs\testx.xls") 
Set objClassOnly = GetObject("", "Excel.Application")

Debug.Print objWithName.Name 
Debug.Print objClassOnly.Name

It is possible to have more than one instance of Excel running and each instance may have more than one workbook open, but get object will only return one instance. If you know the name of the file you want, it is a lot easier, because you can use the first version above.

If you know the application will be open and it’ll be the first (if only) instance open, using the following code. In Word, you’ll need to add Excel 12 reference (Tools| References, Microsoft Excel 12.0 Object Library).

Sub test()
    Dim objClassOnly As Excel.Application
    Set objClassOnly = GetObject(, "Excel.Application")
    Debug.Print objClassOnly.Name
    Debug.Print objClassOnly.ActiveWorkbook.Name
End Sub