Need to process first row.

[Greg Maxey]

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]

mySSRange must be referring to a named range that starts with Row 2.

To the "address" of a Named range:

Insert>Name>Define: Select mySSRange and change the "Refers-To" address to
what you want.

HTH,

 

[Greg Maxey]

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 Name>Define:

=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.

 

[Tim Williams]

Maybe try switching to ADO: the Connection string has some options, one of which is to specify whether the source range has headers
or not...

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & _
";Extended Properties='Excel 8.0;HDR=No'"
oRS.Open sSQL, oConn

where sPath points to your Excel file and sSQL is your SQL.

 

[Greg Maxey]

Thanks Tim but I am in over my head here. I work with Word VBA a
little but am really deep water with Excel. I don't have any idea
what an SQL is.

 

[Tim Williams]

Greg,

The code below tested OK on my system.
Changing "HDR=No" to "HDR=Yes" in the connection string will cause the first
row to be treated as field names.

Tim


'**************************************
Sub ScratchMacro()
'Set Reference to "Microsoft ADO 2.6 (or similar) Object Library".

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset

Dim sPath As String
sPath = ThisWorkbook.Path & "\SQL_Source.xls"

Dim sSQL As String
sSQL = "select * from [RNG_DATA]"


oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & _
";Extended Properties='Excel 8.0;HDR=No'"

oRS.Open sSQL, oConn

If Not oRS.EOF Then
'Loop through each recordset.

MsgBox oRS.GetString(, , " --- ", vbCrLf) 'for debugging display of
(small) recordsets

' Do While Not rs.EOF
' For i = 0 To rs.Fields.Count - 1
' MsgBox rs.Fields(i).Value
' Next i
' rs.MoveNext
' Loop
Else
MsgBox "No records returned!"
End If

'Clean up.
oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
End Sub

 

[Greg Maxey]

Tim,

Yes I was able to get that to work. The HDR=No is the key and I was
also able to make that work with my original method as well by making
this change:

Set db = OpenDatabase("C:\Book1.xls", False, False, "Excel
8.0;HDR=NO")

Thanks for your help.

Greg,

The code below tested OK on my system.
Changing "HDR=No" to "HDR=Yes" in the connection string will cause the first
row to be treated as field names.

Tim

'**************************************
Sub ScratchMacro()
'Set Reference to "Microsoft ADO 2.6 (or similar) Object Library".

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset

Dim sPath As String
sPath = ThisWorkbook.Path & "\SQL_Source.xls"

Dim sSQL As String
sSQL = "select * from [RNG_DATA]"

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & _
";Extended Properties='Excel 8.0;HDR=No'"

oRS.Open sSQL, oConn

If Not oRS.EOF Then
'Loop through each recordset.

MsgBox oRS.GetString(, , " --- ", vbCrLf) 'for debugging display of
(small) recordsets

' Do While Not rs.EOF
' For i = 0 To rs.Fields.Count - 1
' MsgBox rs.Fields(i).Value
' Next i
' rs.MoveNext
' Loop
Else
MsgBox "No records returned!"
End If

'Clean up.
oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
End Sub

Thanks Tim but I am in over my head here. I work with Word VBA a
little but am really deep water with Excel. I don't have any idea
what an SQL is.

- Show quoted text -

 

[George Nicholson]

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,