H
hughess7
Hi all
I have a list of customers in a query called "qry RBUs". I have a continuous
form which filters on an option group for different activities (Audits and
VVs). I want to create individual spreadsheets for EACH RBU for EACH
Activity. So far I have started with the code below, firstly is it best to do
it this way? Or use recordsetclone?
Secondly, what is the best way to filter the recordset per RBU (only if any
data exists for that RBU) and create an excel spreadsheet from this, named by
Activity & RBU.
Thirdly, is it possible to apply conditional formatting to the rows in the
excel spreadsheet via automation from Access? One of the columns is Selected
(True/False) - I want these to be in different colours. Or as a minimum the
False ones to be highlighted in yellow.
Dim rsRBU As DAO.Recordset, rsPlanning As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Select Case Me!Audit_Status
Case 1
Set rsPlanning = db.OpenRecordset("qry Resource Planning VVs")
Case 2
Set rsPlanning = db.OpenRecordset("qry Resource Planning Audits")
Case Else
msg = "You must choose either Audit or VV to produce RBU
spreadsheets."
MsgBox msg, vbCritical, "Selection not valid"
End Select
Set rsRBU = db.OpenRecordset("[qry RBUs]")
Do Until rsRBU.EOF
Loop
I have a list of customers in a query called "qry RBUs". I have a continuous
form which filters on an option group for different activities (Audits and
VVs). I want to create individual spreadsheets for EACH RBU for EACH
Activity. So far I have started with the code below, firstly is it best to do
it this way? Or use recordsetclone?
Secondly, what is the best way to filter the recordset per RBU (only if any
data exists for that RBU) and create an excel spreadsheet from this, named by
Activity & RBU.
Thirdly, is it possible to apply conditional formatting to the rows in the
excel spreadsheet via automation from Access? One of the columns is Selected
(True/False) - I want these to be in different colours. Or as a minimum the
False ones to be highlighted in yellow.
Dim rsRBU As DAO.Recordset, rsPlanning As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Select Case Me!Audit_Status
Case 1
Set rsPlanning = db.OpenRecordset("qry Resource Planning VVs")
Case 2
Set rsPlanning = db.OpenRecordset("qry Resource Planning Audits")
Case Else
msg = "You must choose either Audit or VV to produce RBU
spreadsheets."
MsgBox msg, vbCritical, "Selection not valid"
End Select
Set rsRBU = db.OpenRecordset("[qry RBUs]")
Do Until rsRBU.EOF
Loop