Creating complex spreadsheet from within Access

[Rivulent]

Hello everyone, long time reader first time poster. I have a question that I can not currently find on my own.

I don't want to go into an overexplanation, but I need to be able to create a workbook that I does roughly the following things:

1) I have a very specific template that the sheets must follow. For example, There is a consistent header and footer the output must have, with a maximum of 15 rows per (printed) sheet. There are other tweaks I must do, but I believe I can handle that on my own. But essentially I need to be able to set individual cell properties, including values (in some cases the values need to be from the query) and row/collumn sizes/borders.

2) Groups rows by one of the collumn's values, and create an individual worksheet for each of these rows. This means that all 15 of the entries with a FieldX value of "apples" will be in the "apples" worksheet, and the 7 entries with a FieldX value of "pickles" will be in the "pickles" worksheet.

3) The worksheets need to be alphanumeric order so when they go to print they will print in order.

In the end I want to be able to have the user click an "output/print" button they can press. Doing so will take a Query/Table's information, and output it into one excel spreadsheet which fills in everything and they are able to print. This will save about 2 hours of gruntwork each time we need to print out a set, not to mention time saved when things need to be changed.

If you all need more information let me know. I will check back for replies ASAP. Thanks!

[nico5038]

Hmm, will be a daunting task, as Access can't do this without VBA code.

Found this sample on the web:

Function fncPopulateExcel() As String
 
Private Const XLT_LOCATION As String = "W:\Reports\Database\Request.xlt"
 
On Error GoTo Populate_Err
 
    Dim db As Database
    Dim qdf As QueryDef
    Dim prm As Parameter
    Dim rs As Recordset
    Dim objXL As Object, objSheet As Object, objRange As Object
    Dim strSaveAs As String, strRecord As String
    Dim x As Integer, intRow As Integer
 
    DoCmd.Hourglass True
    Set db = CurrentDb()
 
    ' Open, and make visible the Excel Template (Request.xlt)
Set objXL = GetObject(XLT_LOCATION)
 
objXL.Application.Visible = True
objXL.Parent.Windows(1).Visible = True
 
    ' Open the recordset, and activate the sheet in the template
 Set qdf = db.QueryDefs("qryRequest")
 For Each prm In qdf.Parameters
   prm.Value = Eval(prm.Name)
   Next prm
 
   Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    Set objSheet = objXL.Worksheets("TNSS")
    objSheet.Activate
    rs.MoveFirst
 
    ' Insert the data from the recordset into the worksheet
 
objXL.ActiveSheet.Cells(1, 5).Value = rs![Type]
 
objXL.ActiveSheet.Cells(3, 2).Value = rs![ProjectTitle]
 
' Set the save string, and save the spreadsheet. The file is saved with the project title as its name. (rs![ProjectTitle])
 
        strSaveAs = "W:\Reports\TimingRequests\" & rs![ProjectTitle] & ".xls"
        objXL.SaveCopyAs strSaveAs
      PopulateExcel = strSaveAs
          rs.Close
 
    'Quit Excel
    objXL.Application.Visible = True
    objXL.Parent.Windows(1).Visible = True
    objXL.Application.DisplayAlerts = False
    objXL.Application.Quit
 
    Set objXL = Nothing
    Set objSheet = Nothing
    Set objRange = Nothing
    Set rs = Nothing
end Function
 

It gives the rough structure. You'll need to add an additional recordsource to loop through the sheets needed for the different fruits, but it's a start.

Let me know when and where you get stuck.

Nic;o)

[NeoPa]

The code Nico posted uses these techniques, so you won't necessarily need this, but jic I thought I'd post the link (Application Automation). It's fairly brief but does cover the fundamental concepts so may help you understand what's happening if you're new to it.

[Rivulent]

Thanks for the suggestion and sample. I figure I would have to work within VBA to do this, which I have no problem doing.

I will take a look at this when i get more free time.. probably this weekend or next. I will post an update when I get to the next stopping point.

Thanks again!