Getting filename of open excel file in Word vba

Comments

• yitno

  February 2011

  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.
  [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
  
• yitno

  February 2011

  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.
  [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
  
• JonArcher

  February 2011

  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,
  
• yitno

  February 2011

  Hi 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
  
• JonArcher

  February 2011

  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,