Exporting Search Results to Excel

[gnarpeggio]

Hello,

Recently, I've been working on developing a comprehensive search form for my staff to use when searching for records on our database. I found an excellent VBA code example on the forum a few days back (Thanks again gromit!) and I'm trying to get the results of said search exported to an Excel spreadsheet when the user clicks the "Export" button.

I use a Private Function called "BuildFilter" that runs and evaluates all criteria entered when the "Search" button is clicked, but is there a way to call the search results into an OutputTo method?

The form I'm using displays the results in a subform based on a query with no criteria (as it is filled by the user) but currently the OutputTo method exports all records since the command is only calling the original blank query.

I'm hoping to call the BuildFilter's results, then transfer the data to Excel. Any ideas?

Thanks!

 

[boblarson]

How is your search returned? Is it a form or a query?

I have code on my website to export a table, query, or form to Excel which sometimes can be helpful if the standard methods don't provide what you need. You can find the functions here:
http://www.btabdevelopment.com/ts/default.aspx?PageId=10

 

[gnarpeggio]

The user enters the criteria desired on a form, and the results are displayed within the subform, which is tied to my blank query (query name is qrySearchFilter).

The search button's Click property has the following code:

' Code for the Search command button.

Private Sub btnSearch_Click()

' Updates the record source.
' The subform for this search is named "subfrmResults"
' The query used for this search is named "qrySearchFilter"

Me.subfrmResults.Form.RecordSource = "SELECT * FROM qrySearchFilter " & BuildFilter

' Requery the subform

Me.subfrmResults.Requery
End Sub

The "BuildFilter" is the function that evaluates the entered criteria. The subfrmResults displays the results in a subform.

 

[boblarson]

You can just export the subform using this function (copy the entire function into a new STANDARD module and then save the module with a name different than the function, and remember not to change any of the function except the formatting code near the bottom):
http://www.btabdevelopment.com/ts/default.aspx?PageId=47

And you would call it by using your subform control name (control on the main form which houses the subform).

Call Send2Excel(Me.subfrmResults.Form)

That is, if subfrmResults is the name of the subform control and not the subform name. If the subform control and subform name are the same then it is okay to use.

 

[gnarpeggio]

So I've added the module to my database and named it "Export". In the OnClick event for the proposed exporting command button, I've added the

Call Send2Excel (Me.subfrmResults.Form)

code. But now I'm getting a "User-defined type not defined" compile error in the module. This is occuring at

Dim rst As DAO.Recordset

My first question is, should I have started with replacing the "frm" ([frm As Form] portion) with my subform's control name as indicated in the comment? And second, when I call the module should I still be using Send2Excel and not Export (the new name of the module)?

I appreciate your help

 

[boblarson]

But now I'm getting a "User-defined type not defined" compile error in the module. This is occuring at

Dim rst As DAO.Recordset

So you probably missed this instruction on the web page:

MAKE SURE TO SET A REFERENCE TO DAO IF YOU DON'T HAVE ONE SET ALREADY.

My first question is, should I have started with replacing the "frm" ([frm As Form] portion) with my subform's control name as indicated in the comment?

NO, you just pass it to the function when you call it like I showed in the example I posted here.

And second, when I call the module should I still be using Send2Excel and not Export (the new name of the module)?

Yes, you use

Call Send2Excel(Me.subfrmResults.Form)

Just like it shows.

 

[gnarpeggio]

OK this clears it up a bit. But what exactly would I set as the reference? What am I referencing? Is it Excel itself or the table that the records are pulled from?

I'm a tad new to the DAO reference portion

 

[boblarson]

OK this clears it up a bit. But what exactly would I set as the reference? What am I referencing? Is it Excel itself or the table that the records are pulled from?

I'm a tad new to the DAO reference portion

Open up the VBA window.

Go to TOOLS > REFERENCES

Scroll down the list until you find the reference as noted below

For Access 2003 or earlier select:
Microsoft DAO 3.x Object Library
(where .x is either .51 or .6 - choose the higher one)

For Access 2007 select:
Microsoft Office 12 Database Engine Object Library

For Access 2010 select:
Microsoft Office 14 Database Engine Object Library

and check the check box beside it.

 

[gnarpeggio]

This is working great, but which portion of the function tells Excel to open the sheet with the "Wrap Text" command? Some items have large amounts of text thus causing some records to expand a great length. I'm trying to make them nice and even if possible.

Thanks again!

 

[boblarson]

This is working great, but which portion of the function tells Excel to open the sheet with the "Wrap Text" command?

Currently that doesn't exist. So, you would need to add this:

ApXL.Selection.WrapText = True

You would have to determine what you want to wrap but it should be done before the autosize:

    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
 
    ApXL.Selection.WrapText = True

    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

I put it where all of the cells would get set to wrap, but you can use your own selection if it is different from that.

 

[gnarpeggio]

Problem solved once again!

And my last and final question; where in the code would you change to make it display a save prompt once clicked instead of directly opening the file? I know in the OutputTo method you can leave the file name argument blank and a prompt will open initially. This will give my users a chance to name the spreadsheet to their own preference.

If it's too much alteration then I can just ignore it but this would greatly help.

 

[boblarson]

Problem solved once again!

And my last and final question; where in the code would you change to make it display a save prompt once clicked instead of directly opening the file? I know in the OutputTo method you can leave the file name argument blank and a prompt will open initially. This will give my users a chance to name the spreadsheet to their own preference.

If it's too much alteration then I can just ignore it but this would greatly help.

Before you call the function you would then use something like this:
http://www.mvps.org/access/api/api0001.htm

You can use the built in Office File Dialog but it doesn't work with the runtime (according to a Microsoft page).

So you would have your click event and then call the save dialog like:

Dim strFileName As String
 
strFileName = ahtCommonFileOpenSave

And then you would need to modify the function I gave you to include a parameter for the file. Put it before the Optional part

Public Function Send2Excel(frm As Form, [COLOR=red]strFileName As String, [/COLOR]Optional strSheetName As String)

And then you would need to add code to the function to save as that file name:

xlWBK.SaveAs strFileName

 

[gnarpeggio]

Sorry about the delay, this is what I have so far...

1. Copied the entire module's structure into my database and named it "Dialog".

2. Added the code to call the dialog in the OnClick event.
(strFileName = ahtCommonFileOpenSave)

3. Modified the function you provided to include the strFilter As String parameter.

4. Added the xlWBK.SaveAs strFileName code to the function you provided at

...

With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With

' selects all of the cells
ApXL.ActiveSheet.Cells.Select

ApXL.Selection.WrapText = False

' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select

xlWBk.SaveAs strFileName

rst.Close
Set rst = Nothing
Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function
End Function

5. Setup the OnClick for the Save/As dialog box as follows

Private Sub cmdExcel_Click()

Dim strFilter As String
Dim strInputFileName As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

Dim strFileName As String

strFileName = ahtCommonFileOpenSave

Call Send2Excel(Me.subfrmResults.Form)

End Sub

6. Compiled the code to check for errors. The first error was produced as "Argument Not Optional" for the Call Send2Excel portion in the OnClick event. I tried changing the parameter in your function to Optional strFileName As String and to my surprise, it worked.

7. Now the main problem: The button opens the dialog box in Save As with .XLS as the only file type (so far, so good). But when I save a file, in this case calling it "test", another Open dialog box opens immediately after closing the Save As box but the file type field is completely blank!

When I try to cancel the second box, the spreadsheet still opens with the selected data and a "1004" error is displayed in the database "The file could not be found. Try one of the following:"

Any ideas?

 

[boblarson]

7. Now the main problem: The button opens the dialog box in Save As with .XLS as the only file type (so far, so good). But when I save a file, in this case calling it "test", another Open dialog box opens immediately after closing the Save As box but the file type field is completely blank!

When I try to cancel the second box, the spreadsheet still opens with the selected data and a "1004" error is displayed in the database "The file could not be found. Try one of the following:"

Any ideas?

Yeah, you're calling the dialog twice (see red parts):

strSaveFileName = [COLOR=red][B]ahtCommonFileOpenSave[/B][/COLOR]( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

Dim strFileName As String

strFileName = [B][COLOR=red]ahtCommonFileOpenSave[/COLOR][/B]

You should be using this:

Private Sub cmdExcel_Click()

Dim strFilter As String
Dim strInputFileName As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)


Call Send2Excel(Me.subfrmResults.Form[COLOR=red][B], strSaveFileName[/B][/COLOR])

End Sub

 

[gnarpeggio]

Bob,

You are truly a genius! The code works perfect now. Now that you've pointed this out, I'll make sure to watch for duplicate calls in event procedures.

Thanks again!

 

[gnarpeggio]

Ok, back for one more question...

I tested the Save As box once it is clicked and all works well, but the export function is still called even if the user cancels the Save As box, thus opening the excel spreadsheet regardless. In addition, the 1004 error I previously mentioned pops up as well after the action is canceled. Is there any way to cancel the action in the OnClick event and get rid of the 1004 message?

Thanks again!

 

[boblarson]

Just before the function call, check to see if there is a file name:

If strSaveFileName <> vbNullString Then
   Call Send2Excel(Me.subfrmResults.Form[COLOR=black], strSaveFileName[/COLOR])
End If

 

[Felix314]

Bob, I'm not sure why this is, but whenever I try to do the same thing and I click on my main form button to send a subform's results to Excel using your "Send2Excel" module code, an error pulls ups saying:

"Compile error: Sub or Function not defined"

It then highlights the top line in my on click event code:

Private Sub cmdExport_Click()
Call Send2Excel(Me.subgraduates.Form)
End Sub

All I did was post an exact copy of your module code into a basic module and named it "MailMerge". I am using Access 2003 as an ADP file with SQL as a backend server. Any idea what is going on? I know this thread is very old..