Critique of code

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Hi,

I consider myself a dabbler in Word VBA and a begginer (is there
anything less skilled than that :-)?) with Excel.

With my technical level established, a user in the Word VBA groups
asked how to populate a dropdown field in a Word document with data
from Excel and then populate other fields in Word with Excel data based
on the item selected in the dropdown.

First of all you can't have a dropdown field in Word that contains more
than 25 items so a Word UserForm is required.

I thought I would have a go at figure out this problem.

I created auser form (named UF) contain one listbox and one command
button.

I call the userform with:

Sub CallUF()
Dim myFrm As UF
Set myFrm = New UF
myFrm.Show
Unload myFrm
Set myFrm = Nothing
End Sub

I created a Excel file named: C:\myWorkbook1
The spreadsheet has 3 columns headed: "Name" "Age" "Address"
I named the excel range containing the data "mydatabase"

Next I populated the listbox from the spreadsheet column 1 using the
Userform Initialize event.

'First, you need to set a reference in your project to the Microsoft
DAO 3.51
'(or 3.6) Object Library.

Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\MyBook1.xls", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mydatabase`")
While Not rs.EOF
Me.ListBox1.AddItem rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

In the document, I created bookmarks "Name" "Age" "Address" where I
wanted the data to appear. I used the Command Button click event to
process the UserForm Listbox selection and populate the data in the
document:

Private Sub CommandButton1_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim oNameRng As Word.Range
Dim oAgeRng As Word.Range
Dim oAddressRng As Word.Range
Dim oBM As Bookmarks
Dim i As Long
Set db = OpenDatabase("C:\MyBook1.xls", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mydatabase`")
Set oBM = ActiveDocument.Bookmarks
Set oNameRng = oBM("Name").Range
Set oAgeRng = oBM("Age").Range
Set oAddressRng = oBM("Address").Range
i = Me.ListBox1.ListIndex
oNameRng.Text = Me.ListBox1.Text
oBM.Add "Name", oNameRng
rs.Move (i)
oAgeRng.Text = rs.Fields(1).Value
oBM.Add "Age", oAgeRng
oAddressRng.Text = rs.Fields(2).Value
oBM.Add "Address", oAddressRng
Me.Hide
End Sub

Like I said, I am no expert with Excel and while it appears to work in
this simplified example it may actually be a real dog's breakfast.

I appreciate any comments that would expain inefficiencies in my method
or explanations of a better method.

Thanks.
 
Greg said:
Hi,

I consider myself a dabbler in Word VBA and a begginer (is there
anything less skilled than that :-)?) with Excel.

With my technical level established, a user in the Word VBA groups
asked how to populate a dropdown field in a Word document with data
from Excel and then populate other fields in Word with Excel data based
on the item selected in the dropdown.

First of all you can't have a dropdown field in Word that contains more
than 25 items so a Word UserForm is required.

I thought I would have a go at figure out this problem.

I created auser form (named UF) contain one listbox and one command
button.

I call the userform with:

Sub CallUF()
Dim myFrm As UF
Set myFrm = New UF
myFrm.Show
Unload myFrm
Set myFrm = Nothing
End Sub

I created a Excel file named: C:\myWorkbook1
The spreadsheet has 3 columns headed: "Name" "Age" "Address"
I named the excel range containing the data "mydatabase"

Next I populated the listbox from the spreadsheet column 1 using the
Userform Initialize event.

'First, you need to set a reference in your project to the Microsoft
DAO 3.51
'(or 3.6) Object Library.

Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\MyBook1.xls", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mydatabase`")
While Not rs.EOF
Me.ListBox1.AddItem rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
Click to expand...


Why bother with DAO, just open a new instance of Excel from within Word

Private Sub UserForm_Initialize()
Dim xlApp As Object
Dim xlWB as Object
Dim xlWS as Object
Dim cRows As Long

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open(("C:\MyBook1.xls")
Set xlWS = xlWB.Worksheets(1)
cRows = xlWS.Range("myDatabase").Rows.Count -
xlWS.Range("myDatabase").Row + 1

In the document, I created bookmarks "Name" "Age" "Address" where I
wanted the data to appear. I used the Command Button click event to
process the UserForm Listbox selection and populate the data in the
document:

Private Sub CommandButton1_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim oNameRng As Word.Range
Dim oAgeRng As Word.Range
Dim oAddressRng As Word.Range
Dim oBM As Bookmarks
Dim i As Long
Set db = OpenDatabase("C:\MyBook1.xls", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mydatabase`")
Set oBM = ActiveDocument.Bookmarks
Set oNameRng = oBM("Name").Range
Set oAgeRng = oBM("Age").Range
Set oAddressRng = oBM("Address").Range
i = Me.ListBox1.ListIndex
oNameRng.Text = Me.ListBox1.Text
oBM.Add "Name", oNameRng
rs.Move (i)
oAgeRng.Text = rs.Fields(1).Value
oBM.Add "Age", oAgeRng
oAddressRng.Text = rs.Fields(2).Value
oBM.Add "Address", oAddressRng
Me.Hide
End Sub

Like I said, I am no expert with Excel and while it appears to work in
this simplified example it may actually be a real dog's breakfast.

I appreciate any comments that would expain inefficiencies in my method
or explanations of a better method.
Click to expand...


then just load the listbox direct

Dim i As Long

With Me.ListBox1
For i = 1 To cRows
.AddItem xlWs.Range("myDatabase").Cells(i,1)
.List(.ListCount - 1, 1) = xlWs.Range("myDatabase").Cells(i,2)
.List(.ListCount - 1, 2) = xlWs.Range("myDatabase").Cells(i,3)

End With
 
Forgot to tidy-up

Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
So, a "dabbler" in Word VBA is also a MVP !! What does that make the
rest of us ?? <bg>

Pete
 
Pete,

When I consider what I know with what there is to be known then regardless
of the letters after my name I am still a dabbler ;-)
 
When you put it that way (consider what I know with what there is to be
known), then there can *only* be dabblers in this world.

It should be ... "consider what I know, with what others know, of what there
is to be known", and then you MVP's are definitely *not* dabblers!<vbg>
 
Pete/RD

It would be rude for me to ignore your compliments. Thank you.

I think most MVPs, Word and others, would say that the MVP program is as
sucessuful as it is because of the collective contributions of the
individual MVPs.

I have had no formal training in VBA. While I plow my way through workable
solutions to many problems, I don't really understand the "object model, or
the difference between a method, variable, procedure etc. Despite the
continued efforts of true masters "Classes" is still a dark territory. For
those reasons and others, I say "dabble. ;-)
 
Bob,

Ok. Thanks for showing me the direct method. I didn't really want to
populate 3 listboxes in a user form. I only wanted to populate the one
listbox with the data in the "Name" column adn then fillin the the
appropriate bookmark in the document with the item selected in the list box
and the other bookmarks with the related data.

Here is the command button click code I used:

Private Sub CommandButton1_Click()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim oNameRng As Word.Range
Dim oAgeRng As Word.Range
Dim oAddressRng As Word.Range
Dim oBM As Bookmarks
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("E:\My Documents\Excel
Files\WordLinkedSpreadsheet.xls")
Set xlWS = xlWB.Worksheets(1)
Set oBM = ActiveDocument.Bookmarks
Set oNameRng = oBM("Name").Range
Set oAgeRng = oBM("Age").Range
Set oAddressRng = oBM("Address").Range
i = Me.ListBox1.ListIndex
oNameRng.Text = Me.ListBox1.Text
oBM.Add "Name", oNameRng
oAgeRng.Text = xlWS.Range("mySSRange").Cells(i + 2, 2)
oBM.Add "Age", oAgeRng
oAddressRng.Text = xlWS.Range("mySSRange").Cells(i + 2, 3)
oBM.Add "Address", oAddressRng
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
Me.Hide
End Sub
 
You can be a Word MVP without ever touching VBA, the award is given for
community support which could be all within the front-end of Word.

Ditto Excel.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Greg,

I didn't populate 3 listboxes, I populated 3 columns in one listbox. If you
just want name then just add that column. If you want to retrieve all 3
columns but not show them, use the code as I show, but set the columncount
of the listbox to 1.

Taking a quick look at your code, I don't see you populating the ListBox.

I presume you aren't there, so what is still needed?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Greg,

I just knocked up a working example


Private Sub CommandButton1_Click()
Dim oNameRng As Word.Range
Dim oAgeRng As Word.Range
Dim oAddressRng As Word.Range
Dim oBM As Bookmarks
Dim i As Long

Set oBM = ActiveDocument.Bookmarks
Set oNameRng = oBM("Name").Range
Set oAgeRng = oBM("Age").Range
Set oAddressRng = oBM("Address").Range
i = Me.ListBox1.ListIndex
oNameRng.Text = Me.ListBox1.Value
oBM.Add "Name", oNameRng
oAgeRng.Text = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
oBM.Add "Age", oAgeRng
oAddressRng.Text = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
oBM.Add "Address", oAddressRng
Me.Hide
End Sub

Private Sub UserForm_Initialize()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open( _
"E:\My Documents\Excel Files\WordLinkedSpreadsheet.xls")
Set xlWS = xlWB.Worksheets(1)
cRows = xlWS.Range("myDatabase").Rows.Count - _
xlWS.Range("myDatabase").Row

With Me.ListBox1
For i = 1 To cRows
.AddItem xlWS.Range("myDatabase").Cells(i, 1)
.List(.ListCount - 1, 1) = xlWS.Range("myDatabase").Cells(i, 2)
.List(.ListCount - 1, 2) = xlWS.Range("myDatabase").Cells(i, 3)
Next i
End With

Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob,

Sorry I only sent you the Command_Button code. I did populate the listbox
in the initialize code. I will look over your recent code closer as I am
sure it will work better than my method. Thanks again.
 
Bob,

Showing my lack of experience and knowledge here I am sure. I had to revise
your initialize code a bit or else I was getting the spreadsheet column
heading included in my listbox:

So I was seeing:

Name
Joe
Bill

Instead of:
Joe
Bill
Bob

Changed to this:

Private Sub UserForm_Initialize()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open( _
"E:\My Documents\Excel Files\WordLinkedSpreadsheet.xls")
Set xlWS = xlWB.Worksheets(1)
cRows = xlWS.Range("mySSRange").Rows.Count - _
xlWS.Range("mySSRange").Row + 1 ' Added the "+ 1"
************************
With Me.ListBox1
For i = 2 To cRows 'Changed your 1 to a 2
**********************************
.AddItem xlWS.Range("mySSRange").Cells(i, 1)
.List(.ListCount - 1, 1) = xlWS.Range("mySSRange").Cells(i, 2)
.List(.ListCount - 1, 2) = xlWS.Range("mySSRange").Cells(i, 3)
Next i
End With
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

Am I missing something in the setup of the spreadsheet that would normally
exclude the heading row?

Another question related to my first method.

If I use this line in the command button click macro:

oNameRng.Text = Me.ListBox1.Value

instead of

oNameRng.Text = 'Me.ListBox1.Text

Then when I select the first item in the Listbox I sometimes get an error
"Invalid use of null" It doesn't happen every time and in fact isn't
happening now so I can't give you the exact error code. It never happened
if I stepped through the code. I used .Text rather than .Value in my code
as a result of this oddity. Any possilbe explanation?

Thanks so much for taking the time to assist.
 
Bob,

It is Runtime Error 94: Invalid use of Null.

I feel like a dolt. The problem is self inflicted and has nothing to
do with the first item in the list. It is caused by not physically
selecting an item and occurs in both methods.

Sorry if I confused anyone.
 
If the range includes the header, then it will be picked up when you iterate
through the list to get the values. The solution, as you identified, is to
start at row 2, or else re-define the range name to exclude the header.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob,

Thanks for clearing that up.

Greg
Bob said:
If the range includes the header, then it will be picked up when you iterate
through the list to get the values. The solution, as you identified, is to
start at row 2, or else re-define the range name to exclude the header.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
Bob,

I concede that your direct method works, but am still stuck on
something trying to understand how the DAO method works.

If I have mydatabase range defined as:

Name Age Address
Bill 25 Ohio
Joe 30 New York
Mary 35 New Jersey


That is a range spanning A1:C4

and run this code:

Sub test()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\myBook1.xls", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mydatabase`")
While Not rs.EOF
MsgBox rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub


While Not rs.EOF process returns:

Bill
Joe
Mary

Unlike the direct method that you have shown me and even with my column
header "Name" within the defined range "mydatabase," the While Not
rs.EOF seems to skip the first row of the range.

I assume that skipping the first RS in the defined range must have
something to do with how this line is constructed:

Set rs = db.OpenRecordset("SELECT * FROM `mydatabase`")

I got this code from the Word MVP FAQ site.
http://word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm

There is no explanation of that line in the article and I can find no
explanation in the VBA help file either. Especially related to the
"*."

Can you enlighten?

Thanks
 
Can't help you there, don't use DAO. ADO, yes, DAO, no.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Bob,

Thanks just the same. This issue is buried pretty deep in this string
(or thread whatever they are called) and I think I will ask about it as
a new separate question.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Expanation of OpenRecordSet Statement Please 5
Changes to working code 5
Bring Data From MS Acess in a Combobox 1
User Form Problems 4
VBA Access export help 2
Bring Data From MS Acess in a Textbox 1
Setting a variable for the userform itself? 3
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0

Back
Top