S
Steven Cheng
i have always had problems with this method and i can't clearly see why. can
someone tell me the error in my ways here....
i want to copy a recordset to excel and got everything working up to the
copyfromrecordset command. i know that the the rs is not empty as i did a
test to ensure this and got a valid recordcount...what is wrong with this
statement:
Private Sub createglexport()
Dim db As DAO.Database
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim sqlstring As String
Dim starting As Date
Dim ending As Date
Dim xlwkb As Excel.Workbook
Dim xlwks As Excel.Worksheet
Dim xlrng1 As Excel.Range
Dim xlrng2 As Excel.Range
Dim xl As Excel.Application
Dim i As Integer, maxrecord As Integer, maxfields As Integer
starting = CDate(InputBox("Starting date (mm/dd/yyyy)"))
ending = CDate(InputBox("Ending date (mm/dd/yyyy)"))
sqlstring = "SELECT * from tblData;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstring)
rs.MoveLast
MaxRecords = rs.RecordCount
maxfields = rs.Fields.Count
Set xl = New Excel.Application
Set xlwkb = xl.Workbooks.Add
xl.Visible = True
Set xlwks = xlwkb.ActiveSheet
xlwks.Name = "Journal_Details"
xlwkb.Worksheets.Add
xlwkb.ActiveSheet.Name = "Journal_Headers"
Set rng1 = xlwks.Range("A1")
For i = 0 To rs.Fields.Count - 1
rng1.Offset(0, i).Value = rs.Fields(i).Name
Next
Set rng1 = xlwks.Range("A2")
xlwks.Range(Cells(1, 2), Cells(MaxRecords, maxfields)).CopyFromRecordset
rs
Set rs = Nothing
Set db = Nothing
Do While rng1.Value <> ""
Select Case rng1.Offset(0, 9).Value
Case "330000" Or "331000" Or "332000" Or "128003"
rng1.Offset(0, 3).Value = rng1.Offset(0, 9).Value
Case Else
End Select
Loop
End Sub
someone tell me the error in my ways here....
i want to copy a recordset to excel and got everything working up to the
copyfromrecordset command. i know that the the rs is not empty as i did a
test to ensure this and got a valid recordcount...what is wrong with this
statement:
Private Sub createglexport()
Dim db As DAO.Database
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim sqlstring As String
Dim starting As Date
Dim ending As Date
Dim xlwkb As Excel.Workbook
Dim xlwks As Excel.Worksheet
Dim xlrng1 As Excel.Range
Dim xlrng2 As Excel.Range
Dim xl As Excel.Application
Dim i As Integer, maxrecord As Integer, maxfields As Integer
starting = CDate(InputBox("Starting date (mm/dd/yyyy)"))
ending = CDate(InputBox("Ending date (mm/dd/yyyy)"))
sqlstring = "SELECT * from tblData;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstring)
rs.MoveLast
MaxRecords = rs.RecordCount
maxfields = rs.Fields.Count
Set xl = New Excel.Application
Set xlwkb = xl.Workbooks.Add
xl.Visible = True
Set xlwks = xlwkb.ActiveSheet
xlwks.Name = "Journal_Details"
xlwkb.Worksheets.Add
xlwkb.ActiveSheet.Name = "Journal_Headers"
Set rng1 = xlwks.Range("A1")
For i = 0 To rs.Fields.Count - 1
rng1.Offset(0, i).Value = rs.Fields(i).Name
Next
Set rng1 = xlwks.Range("A2")
xlwks.Range(Cells(1, 2), Cells(MaxRecords, maxfields)).CopyFromRecordset
rs
Set rs = Nothing
Set db = Nothing
Do While rng1.Value <> ""
Select Case rng1.Offset(0, 9).Value
Case "330000" Or "331000" Or "332000" Or "128003"
rng1.Offset(0, 3).Value = rng1.Offset(0, 9).Value
Case Else
End Select
Loop
End Sub