Full Version: Export To Excel
UtterAccess Forums > Microsoft® Access > Access Forms
hawk7890
Good day,
I have a search form with a sub form. I was wondering how I would add a button that will take the results and export to a spreadsheet. I have disabled most of the default menues and this also disables export, so my only option is to have a button on the form.
Thank you
Marc
ipisors
When exporting, you'd want to export a query or recordset. Check out the help files in regard to:
CODE
DoCmd.OutputTo

r,
CODE
DoCmd.TransferSpreadsheet
hawk7890
Thank you, could you specify how the code should be entered?
I have a main form - frmSearchCriteriaMain
The results show in sub form with this as a record source: (which is what I want to export)
SELECT qrySearchCriteriaSub.CallID, qrySearchCriteriaSub.Category_ID, qrySearchCriteriaSub.Category, qrySearchCriteriaSub.CallType_ID, qrySearchCriteriaSub.CallType, qrySearchCriteriaSub.Description, qrySearchCriteriaSub.Resolution, qrySearchCriteriaSub.TicketNumber, qrySearchCriteriaSub.Status_ID, qrySearchCriteriaSub.Status, qrySearchCriteriaSub.qrydte, qrySearchCriteriaSub.EnteredTime,
Also is there an easy way to prompt the user to enter a filename? I can hard code a save location if having a browse window is complicated.
Thanks
Marc
ipisors
If the form's recordsource is simply that SQL with no query name, why not copy that SQL into a query, save the query, then you should be good with the DoCmd.OutputTo code (naming the query in the OutputTo method's arguments).
This would be a great opportunity to learn to tap VBA help - the DoCmd.OutputTo is pretty easy to learn, both from the Intellisense popups as well as simply searching VBA help for DoCmd.OutputTo. Let us know if you try some specific things and get stuck.
hawk7890
Thanks for the quick reply, But I am not too sure how to do what you are saying with saving a query. Here is the code behind my search button.
Dim sSql As String
Dim sCriteria As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
sCriteria = "WHERE 1=1 "
lblRecordCount.Caption = "0"
'This code is for a specific search where you will need to enter the exact string
'The source for this code can either be from a table or query

If Me![Category] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Category_ID = " & Me![Category]
End If

If Me![CallType] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.CallType_ID = " & Me![CallType]
End If
If Me![Status] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Status_ID = " & Me![Status]
End If

If Me![FullName] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Staff_ID = " & Me![FullName]
End If

If Me![TicketNumber] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.TicketNumber like ""*" & TicketNumber & "*"""
End If
If Me![cboEnteredDate] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.qrydte = " & Format(Me!cboEnteredDate, conJetDate)
End If
' MsgBox sCriteria
If Nz(DCount("*", "qrySearchCriteriaSub", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
sSql = "SELECT [CallID],[Category],[CallType],[Description],[Resolution],[TicketNumber],[qrydte
],[EnteredTime],[Status],[FullName] from qrySearchCriteriaSub " & sCriteria
' MsgBox sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
Me.frmSearchCriteriaSub.Visible = True
Me.frmSearchCriteriaSub.Form.OrderBy = "Category"
Me.frmSearchCriteriaSub.Form.OrderByOn = True
lblRecordCount.Caption = DCount("[CallID]", "qrySearchCriteriaSub")
lblRecordCount.Caption = DCount("[CallID]", "qrySearchCriteriaSub", Right(sCriteria, Len(sCriteria) - 14))
Category.SetFocus
Else
Me.frmSearchCriteriaSub.Visible = False
MsgBox "The search failed find any records" & vbCr & vbCr & _
"that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
Category.SetFocus

End If
ipisors
Ok, so it's not a fixed recordsource, then, it's dynamically built. In that case you can't do what I suggested I suppose.
But you can:
  1. create a saved query of anything else (even blank, if you can trick access into allowing you to save a blank query), let's say it's called qryBucket
  2. code CurrentDb.QueryDefs("qryBucket").SQL=Me.Recordsource
  3. code DoCmd.OutputTo..... (refer to the qryBucket query)

I always maintain at least one, and usually several, generic queries that are literally "nothing" - they exist for the purpose of being filled with SQL at run time and then output, opened, etc. For situations where I have dynamically changing SQL, but I need a hard-saved query object because you can't use some commands with anything but a real saved q
hawk7890
I get an error message: Invalid SQL Statement; Expected 'Delete','Insert','Procedure','Select',or 'update'
This is what I put as the output as I can't find much info on the other command
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryBucket", "c:\test.xls"
Thanks
Marc
ipisors
must be an invalid recordsource ... try adding a
debug.print me.recordsource
When go to the immediate window and see what the problem is ...
hawk7890
I have never used that kind of code (sure sounds useful though) Where do I add this?
ipisors
after wherever your code line is which finishes putting the .SQL into the querydefs("qryBucket") (I'm not sure where exactly you put that), then add:
ebug.Print strSQL
then when the error happens, go to VBA project, actually you can go ahead and reset (no need to keep running), and View > Immediate window. Check out the link in my signature about debugging ... learning to debug puts you in control over a large part of your troubleshooting and is a very 'empowering' thing , was to me at l
hawk7890
This doesn't seem to do anything, I have attached the database
When it opens, Select Search, Choose a category of ACR and press the search button. I'd like to export the result found to Excel.
Thanks
Marc
hawk7890
I've been working on this some more and have not been able to make any progress. Can anyone help?
Thanks
Marc
ipisors
Sorry I didn't see your previous post didn't get the email for some reason.
Ok, so when you press Debug, and then hold your mouse over Me.Recordsource, you see that it is a zero length string! So the error makes sense.
(refering to the main form, then its Subform control, then that subform control's Form object, then that Form object's recordsource - real hairy, I know!)
ry:
CODE
CurrentDb.QueryDefs("qryBucket").SQL = Form_frmSearchCriteriaMain.frmSearchCriteriaSub.Form.RecordSource
DoCmd.OutputTo acOutputQuery, "qryBucket", acFormatXLS, Environ("temp") & "\test.xls", True

Ohonestly am somewhat unsure why the transferSpreadsheet command was behaving as if the referenced SQL was an append query ?? even after i adjusted the Recordsource line, debugging showed the SQL statement to clearly be a Select, and yet the transfer spreadhseet line seemed to invoke some kind of append action. i didn't examine the db for long enough to figure out why, and truthfully, I Just about NEVER use Transfer spreadsheet and am not that much of an expert on it.
hawk7890
Thank you very much, I am glad it works. Is there a way I can change the titles, and perhaps not export the ID column?
Thanks
Marc
ipisors
Hello again, you might replace some of the select statement:
!--c1-->
CODE
dim strSQL as string
strSQL = Form_frmSearchCriteriaMain.frmSearchCriteriaSub.Form.RecordSource
strSQL=replace(strSQL,"[CallID],","")
CurrentDb.QueryDefs("qryBucket").SQL = strSQL

that should get rid of ID column.
hawk7890
Thanks for your quick reply. That did work to remove the Id column, what if I wanted to have qrydte export with the header Entered Date. How would I accomplish that? I'm not sure if I want to change the field name as my query qrySearchCriteria sub has this field qrydte: DateValue([EnteredDate]) and this would cause a circular reference.
Thanks
Marc
ipisors
I think you've gotten to the point where you might as well just write a new query for what you want. it seems little use to go to all this trouble to output a query that mimics the form's recordsource, when what you really want is something several degrees different from the form's already-simple recordsource SQL.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.