Exporting Queries to Excel Sheets, 1 Student per Workbook

[StudentTeacher]

Hi Programmers,
How can Access export queries for 1 student into an Excel workbook template, putting each query on a separate sheet, naming the workbook with query fields + the date?

Here's more info. I have a database that students log into; opens a mainform with 12 subforms in 12 tab controls. On the last form is a button to output daily journal-type data. This runs 12 queries and exports the data to 12 sheets in an Excel workbook:



Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report1", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report2", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report3", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report4", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report5", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report6", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report7", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report8", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "q_rpt9", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report10", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report11", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report12", "U:\Journal Report.xls", True)

Here are my problems:

I don't know how to create a separate workbook for each kid from the Excel template. I can't figure out how to do a where statement using the kids' "StudentID" field from the main form. Right now, all kids are exported into the same workbook, "Journal Report.xls." I'd like to have a separate journal workbook for each kid, and their Excel file to be named: "StudentLname, CounselorLname, "Journal" [today's date].xls."

Also, I've done some formatting of the XLS template, like conditional formatting (if cells = TRUE, then color red), freezing panes, and resizing columns. Is it possible for all of these things to remain in the workbook? Right now, all of this formatting becomes erased as a new XLS is generated.

Lastly, these workbooks need to be password encrypted with my password, such as "123456" because they have to dumped into a shared folder.

THANK YOU VERY MUCH
StudentTeacher

[weekend00]

'suppose studentID is a number field
'rename your q_rpt9 to report9
'reports are base on your report1 ~ report12
Private Sub command0_click()
Dim rs As DAO.Recordset
Dim qdef As QueryDef
Dim SQLtext As String
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("select studentID, first(lastname) as lname, first(firstname) as fname from studentTable group by studentID")
Do While Not rs.EOF
For i = 1 To 12
SQLtext = CurrentDb.QueryDefs("report" & i).SQL
SQLtext = Left(SQLtext, InStrRev(SQLtext, ";", -1) - 1)
Set qdef = CurrentDb.CreateQueryDef("_report" & i, SQLtext + " where studentID=" & rs!studentID)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "_report" & i, "U:\" + rs!lname + ", " + rs!fname + " Journal Report " + Format(Now, "yyyy-mm-dd") + ".xls", True
DoCmd.DeleteObject acQuery, "_report" & i
Next
rs.MoveNext
Loop
Set qdef = Nothing
Set rs = Nothing
End Sub

[StudentTeacher]

Hi Weekend00,

Thanks for the code. I actually renamed the queries as "Report1, Report2, ...Report12" in an effort to make this easier--However, the individual worksheet tabs should be named with their real names (instead of Report1, Report2, etc.), which are:

"q_rpt1PleasureOrPain"
"q_rpt2PowerOrControl"
"q_rpt3Attachment"
"q_rpt4SocialStanding"
"q_rpt5Justice"
"q_rpt6Freedom"
"q_rpt7DirectionOrFocus"
"q_rpt8Physical"
"q_rpt9Interest"
"q_rpt10SafetyOrSecurity"
"q_rpt11Goal"
"q_rpt12Topic"

It would be best if the more descriptive part of the query names became the names of the worksheet tabs.

Thanks again

[weekend00]

the sheetname is always exactly the query name which you export.

in this case, you need to change the querys' names which means the code must be updated.

you can store all the sheetnames in an array rptNames, and change the code to use rptNames(i) instead of "report" & i.

[ajetrumpet]

Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report1", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report2", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report3", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report4", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report5", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report6", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report7", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report8", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "q_rpt9", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report10", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report11", "U:\Journal Report.xls", True)
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Report12", "U:\Journal Report.xls", True)

That is very ineffecient, but it does work.

Weekend is right. An array would suit you much better, and so would an Excel application object, workbook object and some loop code for your array. Create a new book, do an import, close the book (and repeat 12 times).

[StudentTeacher]

Thanks Adam and Weekend,

Sorry for my inexperience--I've spent time searching for array info and examples and yikes, don't understand it --which is why I'm a student teacher and not a programmer. Any further help would be appreciated to close this thread. Thanks.

[weekend00]

Code using array:
Please keep your report1 ~ report12 unchanged.
If you want format in your spread sheet, please refer to what Aje said, I am not good at this kind of work.

Private Sub command0_click()
Dim rs As DAO.Recordset
Dim qdef As QueryDef
Dim SQLtext As String
Dim reportName As Variant
a = Array( _
"q_rpt1PleasureOrPain", _
"q_rpt2PowerOrControl", _
"q_rpt3Attachment", _
"q_rpt4SocialStanding", _
"q_rpt5Justice", _
"q_rpt6Freedom", _
"q_rpt7DirectionOrFocus", _
"q_rpt8Physical", _
"q_rpt9Interest", _
"q_rpt10SafetyOrSecurity", _
"q_rpt11Goal", _
"q_rpt12Topic")
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("select studentID, first(lastname) as lname, first(firstname) as fname from studentTable group by studentID")
Do While Not rs.EOF
For i = 1 To 12
SQLtext = CurrentDb.QueryDefs("report" & i).SQL
SQLtext = Left(SQLtext, InStrRev(SQLtext, ";", -1) - 1)
Set qdef = CurrentDb.CreateQueryDef(reportName(i), SQLtext + " where studentID=" & rs!studentID)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName(i), "U:\" + rs!lname + ", " + rs!fname + " Journal Report " + Format(Now, "yyyy-mm-dd") + ".xls", True
DoCmd.DeleteObject acQuery, reportName(i)
Next
rs.MoveNext
Loop
Set qdef = Nothing
Set rs = Nothing
End Sub

[ajetrumpet]

Thanks Adam and Weekend,

Sorry for my inexperience--I've spent time searching for array info and examples and yikes, don't understand it --which is why I'm a student teacher and not a programmer. Any further help would be appreciated to close this thread. Thanks.

weekend is a good programmer it looks like. I'm going to bow out here so multiple code samples don't cause confusion for you StudentTeacher. (too much code can confuse anyone if the price is right!)

[weekend00]

I was a programmer and now no more.

Languages I'd used(some may not be called a language) : Fortran, Pascal, Assembly, c (turbo C, c++, visual C++...), dbase, foxbase, foxpro, TSQL(sybase), powerbuilder, java, HTML, ASP, javascript, vbscript, VBA, and some other I forgot.

A little experience: visual stutio, .net

I heard about but don't really know: cobol, perl, ...

OS: dos 1.0~7.0, DRdos 6.0, Windows 2.0,3.x, windows 95,98,NT,2000,XP, unix/sco unix, solarix

Database: dbase, foxbase,foxpro, sql server, Access, sybase, oracle

Now, I am nothing.