Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Word, PowerPoint, Outlook, and Other Office Products > Open Excel Spreadsheet from Word VBA and Import data from excel

Thread Tools Display Modes
Old 05-01-2009, 07:35 PM
gsatterlee gsatterlee is offline
Join Date: May 2009
Posts: 2
Default Open Excel Spreadsheet from Word VBA and Import data from excel

I have approx. ten thousans copies of the same form to be filled out for the client. Four cells in the table on my form need to be changed on each copy. the data that is needed for one of the cells i must manually enter however the data for the other three of these cells in located in an excel spreadsheet. is there anyway to use VBA in my Word Form to automatically open the excel spreadsheet, use the data i entered manually in the first cell of my word table to search the excel spreadsheet for the corresponding values and import them back into the other three cells in my table in word?

for example:

my table in word
1. 215-AA-1234 (data entered manually)

and the excel spreadsheet
1 A B C D
8 215-AA-1234 AC-9043 Transmitter 5423698

i want to write a VBA in word that can auto open the spread sheetin excel. Use the data "215-AA-1234" from the table cell "1." to find the corresponding row "8" in the spreadsheet. take the values for B, C & D from that row in spreadsheet and fill them into the cells "2, 3, & 4" from the word table.

we have eight staff working on this manually and we are still falling behind, i am doing my best to automate and have made some progress but i think i am a bit out of my league with this problem...
Reply With Quote
Old 05-02-2009, 04:31 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
Join Date: Mar 2005
Location: London, UK
Posts: 3,400

Welcome to the forum!

The best place for you to start is the tutorial on automating Excel using VB6. Automating Excel from VB6 is almost the same (programmatically) as automating Excel from Word VBA so it should give you all the information you need.


Hope that helps!
RAD Excel Blog
Reply With Quote
Old 05-02-2009, 06:38 PM
gsatterlee gsatterlee is offline
Join Date: May 2009
Posts: 2

Colin i appreciate that!

i have been posting on this problem in four different forums for a week now any your the first to touch one. i have a script written in my word document that will communicate with and open the propper excel file i need it to then continues to colse it.

Sub OpenExcelWorkbook()

Dim objXLApplication As Excel.Application
Dim objXLWorkbook As Excel.Workbook

Set objXLApplication = CreateObject("Excel.Application")

Set objXLWorkbook = objXLApplication.Workbooks.Open("C:\Documents and Settings\Gsatterlee\Desktop\Unit 211& 212 Inst Index.xls")

objXLApplication.Visible = True

'........(this is where i need to insert the parts i dont know how to write)

Set objXLWorkbook = Nothing

Set objXLApplication = Nothing

End Sub

i got that part working fine and understand all the basics they repeat in that link you provided. it is the idea if getting my word macro to use the value of a certain cell in my word table as the parameters for a search in the excel workbook it opens up. that seems a bit more advanced to me...
Reply With Quote
Old 05-09-2009, 05:15 AM
Cindy Meister Cindy Meister is offline
Join Date: May 2009
Posts: 20

Hi gsatterlee

This type of thing needs to be approached from two directions, and you may need to ask in more than one group in order to get all the pieces.

1. If you don't know how to get a value from a Word cell, you need to ask that, specifically, in a Word group.

2. If you don't know how to code a Search in Excel, then you need to ask that in a group the specializes in Excel.

Then, it's just a question of putting the two together. You should also remember to mention the version of Office you're using, as that can affect the answer.

So, where, more exactly, are you stuck, currently?
Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..