none
How to select a workbook from Excel using Word VBA RRS feed

  • Question

  • Hey people,

    I am working on a Word macro that must select a specific range in an Excel Workbook to use this range as reference inside the Word macro.

    I was able to make the macro work, the only problem is that the Excel Workbook is already opened (and it must stay like that), and the code that I got always open the workbook again.

    Here is my code:
    Sub generica1()
    
    Dim excel
    Set excel = CreateObject("excel.application")
    excel.Visible = True
    Dim workbook
    Set workbook = excel.workbooks.Open("C:\Desktop\Pasta2.xlsm")
    Dim worksheet
    Set worksheet = excel.sheets(1)
    Dim rg
    Set rg = excel.Range("B2")
    
    ChangeFileOpenDirectory "C:\Desktop\"
        Selection.InsertFile FileName:=rg, Range:= _
            "", ConfirmConversions:=False, Link:=True, Attachment:=False
    
    
    End Sub
    I believe that the problem is this Open. I tried to change it by activate or select, but failed. Does anyone has an idea of what could I use?
    Set workbook = excel.workbooks.Open("C:\Desktop\Pasta2.xlsm")
    Tuesday, April 16, 2013 2:39 PM

Answers

  • Since you are running from Word, try it this way:

    Sub ExcelFromWordTest()

        Dim rg As Excel.Range
        Dim Excel As Object
        Dim wkbkXLBook As Excel.Workbook
        Dim wkSheet As Excel.worksheet
        Dim tmpWkbk As Object
        Dim strXLWbk As String
        Dim strWkbk As String

        strXLWbk = "C:\Desktop\Pasta2.xlsm"

        On Error Resume Next
        Set Excel = GetObject(, "Excel.Application")
        If Excel Is Nothing Then
            ' no current Excel application
            Set Excel = CreateObject("Excel.Application")
            Set wkbkXLBook = Excel.Workbooks.Open(strXLWbk)
            Excel.Visible = True
        Else
            ' Excel app running
            For Each tmpWkbk In Excel.Workbooks
                If StrComp(tmpWkbk.FullName, strXLWbk, vbTextCompare) = 0 Then
                    ' this is your workbook
                    Set wkbkXLBook = tmpWkbk
                    Exit For
                End If
            Next tmpWkbk
            If wkbkXLBook Is Nothing Then
                ' not open
                Set wkbkXLBook = Excel.Workbooks.Open(strXLWbk)
            End If
        End If

        Set wkSheet = wkbkXLBook.Worksheets(1)
        Set rg = wkSheet.Range("B2")

        ChDir "C:\Desktop\"
        rg.InsertFile FileName:=rg.Value, Range:= _
                      "", ConfirmConversions:=False, Link:=True, Attachment:=False

    End Sub

    • Marked as answer by m.roza Tuesday, April 16, 2013 7:12 PM
    Tuesday, April 16, 2013 6:17 PM

All replies

  • Change

    Dim workbook
    Set workbook = excel.workbooks.Open("C:\Desktop\Pasta2.xlsm")

    to

    Dim Workbook As Excel.Workbook
    On Error GoTo OpenBook

    Set Workbook = Excel.Workbooks("Pasta2.xlsm")
    GoTo AlreadyOpen
    OpenBook:
    Set Workbook = Excel.Workbooks.Open("C:\Desktop\Pasta2.xlsm")
    AlreadyOpen:

    Tuesday, April 16, 2013 2:59 PM
  • Thanks for your help, but unfortunately it didn't work.

    It worked the same way that my code, the Workbook that opened was just a copy of the original "Pasta2.xlsm", not the one that is already opened and edited.

    I guess this is very simple to solve, but I really can't find a way to make it work.
    Tuesday, April 16, 2013 3:28 PM
  • The code does work, and we'll figure out why it doesn't work in your situation.

    Run this when your Pasta2.xlsm is open. Does the messge box say exactly "Pasta2.xlsm"?

    Dim wk As Workbook

    For Each wk In Workbooks
    If wk.Name Like "*Pasta*" Then MsgBox wk.Name
    Next wk


    Tuesday, April 16, 2013 4:40 PM
  • Ok, that's weird. The first time that I ran it, the message was: Pasta2.xlsm

    Then I ran it again and it didn't show anything. In both situations the Pasta2.xlsm was open.

    Tuesday, April 16, 2013 5:31 PM
  • Since you are running from Word, try it this way:

    Sub ExcelFromWordTest()

        Dim rg As Excel.Range
        Dim Excel As Object
        Dim wkbkXLBook As Excel.Workbook
        Dim wkSheet As Excel.worksheet
        Dim tmpWkbk As Object
        Dim strXLWbk As String
        Dim strWkbk As String

        strXLWbk = "C:\Desktop\Pasta2.xlsm"

        On Error Resume Next
        Set Excel = GetObject(, "Excel.Application")
        If Excel Is Nothing Then
            ' no current Excel application
            Set Excel = CreateObject("Excel.Application")
            Set wkbkXLBook = Excel.Workbooks.Open(strXLWbk)
            Excel.Visible = True
        Else
            ' Excel app running
            For Each tmpWkbk In Excel.Workbooks
                If StrComp(tmpWkbk.FullName, strXLWbk, vbTextCompare) = 0 Then
                    ' this is your workbook
                    Set wkbkXLBook = tmpWkbk
                    Exit For
                End If
            Next tmpWkbk
            If wkbkXLBook Is Nothing Then
                ' not open
                Set wkbkXLBook = Excel.Workbooks.Open(strXLWbk)
            End If
        End If

        Set wkSheet = wkbkXLBook.Worksheets(1)
        Set rg = wkSheet.Range("B2")

        ChDir "C:\Desktop\"
        rg.InsertFile FileName:=rg.Value, Range:= _
                      "", ConfirmConversions:=False, Link:=True, Attachment:=False

    End Sub

    • Marked as answer by m.roza Tuesday, April 16, 2013 7:12 PM
    Tuesday, April 16, 2013 6:17 PM
  • THANK YOU very much! It worked like a charm!

    The only changes that I did were in the end:

    ChDir "C:\Desktop\"
        rg.InsertFile FileName:=rg.Value, Range:= _
                      "", ConfirmConversions:=False, Link:=True, Attachment:=False

    Which I changed to my previous version, because yours wasn't working for some reason.

    I was wondering, the "Dim strWkbk As String" is not necessary, right? I've removed it from the code.

    Also, the "Dim tmpWkbk As Object" is for the temporary workbook? You choose not to open the Pasta2 again, right?

    Anyway, thank you again, I was having lots of troubles with this code.

    Tuesday, April 16, 2013 7:11 PM
  • You're right about that other string - I was modifying some other code, and I didn't do a good job of cleaning it out.

    tmpWkbk is used for looping through all workbooks - when Excel is open before running the code, and allows the code to find the workbook of interest if it is open. If it is open, it is not re-opened: if it is not open, it is opened.

    The code works whether Excel is open or not, and if your workbook if open or not.  Your specific situation has Excel open and your workbook open, but the code is flexible enough to still work. 


    Tuesday, April 16, 2013 8:23 PM