By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Creating complex spreadsheet from within Access

P: 2
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!
Feb 27 '07 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,072
Hmm, will be a daunting task, as Access can't do this without VBA code.

Found this sample on the web:
Expand|Select|Wrap|Line Numbers
  1. Function fncPopulateExcel() As String
  3. Private Const XLT_LOCATION As String = "W:\Reports\Database\Request.xlt"
  5. On Error GoTo Populate_Err
  7.     Dim db As Database
  8.     Dim qdf As QueryDef
  9.     Dim prm As Parameter
  10.     Dim rs As Recordset
  11.     Dim objXL As Object, objSheet As Object, objRange As Object
  12.     Dim strSaveAs As String, strRecord As String
  13.     Dim x As Integer, intRow As Integer
  15.     DoCmd.Hourglass True
  16.     Set db = CurrentDb()
  18.     ' Open, and make visible the Excel Template (Request.xlt)
  19. Set objXL = GetObject(XLT_LOCATION)
  21. objXL.Application.Visible = True
  22. objXL.Parent.Windows(1).Visible = True
  24.     ' Open the recordset, and activate the sheet in the template
  25.  Set qdf = db.QueryDefs("qryRequest")
  26.  For Each prm In qdf.Parameters
  27.    prm.Value = Eval(prm.Name)
  28.    Next prm
  30.    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
  31.     Set objSheet = objXL.Worksheets("TNSS")
  32.     objSheet.Activate
  33.     rs.MoveFirst
  35.     ' Insert the data from the recordset into the worksheet
  37. objXL.ActiveSheet.Cells(1, 5).Value = rs![Type]
  39. objXL.ActiveSheet.Cells(3, 2).Value = rs![ProjectTitle]
  41. ' Set the save string, and save the spreadsheet. The file is saved with the project title as its name. (rs![ProjectTitle])
  43.         strSaveAs = "W:\Reports\TimingRequests\" & rs![ProjectTitle] & ".xls"
  44.         objXL.SaveCopyAs strSaveAs
  45.       PopulateExcel = strSaveAs
  46.           rs.Close
  48.     'Quit Excel
  49.     objXL.Application.Visible = True
  50.     objXL.Parent.Windows(1).Visible = True
  51.     objXL.Application.DisplayAlerts = False
  52.     objXL.Application.Quit
  54.     Set objXL = Nothing
  55.     Set objSheet = Nothing
  56.     Set objRange = Nothing
  57.     Set rs = Nothing
  58. 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.

Feb 28 '07 #2

Expert Mod 15k+
P: 31,271
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.
Mar 1 '07 #3

P: 2
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!
Mar 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.