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

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.
onesaintAsked:
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.

omegaomegaDeveloperCommented:
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

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
onesaintAuthor Commented:
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
omegaomegaDeveloperCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

onesaintAuthor Commented:
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
onesaintAuthor Commented:
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
onesaintAuthor Commented:
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
omegaomegaDeveloperCommented:
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
onesaintAuthor Commented:
any idea about specifying the cell next to it? and thanks again Randy.
0
omegaomegaDeveloperCommented:
Hello, OneSaint,

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

Cheers,
Randy
0
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
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.