Expanation of OpenRecordSet Statement Please

[Greg]

I have a spreadsheet "C:\myWorkbook1.xls" with a named range
"mydatabase"

mydatabase range spans a1:C4 and contains the following data:

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

The Column headings "Name, Age and Address" are in row 1 (or A1:C1)

I found some code on the Word MVP FAQ website:

http://word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm

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

I would expect the While Not rs.EOF process to return:

Name
Bill
Joe
Mary

as it cycles through the rs first record to last. It actually returns:

Bill
Joe
Mary

It does not return "Name" which is in the 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`")

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

Can anyone enlighten me as to why the first row in my defined range is
skipped when I run the code above?

Thanks.

 

[Guest]

By default, the first row is treated as the header and is used to define
fields names.
You can specify that your database has no headers by using

"Excel 8.0;HDR=NO"

instead of

"Excel 8.0"

in the 4rth parameter of the opendatabase function.

 

[Greg Maxey]

Thank you very much.

 

[Greg]

PapaDos,

I made the changes that you suggested and confirmed the results.
Thanks again. That solution however presented another problem :-(

Using the same example database:

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

I want to run a macro to build a table in Word with the data:

Before having your tip, I used:

Sub BuildaTableinWordWithExcelData()
Dim myDB As Database
Dim myActiveRecord As Recordset
Dim i As Long
Dim j As Long
Dim dtable As Table, drow As Row
Set myDB = OpenDatabase("C:\myBook1.xls", False, False, "Excel 8.0")
Set myActiveRecord = myDB.OpenRecordset("mySSRange", dbOpenForwardOnly)
Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, _
NumRows:=1, numcolumns:=myActiveRecord.Fields.Count)
Set drow = dtable.Rows(1)
'Add the column header data.
For i = 1 To myActiveRecord.Fields.Count
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1).Name
Next i
Set drow = dtable.Rows.Add
'Populate the data
Do While Not myActiveRecord.EOF
For i = 1 To myActiveRecord.Fields.Count
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1)
Next i
Set drow = dtable.Rows.Add
myActiveRecord.MoveNext
Loop
drow.Delete
myActiveRecord.Close
myDB.Close
End Sub

Using your suggestion, I used:

Sub BuildaTableinWordWithExcelData()
Dim myDB As Database
Dim myActiveRecord As Recordset
Dim i As Long
Dim j As Long
Dim dtable As Table, drow As Row
Set myDB = OpenDatabase("C:\myBook1.xls", False, False, "Excel
8.0;HDR=NO")
Set myActiveRecord = myDB.OpenRecordset("mySSRange", dbOpenForwardOnly)
Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, _
NumRows:=1, numcolumns:=myActiveRecord.Fields.Count)
Set drow = dtable.Rows(1)
Do While Not myActiveRecord.EOF
For i = 1 To myActiveRecord.Fields.Count
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1)
Next i
Set drow = dtable.Rows.Add
myActiveRecord.MoveNext
Loop
drow.Delete
myActiveRecord.Close
myDB.Close
End Sub

When I first ran this code, I got a Runtime error: 94 "Invalid use of
Null" when the code tried to write the label "Age" in the first row. I
suspected that this had something to do with mixed numbers and text in
the column and so I formatted column 2 as "Text" vice the default
"General."

This seems to work, but I was wondering if there was something that I
could put in the code above that would work around this error
automatically to avoid having to specifically set a format?

Thanks.

 

[Guest]

Accessing Excel files through a database engine has some limitations.
This is one of them.

If you ONLY need to READ the data, adding ";IMEX=1" to the 4rth parameter
may help:

"Excel 8.0; HDR=NO; IMEX=1;"

This would convert mixed data types columns to text when possible.
But it only looks at a few lines (default is 8, I think) before deciding if
the column contains more than one data type, so this is not much better. And
do not ever try to update or append to the recordset data with this option...

 

[Greg Maxey]

PapaDos,

Thanks for the information. You seem to have a treasure chest of bits to
add to the 4th parameter with which to enlighten the masses. Where can a
list of these be found for reference?

Thanks again.