Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Getting filename of open excel file in Word vba

JonArcherJonArcher Posts: 3Member
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

  • yitnoyitno Posts: 4Member
    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
  • yitnoyitno Posts: 4Member
    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
  • JonArcherJonArcher Posts: 3Member
    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,
  • yitnoyitno Posts: 4Member
    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
  • JonArcherJonArcher Posts: 3Member
    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,
Sign In or Register to comment.