ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Need to process first row. (https://www.excelbanter.com/excel-programming/385988-need-process-first-row.html)

Greg Maxey[_3_]

Need to process first row.
 
How do I change the setup in my spreadsheet or change the code below
so that the first row of the spreadsheet is processed. If I run this
code now it starts out with row 2.

Thanks.

Sub ScratchMacro()
'Set Reference to "Microsoft DAO 3.51 (or 3.6) Object Library".
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Set db = OpenDatabase("C:\Book1", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
'Loop through each recordset.
While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
MsgBox rs.Fields(i).Value
Next i
rs.MoveNext
Wend
'Clean up.
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub


George Nicholson

Need to process first row.
 
1) Try adding a MoveFirst, just to be sure...:
If Not rs.EOF then rs.MoveFirst
Do While Not rs.EOF
'etc....

2) Consider adding a set of column headers. It may be that when importing an
"Excel Database" that DAO/ODBC assumes there will be Column headers/Field
names in the first row, and that data starts in the 2nd row. I tried to find
some indication of that in the documentation but haven't been able to, so
this is more of a guess than anything else, but it does make sense: a
recordset *has* to have field names and they have to come from somewhere,
even when importing from Excel. Where else *could* they come from except the
first row of the Excel range?

An easy test: if you change your message box to display..
rs.Fields(i).Name & " " & rs.Fields(i).Value
...I bet you'll see the "missing" values from your first row as field names,
followed by the value from a subsequent row (as you are now).
First message box: "Tom Bill"

HTH,



"Greg Maxey" wrote in message
ups.com...
George,

Thanks. I tried that and it isn't the the case. I just created a
brand new file with a 3 x 3 range A1:C3

A B C
1 Tom Don Sue
2 Bill Joe April
3 Jim Tim Mary

I defined mySSRange using NameDefine:

=Sheet1!$A$1:$C$3

When I run my code the first msgbox return is "Bill" not "Tom"

Somehow it seems that my system is treating row one as a heading or
something. I don't have much experience with Excel and I am stumped.

On Mar 23, 1:18 pm, "George Nicholson"
wrote:
mySSRange must be referring to a named range that starts with Row 2.

To the "address" of a Named range:

InsertNameDefine: Select mySSRange and change the "Refers-To" address
to
what you want.

HTH,

"Greg Maxey" wrote in message

ups.com...



How do I change the setup in my spreadsheet or change the code below
so that the first row of the spreadsheet is processed. If I run this
code now it starts out with row 2.


Thanks.


Sub ScratchMacro()
'Set Reference to "Microsoft DAO 3.51 (or 3.6) Object Library".
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
Set db = OpenDatabase("C:\Book1", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
'Loop through each recordset.
While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
MsgBox rs.Fields(i).Value
Next i
rs.MoveNext
Wend
'Clean up.
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub- Hide quoted text -


- Show quoted text -







All times are GMT +1. The time now is 11:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com