CODEWITHSUNDEEP
vbams-word
Jon Archer
Jon Archer

Reputation: 1

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?

Upvotes: 0

Views: 3573

Answers (2)

ForEachLoop
ForEachLoop

Reputation: 2586

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

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 2

Related Questions