Export Query to more excel worksheets 1

[fule12]

Hi all,

I have problem with exporting query to more worksheets.
I have code for export query to one worksheet of workbook but how to divide query to more worksheets if value in one fields of query is different ?

Hire is my code foe export :
---------------- start -----------------------
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim iRow As Integer


Set db = CurrentDb()
Set qdf = db.QueryDefs("Deck_Template&quot
' Enter Parametars value
qdf(0) = Me.cmbVesselName
Set rst = qdf.OpenRecordset(dbOpenDynaset)



'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\template\template_2003.xls&quot
Set objSht = objWkb.Worksheets("2003 Template&quot
objWkb.Activate
' --- the actual data starts at row 2
iRow = 2

rst.MoveFirst

Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!VesselCode
'Debug.Print rst!VesselCode
objSht.Cells(iRow, 2).Value = rst![Category name]
objSht.Cells(iRow, 3).Value = rst!OrderNum
objSht.Cells(iRow, 4).Value = rst!Year
objSht.Cells(iRow, 5).Value = rst!Code
objSht.Cells(iRow, 6).Value = rst![Date Created]
objSht.Cells(iRow, 7).Value = rst![Date RFQ Send]
objSht.Cells(iRow, 8).Value = rst![Order Date]
objSht.Cells(iRow, 9).Value = rst!Description
objSht.Cells(iRow, 10).Value = rst!Vendor
objSht.Cells(iRow, 11).Value = rst!OrderStatus
objSht.Cells(iRow, 12).Value = rst![Vessel Remarks]
objSht.Cells(iRow, 12).Locked = False
objSht.Cells(iRow, 13).Value = rst!Accruals
objSht.Cells(iRow, 14).Value = rst![Invoice Cost]
objSht.Cells(iRow, 15).Value = rst!Duty
iRow = iRow + 1
rst.MoveNext

Loop

objSht.Protect password:="1"

rst.Close
qdf.Close
---------------------- end code --------

So if value in colum :
objSht.Cells(iRow, 4).Value = rst!Year - change i need new worksheet to be created.

I found temp. solution by creating query for each year but
this is not good solution because users can create record with future year so this record will not be export to excel.

Thanks


Fule

 

[AskMan]

You can compare records ... I put in red my corrections

First, Order your query by 'Year'


Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim iRow As Integer
Dim oldYear as long

Set db = CurrentDb()
Set qdf = db.QueryDefs("Deck_Template&quot
' Enter Parametars value
qdf(0) = Me.cmbVesselName
Set rst = qdf.OpenRecordset(dbOpenDynaset)



'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\template\template_2003.xls&quot
Set objSht = objWkb.Worksheets("2003 Template&quot
objWkb.Activate
' --- the actual data starts at row 2

If not rst.EOF then
rst.MoveFirst
oldYear=rst!Year
iRow = 2
End if

Do While Not rst.EOF
if rst!Year <> oldYear then
objWkb.sheets.add after.=objSht
set objSht=objWkb.Activesheet
objSht.name = rst!year
iRow = 2
oldYear=rst!Year
end if

objSht.Cells(iRow, 1).Value = rst!VesselCode
'Debug.Print rst!VesselCode
objSht.Cells(iRow, 2).Value = rst![Category name]
objSht.Cells(iRow, 3).Value = rst!OrderNum
objSht.Cells(iRow, 4).Value = rst!Year
objSht.Cells(iRow, 5).Value = rst!Code
objSht.Cells(iRow, 6).Value = rst![Date Created]
objSht.Cells(iRow, 7).Value = rst![Date RFQ Send]
objSht.Cells(iRow, 8).Value = rst![Order Date]
objSht.Cells(iRow, 9).Value = rst!Description
objSht.Cells(iRow, 10).Value = rst!Vendor
objSht.Cells(iRow, 11).Value = rst!OrderStatus
objSht.Cells(iRow, 12).Value = rst![Vessel Remarks]
objSht.Cells(iRow, 12).Locked = False
objSht.Cells(iRow, 13).Value = rst!Accruals
objSht.Cells(iRow, 14).Value = rst![Invoice Cost]
objSht.Cells(iRow, 15).Value = rst!Duty
iRow = iRow + 1
rst.MoveNext

Loop

objSht.Protect password:=&quot;1&quot;

rst.Close
qdf.Close



Hope that helps

 

[fule12]

AskMan,

Thanks ,is working !!!!

Fule

 

[AskMan]

What a chance ... I didn't test it