Office automation - Access data to Excel

  • Thread starter Thread starter SF
  • Start date Start date
S

SF

Hi,

I have a report in excel and I am trying to export some data from access to
a pre defined excel format report. Some part appear to be OK but I am stuck
with the following:

At the lower part of the excel form, I try to fill data from Access using
looping thru record set, I notice that only one record is filled. My
question is that how do I fill subsequest records based on the number of
exsiting one



Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rstD As DAO.Recordset
Dim Stg As String
Dim I As Long

Stg = "SELECT rptTravelDetails.TravelDetailID,
rptTravelDetails.ActualTravelDate, rptTravelDetails.TravelType,
rptTravelDetails.ActualTravelTime, rptTravelDetails.PntOfTravel,
rptTravelDetails.PerDium, rptTravelDetails.Hotel,
rptTravelDetails.Transport"
Stg = Stg & " FROM rptTravelDetails WHERE (((rptTravelDetails.TravelID) = "
& Me.TravelID
Stg = Stg & ")) ORDER BY rptTravelDetails.TravelDetailID;"


Set dbs = CurrentDb
Set rstD = dbs.OpenRecordset(Stg, dbOpenDynaset)
Debug.Print rstD.RecordCount

Set xlApp = CreateObject("Excel.Application")


xlApp.Visible = True
xlApp.Workbooks.Open "O:\LAAR\Database\Templates\AOT.XLS"
xlApp.Sheets(1).Cells(6, 5).Select
xlApp.ActiveCell.Value = Me.StaffID.Column(1)
xlApp.Sheets(1).Cells(7, 5).Value = Me.Purpose
xlApp.Sheets(1).Cells(10, 5).Value = Me.Destination
xlApp.Sheets(1).Cells(11, 5).Value = Me.NoOfDays

' Lower Part that do not fill the excel file properly

For I = 1 To rstD.RecordCount
xlApp.Sheets(1).Cells(30, 1 + I).Value = rstD.Fields(I)
Debug.Print rstD(I).Value
Next I
 
SF said:
Hi,

I have a report in excel and I am trying to export some data from access
to a pre defined excel format report. Some part appear to be OK but I am
stuck with the following:

At the lower part of the excel form, I try to fill data from Access using
looping thru record set, I notice that only one record is filled. My
question is that how do I fill subsequest records based on the number of
exsiting one



Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rstD As DAO.Recordset
Dim Stg As String
Dim I As Long

Stg = "SELECT rptTravelDetails.TravelDetailID,
rptTravelDetails.ActualTravelDate, rptTravelDetails.TravelType,
rptTravelDetails.ActualTravelTime, rptTravelDetails.PntOfTravel,
rptTravelDetails.PerDium, rptTravelDetails.Hotel,
rptTravelDetails.Transport"
Stg = Stg & " FROM rptTravelDetails WHERE (((rptTravelDetails.TravelID) =
" & Me.TravelID
Stg = Stg & ")) ORDER BY rptTravelDetails.TravelDetailID;"


Set dbs = CurrentDb
Set rstD = dbs.OpenRecordset(Stg, dbOpenDynaset)
Debug.Print rstD.RecordCount

Set xlApp = CreateObject("Excel.Application")


xlApp.Visible = True
xlApp.Workbooks.Open "O:\LAAR\Database\Templates\AOT.XLS"
xlApp.Sheets(1).Cells(6, 5).Select
xlApp.ActiveCell.Value = Me.StaffID.Column(1)
xlApp.Sheets(1).Cells(7, 5).Value = Me.Purpose
xlApp.Sheets(1).Cells(10, 5).Value = Me.Destination
xlApp.Sheets(1).Cells(11, 5).Value = Me.NoOfDays

' Lower Part that do not fill the excel file properly

For I = 1 To rstD.RecordCount
xlApp.Sheets(1).Cells(30, 1 + I).Value = rstD.Fields(I)
Debug.Print rstD(I).Value
Next I
Click to expand...

I think you're missing a rstD.MoveNext before Next I

Regards,
Keith.
www.keithwilby.com
 
If you choose to do it the way you are doing it, you need two loops. An
outer loop for the rows, and an inner loop for the cells; however, you can
use the CopyFromRecordset method to do it with one command:
xlApp.Sheets(1).Cells(30, 1)..CopyFromRecordset rstD
 
Back
Top