Transfering information from Excel to Word, having problems making the code work

Lexus1 · Aug 2, 2018

[FONT=&quot]Dear all,

so recently I have been trying to make Excel transfer information to Word and vice versa. The code below doesn't have a practical purpose beyond learning purposes.

So first I tried this, which results in a subscript out of range error. I would think this would open the excel file and insert the word "Hello" into the cell A1. This has yet no integration with Word:

Code:

[/FONT]
  [FONT=&quot]Sub test555()[/FONT]
  [FONT=&quot]Excel.Application.Workbooks("H:\Macro access file\test.xlsx").Worksheets(0).Range("A1").Value = "Hello"[/FONT]
  [FONT=&quot]End Sub[/FONT]
  [FONT=&quot]

Then I tried this:

Code:

[/FONT]
  [FONT=&quot]Sub Table_test_222()[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Dim myExcel As Excel.Application[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Dim myWorkbook As Excel.Workbook[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Dim myValue As Variant[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Set myExcel = CreateObject("Excel.Applicaton")[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Set myWorkbook = myExcel.Workbooks.Open("H:\Macro access file\test.xlsx")[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]myValue = myExcel.myWorkbook.Range("A1").Value[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]myWorkbook.Close[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Set myExcel = Nothing[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]ActiveDocument.Tables(1).Cell(1, 1).Range.InsertAfter myValue 'this part is word VBA[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]End Sub[/FONT]
  [FONT=&quot]

This code should call the excel sheet test, take the value of the cell A1 and then insert it into the first cell of the first table of an already opened word document. Which also doesn't work.

Code:

[/FONT]
  [FONT=&quot]Sub Table_test_222()[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]'Dim myExcel As Excel.Application[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Dim myWorkbook As Excel.Workbook[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Dim myValue As Variant[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Set myWorkbook = GetObject("H:\Macro access file\test.xlsx")[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]myValue = myWorkbook.Application.Range("A1").Value[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]Set myWorkbook = Nothing[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]MsgBox myValue[/FONT]
  [FONT=&quot] [/FONT]
  [FONT=&quot]End Sub[/FONT]
  [FONT=&quot]

I finally tried this code adapted based on the code example provided in the book "Mastering VBA" but it results in an error 1004 focused on the line "[/FONT]
[FONT=&quot]myValue = myWorkbook.Application.Range("A1").Value". Can someone tell me what I am doing wrong or failing to consider? The macros above are supposed to be run out of word. At this point I did import the Excel office library so that is not the issue. Thanks for any feedback.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]

Kenneth Hobson · Aug 2, 2018

If workbook is open, use 1 as there is not sheet with an index number of 0.

Code:

Excel.Application.Workbooks("H:\Macro access file\test.xlsx").Worksheets(1).Range("A1").Value = "Hello"

Since the workbook is supposed to be open already, you can skip the drive

ath:

Code:

Excel.Application.Workbooks("test.xlsx").Worksheets(1).Range("A1").Value = "Hello"

In the 2nd code, you referenced the object twice. Do it once:

Code:

myValue = myWorkbook.Range("A1").Value

In the 3rd code, Application is not needed but you need to reference a Worksheet, then a Range.

Code:

myValue = myWorkbook.Worksheets(1).Range("A1").Value

Lexus1 · Aug 3, 2018

Thanks for the feedback Kenneth,

If workbook is open, use 1 as there is not sheet with an index number of 0.

I thought that the indexation of objects starts with 0 and not with 1 unless I force it via an option. The interesting thing is, if I use this (while the workbook in question is open), I get a subscript out of range error:

Excel.Application.Workbooks("H:\Macro access file\test.xlsx").Worksheets(1).Range("A1").Value = "Hello"
While if I use any of these:

Code:

Excel.Application.Workbooks("test.xlsx").Worksheets(1).Range("A1").Value = "Hello"

Code:

Worksheets(1).Range("A1").Value = "Hello"

It works normally. So I still don't fully grasp why this error happens. What would I have to do in order to insert "Hello" by opening and closing the Excel sheet?

Since the workbook is supposed to be open already, you can skip the driveath:

Indeed in this case I really just did this in order to get a better understanding of how these objects are connected to one another.

If I use the modification as suggested in the second code I at this point get the error connected to this line "Set myExcel = CreateObject("Excel.Applicaton")" stating that active X component cant create object. Below is the full code:

Code:

Sub Table_test_new()  
Dim myExcel As Excel.Application
  
Dim myWorkbook As Excel.Workbook
  
Dim myValue As Variant
  
Set myExcel = CreateObject("Excel.Applicaton")
  
Set myWorkbook = myExcel.Workbooks.Open("H:\Macro access file\test.xlsx")
  
myValue = myWorkbook.Range("A1").Value
  
myWorkbook.Close
  
Set myExcel = Nothing
  
ActiveDocument.Tables(1).Cell(1, 1).Range.InsertAfter myValue 'this part is word VBA
  
End Sub

Any reason for this error? I can confirm that the Excel and Word libraries are both enabled. I ran this macro out of Word.

Having run the code below as per your modification suggestions, I can confirm that this code works as intended. But I still need to do a bit more research to properly understand the interaction of the individual entries in the code, so I understand why it works.

Sub Table_test_new()
Dim myWorkbook As Excel.Workbook

Dim myValue As Variant

Set myWorkbook = GetObject("H:\Macro access file\test.xlsx")

myValue = myWorkbook.Worksheets(1).Range("A1").Value

Set myWorkbook = Nothing

ActiveDocument.Tables(1).Cell(1, 1).Range.InsertAfter myValue

End Sub

Lexus1 · Aug 3, 2018

One more question Kenneth, where can I find the "edit" button to edit my posts? I see that you edited your post:

Last edited by Kenneth Hobson; Yesterday at 09:44 PM.

but I can nowhere find the "edit" button / function.

Kenneth Hobson · Aug 3, 2018

Excel object is already referenced so Application should not be needed.

Edit option is only available for a short time on some forums. Click your reply to see if available. I often edit code after a post as the first 2 lines merge. It's a vbbulletin deal.

Transfering information from Excel to Word, having problems making the code work

Lexus1

New Member

Excel Facts

Kenneth Hobson

Well-known Member

Lexus1

New Member

Lexus1

New Member

Kenneth Hobson

Well-known Member

Similar threads

Forum statistics

Share this page

Transfering information from Excel to Word, having problems making the code work

Lexus1

New Member

Excel Facts

Kenneth Hobson

Well-known Member

Lexus1

New Member

Lexus1

New Member

Kenneth Hobson

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock