Please can someone help me out.
I am writing some vba code for [b]Word 2003[/b][color=Red][/color]. 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,
Comments
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.
[italic][color=Blue]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:YitnoProjectLogisticomBOM ADM.xls", "Sheet1"
MsgBox "Contents of Cell (1,1) is '" & ExcelSheet.Cells(1, 1) & "'"
End Sub[/color][/italic]
Any hesitate please let me know
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.
[italic][color=Blue]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:YitnoProjectLogisticomBOM ADM.xls", "Sheet1"
MsgBox "Contents of Cell (1,1) is '" & ExcelSheet.Cells(1, 1) & "'"
End Sub[/color][/italic]
Any hesitate please let me know
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,
You can try this code:
[color=Blue]
[italic]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
[/italic][/color]
and let me know if its work fine
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,