onesaint
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.SpecialCe
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
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.SpecialCe
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
ASKER
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.
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.
ASKER
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?
ASKER
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.
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?
omboBox1.Clear
c = 1
For r = 2 To mwshSource.UsedRange.Rows.
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?
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
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
ASKER
any idea about specifying the cell next to it? and thanks again Randy.
Hello, OneSaint,
You may find the OffSet property useful for this. E.g. rngCell.Offset(0,1)
Cheers,
Randy
You may find the OffSet property useful for this. E.g. rngCell.Offset(0,1)
Cheers,
Randy
ASKER
how do i fill a combo box with the information in the first column in the worksheet (and sort it if possible)?