Answered by:
How to select a workbook from Excel using Word VBA
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")
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 StringstrXLWbk = "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 IfSet wkSheet = wkbkXLBook.Worksheets(1)
Set rg = wkSheet.Range("B2")ChDir "C:\Desktop\"
rg.InsertFile FileName:=rg.Value, Range:= _
"", ConfirmConversions:=False, Link:=True, Attachment:=FalseEnd Sub
 Marked as answer by m.roza Tuesday, April 16, 2013 7:12 PM
All replies

Change
Dim workbook
Set workbook = excel.workbooks.Open("C:\Desktop\Pasta2.xlsm")
to
Dim Workbook As Excel.Workbook
On Error GoTo OpenBookSet Workbook = Excel.Workbooks("Pasta2.xlsm")
GoTo AlreadyOpen
OpenBook:
Set Workbook = Excel.Workbooks.Open("C:\Desktop\Pasta2.xlsm")
AlreadyOpen: 
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. 
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 Edited by Bernie Deitrick, Excel MVP 20002010 Tuesday, April 16, 2013 4:41 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 StringstrXLWbk = "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 IfSet wkSheet = wkbkXLBook.Worksheets(1)
Set rg = wkSheet.Range("B2")ChDir "C:\Desktop\"
rg.InsertFile FileName:=rg.Value, Range:= _
"", ConfirmConversions:=False, Link:=True, Attachment:=FalseEnd Sub
 Marked as answer by m.roza Tuesday, April 16, 2013 7:12 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:=FalseWhich 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.

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 reopened: 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.
 Edited by Bernie Deitrick, Excel MVP 20002010 Tuesday, April 16, 2013 8:24 PM