Please can someone help me out.

I am writing some vba code for Word 2003. How can i do the following:

How can i find out the filename of an excel workbook that is also open so that i can send some information to it.

Thanks in advance,

Hi,
I made this code for my own purpose some years ago.Not actually same as you expected, but hopefully you can take advantage of it..

First, you must add Microsoft Excel Object Library by adding it on Tools>References

Second, modify following code as you need.
Dim ExcelApp As Excel.Application
Dim ExcelFile As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet

Function OpenExcelFile(ExcelFileName As Variant, ExcelSheetName As Variant)
Set ExcelApp = CreateObject("Excel.Application")

On Error GoTo NoFile
Set ExcelFile = ExcelApp.Workbooks.Add(ExcelFileName)
On Error GoTo NOSheet
Set ExcelSheet = ExcelFile.Sheets(ExcelSheetName)

ExcelApp.Visible = True
NoFile:
If Err Then
Err.Clear
MsgBox "No file found. Incorrect path '" & ExcelFileName & "'"
End If

NOSheet:
If Err Then
Err.Clear
MsgBox "No sheet '" & ExcelSheetName & "' found on '" & ExcelFileName & "'"
End If
End Function

Private Sub CommandButton1_Click()
OpenExcelFile "D:\Yitno\Project\Logistic\bom\BOM ADM.xls", "Sheet1"
MsgBox "Contents of Cell (1,1) is '" & ExcelSheet.Cells(1, 1) & "'"
End Sub

Any hesitate please let me know

Hi,
I made this code for my own purpose some years ago.Not actually same as you expected, but hopefully you can take advantage of it..

First, you must add Microsoft Excel Object Library by adding it on Tools>References

Second, modify following code as you need.
Dim ExcelApp As Excel.Application
Dim ExcelFile As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet

Function OpenExcelFile(ExcelFileName As Variant, ExcelSheetName As Variant)
Set ExcelApp = CreateObject("Excel.Application")

On Error GoTo NoFile
Set ExcelFile = ExcelApp.Workbooks.Add(ExcelFileName)
On Error GoTo NOSheet
Set ExcelSheet = ExcelFile.Sheets(ExcelSheetName)

ExcelApp.Visible = True
NoFile:
If Err Then
Err.Clear
MsgBox "No file found. Incorrect path '" & ExcelFileName & "'"
End If

NOSheet:
If Err Then
Err.Clear
MsgBox "No sheet '" & ExcelSheetName & "' found on '" & ExcelFileName & "'"
End If
End Function

Private Sub CommandButton1_Click()
OpenExcelFile "D:\Yitno\Project\Logistic\bom\BOM ADM.xls", "Sheet1"
MsgBox "Contents of Cell (1,1) is '" & ExcelSheet.Cells(1, 1) & "'"
End Sub

Any hesitate please let me know

Hi, thanks for that, I can see how your code works but my problem is that when the vba code is run in Word, the filename of the open Excel spreadsheet is what I need the code to find.

Therefore, what I require is some code hopefully ending with a string which holds the Excel spreadsheet filename.

Can you help with this??

Thanks Jon,

Hi Jon,

You can try this code:

Private Sub CommandButton2_Click()

Dim Excelfilename As String
Set ExcelApp = GetObject(, "Excel.Application")

If TypeName(ExcelApp) <> "Empty" Then
Set ExcelFile = ExcelApp.ActiveWorkbook
Excelfilename = ExcelFile.FullName
ExcelApp.Visible = True
Else
MsgBox "No excel instance found"
End If

End Sub


and let me know if its work fine

Hi Yitno,

Code works fine for me thanks for that.

The one thing to watch out for though is if no Excel workbook is open you get an error message. This is easily rectified with an "on error goto" line.

Once again thanks,

Jon,