?
Solved

VBA: read Excel workbook from Word VBA and insert cell contents into user form.

Posted on 2006-04-06
9
Medium Priority
?
917 Views
Last Modified: 2008-03-17
Hello All,

ive got a user form written in Word VBA that requires user input and then fills out a word document. What i would like to be able to do is open an excel work book and take the contents of the first cell and enter it into a textbox. Based upon whats in the first cell there will be a corresponding address. The address will be broken into street and city-state-zip which will go into a second and third text box.

 I would also like to be able to write to the excel workbook in the event that the contact is new and not in the workbook. A1 being the name, B1 is the street address, C1 is the city state and zip, D1 might be some other info, etc… I can have an if then statement with a check box in the user form if I know the contact is going to be new.

I have already set up the object libraries in word vba for excel and has a decent understanding of VB. Thank you in advance for any help.
0
Comment
Question by:onesaint
  • 5
  • 4
9 Comments
 
LVL 12

Accepted Solution

by:
omegaomega earned 1400 total points
ID: 16398672
Hello, OneSaint,

I have pasted a sample below that I hope will help get you started.  Just remember to add a reference to the Microsoft Excel Object Library so that you can access its objects.  I added a button to the Word document that just opens UserForm1 in its Click event.  UserForm1 just has a single TextBox and a single Button on it.  The contents of the first cell of a test workbook are loaded into the TextBox when the Form opens.  Clicking the button saves the contents of the TextBox back to the cell, saves and closes the workbook, quits Excel and closes the form.

Once you get going, I'm afraid that you will find a lot of "little" things that will need to be dealt with.  (Like what action should be taken if the Excel File is missing.  Or what should happen if someone (something) else has the Excel file open when you are trying to do things with it. Or what happens when the text stored back to the cell starts with an "="... )

Good luck!

Cheers,
Randy

In the Word ThisDocument object:
=================================

Option Explicit

Private Sub CommandButton1_Click()

    UserForm1.Show vbModal
   
End Sub

=================================

And in UserForm1:
=================================
Option Explicit

Private mappExcel As Excel.Application
Private mwbkSource As Excel.Workbook
Private mwshSource As Excel.Worksheet

Private Sub UserForm_Initialize()

    Set mappExcel = New Excel.Application
    Set mwbkSource = mappExcel.Workbooks.Open("J:\Test\Word+Excel\MyBook1.xls")
    Set mwshSource = mwbkSource.Worksheets("Sheet1")
   
    TextBox1.Text = mwshSource.Cells(1, 1).Value
   
End Sub

Private Sub CommandButton1_Click()

    mwshSource.Cells(1, 1).Value = TextBox1.Text
   
    mwbkSource.Save
    mwbkSource.Close
    mappExcel.Quit
    Set mwshSource = Nothing
    Set mwbkSource = Nothing
    Set mappExcel = Nothing
   
    Unload UserForm1

End Sub

=================================

0
 

Author Comment

by:onesaint
ID: 16402654
good start randy,
 
how do i fill a combo box with the information in the first column in the worksheet (and sort it if possible)?
0
 
LVL 12

Expert Comment

by:omegaomega
ID: 16404036
Hello, OneSaint,

There are many ways to skin the cat.  I can give you some suggestions, but I can't give you a definitive answer because I don't know the details about your requirements.  (Nor do I want to.)   ;-)  

One simple approach that is generally flexible can be illustrated by adding the following lines to UserForm_Initialize:

    Dim intRows As Integer

    mwshSource.UsedRange.Sort Key1:=mwshSource.Cells(1, 1)
   
    intRows = mwshSource.Cells.SpecialCells(xlCellTypeLastCell).Row
    For Each rngCell In mwshSource.Range("A1:A" & intRows).Cells
        ComboBox1.AddItem rngCell.Value
    Next rngCell
   
where, of course, ComboBox1 is the name of the combo-box on your UserForm.  There are many other methods.  You can treat the Excel sheet like a database and bind the ComboBox to the resulting "dataset".  You can load the column into a variant array (or an array variant), sort it "manually" and set the ComboBox's List property.   You can decide to use VB that includes a ComboBox with an automatic Sort feature.  The list of possibilities goes on, and each method has its advantages.  What the best method is in your case depends on what it is you are really trying to achieve.

But this is probably a good time to ask yourself if YOU know all the details of your requirements.  Once you are convinced that you do, spend some time learning about and evaluating the tools that you have available.  Then decide how to proceed.  You can probably get good answers to each individual question you ask in a group like this.  But if you try to cobble together a project by adding pieces on an ad-hoc basis and combining the un-coordinated ideas about how to implement each one, the end result will be unsatisfactory.

BTW.  It sounds like you are intending to use Excel as a database. Many people do this, and the reason is usually because they know how to use Excel and think that it's too much trouble to learn/use something else.  Please don't do this.  Excel is a wonderful tool, but it makes a terrible DataBase.  Look into using Access for this instead.  

Cheers,
Randy


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:onesaint
ID: 16404367
Randy,

i am intent on using Excel as a database only because there isn’t a terrible amount of information i need to store, only it needs to be accessed constantly. for instance i have 5 word docs that all need to have the same info entered into them in the same place (contact info). But we don’t deal with a whole lot of contacts. Additionally, you are right. I am not totally comfortable using access for DBs. I have some macro experience with both word and excel, but none with access. so its foreign to me. However I am not unwilling to learn or start trying it.  i appreciate all the help.
0
 

Author Comment

by:onesaint
ID: 16404560
so Randy, would you mind posting another method as well? rngcell isnt deifned so i added  Dim rngCell As Range but now im running into data or method not found in regards to "value" in this line "ComboBox1.AddItem rngCell.Value." any ideas?
0
 

Author Comment

by:onesaint
ID: 16405036
K. SO i figured out how to populate the combo box with a cell from column 1:

omboBox1.Clear
    c = 1
    For r = 2 To mwshSource.UsedRange.Rows.Count
        ComboBox1.AddItem mwshSource.Cells(r, c)
    Next r

however now i need to specify the cell b in the same row. any ideas? and would this be easier in access?
0
 
LVL 12

Expert Comment

by:omegaomega
ID: 16406328
Hello, OneSaint,

Yes, sorry I missed copying the declaration for rngCell into my note.  It is:

    Dim rngCell As Excel.Range
   
and the "Excel." part seems to be important.  (Perhaps Word also has a Range type.  I don't remember this well.)

Cheers,
Randy
0
 

Author Comment

by:onesaint
ID: 16409804
any idea about specifying the cell next to it? and thanks again Randy.
0
 
LVL 12

Expert Comment

by:omegaomega
ID: 16414894
Hello, OneSaint,

You may find the OffSet property useful for this.  E.g. rngCell.Offset(0,1)

Cheers,
Randy
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Simple Linear Regression
Progress

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question