Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need to automate process | Excel Discussion (Misc queries) | |||
If/then Process? | Excel Discussion (Misc queries) | |||
How to count process running time ( process not finished) | Excel Programming | |||
How to count process running time ( process not finished) | Excel Programming | |||
step into process | Excel Programming |