word and excel talking

[richATrichardjones]

Hello all,
I have a problem with Office VBA. I have happily used VBA within an Excel with forms, functions etc. However my next problem is one I am finding very difficult to solve.

I have two files in a directory; a word file and an excel file. I have an 5 names listed in Excel in cell A1:A5
I want to write a macro in word that will open excel and read in these five values. I want to put them into a combo box on a form Ive created. I know how to do this combobox population part. The bit Im stuck on is opening excel and reading in the five values. Im sure it must be possible but ama bit stuck!
Any help would be appreciated
Thanks in advance
Richard

 

[fumei]

Have you searched in Help, or here for that matter, for how to open up Excel by code? How to get the data from the cells A1:A5?


Gerry

 

[richATrichardjones]

Hi fumei

I have looked in the help and cant really see anything. i can only see how to open another word document from within word. I havent searched this forum which I might tomorrow when I more time

Richard

 

[fumei]

Look upo "open another application" in Help.

Essentially you have to (and this is directly from Help):

Dim xlApp As Object
' Declare variable to hold the reference.

Set xlApp = CreateObject("excel.application")

' You may have to set Visible property to
' True if you want to see the application.
xlApp.Visible = True

' Use xlApp to access Microsoft Excel's
' other objects. This is where you open the
' file and get the data.

xlApp.Quit
' When you finish, use the Quit method to
' close

' Always make sure your objects are destroy to release memory
Set xlApp = Nothing

Try recording a macro in Excel to figure out how to get the data from the cells.

Post back here when you have specific problems.

Gerry

 

[richATrichardjones]

Thanks for your help Gerry.
I'll let you know how I get on
Richard

 

[richATrichardjones]

HI Gerry,
I have messed about with what you said. It "almost" does what I want.
I can get excel to open but the actual excel document doesnt. the code I have so far is

=========================================
Dim xlApp As Object
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True

xlApp.Documents.Open "C:\test 1\rich.xls"

xlApp.Quit
Set xlApp = Nothing

I get a runtime error 438 on the open line. I cannot see what is wrong with it. Ihave lifted the open code from the help where it shows how to open a word document. the only thing I hvae changed is the file to open. Have I missed something blatantly simple!?!?

Richard

 

[richATrichardjones]

Just worked it out myself

The line should be
xlApp.workbooks.open("lbjljlj")

For some reason the code completion didnt work which is really useful to see what methods are available. Got it going now though

Thanks for your help!

 

[PHV]

the code completion didnt work
If you want IntelliSense you have to reference the Excel object library:
menu Tools -> References ...
and replace this:
Dim xlApp As Object
By this:
Dim xlApp As Excel.Application

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[richATrichardjones]

Thanks for the reply PHV
I'll remember that for the future.
You dont realise how useful some features are until you dont have them....
Richard

 

[fumei]

And my terrible mistake regarding "Documents", rather than "Workbooks" (I spend most of my time with Word...); AND the Dim As Object...I just copied and pasted from Help. PHV is, as usual, absolutely correct, Dim blahblah As Excel.Application is the proper syntax; AND forgetting to mention you need the Reference to Excel

Hmmmmmm.

Gerry

 

[richATrichardjones]

Thanks for your help fumei. You got me started which helped me!
Richard