Use Excel as a part of Word in VBA

Having a Word template in which a Excel file is incorporated.
I need to be able to open the excel file and read the content of the cells in column A for not empty
and copy it into an other excel file, in which I will have a VLookup to retrieve the description of code in Column A.

I can't figure it out.  I don't know how to open the excel file into Word and be able to read in it.

Can you help please.
Thanks
Nancy
Nancy TherrienTI Tech and NetworkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Is the first Excel file embedded in the Word document?  Or just an external Excel file you need to get data from and ultimately pull into your Word document?


»bp
Nancy TherrienTI Tech and NetworkAuthor Commented:
The Excel file is embedded in the word doc.  I want to copy the data in column A when it is not empty in an other excel file, for me to be able to Vlookup inside of him.
Thanks
Bill PrewIT / Software Engineering ConsultantCommented:
Can you provide a sample of the Word file with the embedded Excel?

What do you have so far for your macro to do this, can you share that?


»bp
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Nancy TherrienTI Tech and NetworkAuthor Commented:
To be honest, I have nothing so far. Just trying to open the excel speadsheets from Word.
I have also manually open the excel embedded files and try to find the lastrow and it gives me not the good information.

dim lastrow as integer
dim i as interger

lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = lastrow to 1 step -1
     If range("A:A" & i) ="" then
         i = i-1
     else
    end if
   msgbox i  ' to let me know what row it is.
Next i
Nancy TherrienTI Tech and NetworkAuthor Commented:
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, that's helpful.  Let me see what I can work out to at least access the content of the embedded sheet.

If you want to think about / share how the other Excel file figures in that would be helpful too.


»bp
Nancy TherrienTI Tech and NetworkAuthor Commented:
Good Morning,
I have found that with the line below I'm able to enter into the embedded excel sheet, but I can't navigate into it.

ActiveDocument.InlineShapes(1).OLEFormat.Open


Thanks,
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, I haven't actually worked with embedded Excel automation before, but made some progress.  Here is a small test routine that I added to the Word doc and that displays the values from column A in the immediate window of the debugger when it is run.  I haven't figured out how to gracefully end the access of the embedded Excel sheet yet, but this might give you some ideas.  There's more to do of course, but it's turning into a bit harder effort than I expected, working with the embedded sheet is a bit more sensitive than the normal external Excel automation I have done a lot of.

You will need to add a reference to the Word VBA to the Excel Object Library too for this...

Sub Test()
    set ole = ActiveDocument.InlineShapes(1).OLEFormat
    ole.Activate
    Set wb1 = ole.Object
    Set ws1 = wb1.WorkSheets(1)
    LastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    For r = 2 To LastRow
        Debug.Print ws1.Cells(r, 1)
    Next r
    wb1.Close False
End Sub

Open in new window


»bp
Nancy TherrienTI Tech and NetworkAuthor Commented:
Good Afternon, Bill

Thanks, with your code I'm able to read the data inside of it.  Now I will have to work in a chart to be able to copy those value into a brand new excel sheet.

If you have more idea please it will be welcome.

Thanks,
Nancy
Nancy TherrienTI Tech and NetworkAuthor Commented:
Here is what I got so far.
I tought that I can use a variant table to store the info and copy it into an other excel files
It doesn't seems to work.
Why ?


Public code As String
Public MonTableau() As String
Public r As Integer


Sub Macro4()
'
    Dim ole As OLEFormat
    Dim appXl As Excel.Application
    Dim Wb As Excel.Workbook
   ' Dim MonTableau() As String
    Dim i As Integer, j As Integer

   
   
    code = "" ' met la variable Code à Vide
    Set ole = ActiveDocument.InlineShapes(1).OLEFormat
    ole.Activate
    Set wb1 = ole.Object
    Set ws1 = wb1.Worksheets(1)
    lastrow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
   
    ReDim MonTableau(lastrow)  ' donne la dimension exacte à mon tableau
   
    For r = 2 To lastrow
        MonTableau(r) = ws1.Cells(r, 3)
        MsgBox MonTableau(r)
    Next r
   
   
'    For R = 2 To lastrow
'       If code = "" Then
'            code = code & " " & "," & ws1.Cells(R, 3)
'        Else
'        code = code & "," & ws1.Cells(R, 3)
'       End If
'    Next R
   
    Set appXl = CreateObject("Excel.Application")
    appXl.Visible = True
    Set Wb = appXl.Workbooks.Open("C:\Users\nancyt\Documents\TestCode.xlsx")
   
    Cells(r, 1) = MonTableau(r)   '  this part is not working

   
 
End Sub
irudykCommented:
I think you need to reference the Wb object

Try:

Wb.ActiveSheet.Cells(r, 1) = MonTableau(r) 

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
In TestCode.xlsx do you want to add a new sheet for the data, or is there already a sheet there you want to place it in.  If the latter, is it the first sheet, or a specifically named sheet?


»bp
Nancy TherrienTI Tech and NetworkAuthor Commented:
hello Bill,
No, I just want to paste the value in a "A" column a Sheet1 of this excel file.

Thanks,
Bill PrewIT / Software Engineering ConsultantCommented:
See if this helps.

Sub Macro4()
'
    Dim ole As OLEFormat
    Dim appXl As Excel.Application
    Dim wb2 As Excel.Workbook
   ' Dim MonTableau() As String
    Dim i As Integer, j As Integer

    
    
    code = "" ' met la variable Code à Vide
    Set ole = ActiveDocument.InlineShapes(1).OLEFormat
    ole.Activate
    Set wb1 = ole.Object
    Set ws1 = wb1.Worksheets(1)
    lastrow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    
    ReDim MonTableau(lastrow)  ' donne la dimension exacte à mon tableau
    
    For r = 2 To lastrow
        MonTableau(r) = ws1.Cells(r, 3)
        MsgBox MonTableau(r)
    Next r
    
    
'    For R = 2 To lastrow
'       If code = "" Then
'            code = code & " " & "," & ws1.Cells(R, 3)
'        Else
'        code = code & "," & ws1.Cells(R, 3)
'       End If
'    Next R
   
    Set appXl = CreateObject("Excel.Application")
    appXl.Visible = True
    Set wb2 = appXl.Workbooks.Open("C:\Users\nancyt\Documents\TestCode.xlsx")
    Set ws2 = wb2.Worksheets(1)
    
    For r = 2 To lastrow
        wb2.Cells(r, 1) = MonTableau(r)   '  this part is not working
    Next r
    
End Sub

Open in new window


»bp
Nancy TherrienTI Tech and NetworkAuthor Commented:
It is not working,  still bugging here

wb2.Cells(r, 1) = MonTableau(r)   '  this part is not working

property or method is not for this object :  error 438

Thanks
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, I will be away for a bit but will test her later and see what I get, and if I can resolve.


»bp
irudykCommented:
Try modifying the line of code so that wb2 is changed to ws2

So:

ws2.Cells(r, 1) = MonTableau(r)   '  this part is not working

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
Yes, that was a typo...

With that small correction the code seems to work in a test here.


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
You could also simplify it a bit moving the data right from the embedded sheet to the external sheet, without the need for an array, as follows...

Option Explicit

Sub Macro4()
    
    Dim ole As OLEFormat
    Dim appXl As Excel.Application
    Dim wb1 As Excel.Workbook
    Dim wb2 As Excel.Workbook
    Dim ws1 As Excel.Worksheet
    Dim ws2 As Excel.Worksheet
    Dim lastrow As Long
    Dim r As Long
    Dim Code As String
    
    Code = "" ' met la variable Code à Vide
    Set ole = ActiveDocument.InlineShapes(1).OLEFormat
    ole.Activate
    Set wb1 = ole.Object
    Set ws1 = wb1.Worksheets(1)
    lastrow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    
    Set appXl = CreateObject("Excel.Application")
    appXl.Visible = True
    Set wb2 = appXl.Workbooks.Open("C:\Users\nancyt\Documents\TestCode.xlsx")
    Set ws2 = wb2.Worksheets(1)
    
    For r = 2 To lastrow
        ws2.Cells(r, 1) = ws1.Cells(r, 3)
        MsgBox ws2.Cells(r, 1)
    
'        If code = "" Then
'            code = code & " " & "," & ws1.Cells(r, 3)
'        Else
'            code = code & "," & ws1.Cells(r, 3)
'       End If
    Next r
    
End Sub

Open in new window


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nancy TherrienTI Tech and NetworkAuthor Commented:
Cool Thanks a lot Bill for your help.
This is working.  I really appreciate your help.
:)
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome, glad I was able to be of some help, thanks for the feedback.


»bp
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.