Search form - Runs a query - Export to Excel

[ryanmce92]

Hi All,



I have a form which allows the end user to search for a certain supplier using certain fields e.g Supplier Name, Country of Manufacture etc. This form has a button on it which runs a query and brings up the results of their search in the datasheet view.

Rather than have the user get the results in the datasheet view I was wondering if there was a way to have an export results to excel button?. My users are kind of illiterate when it comes to I.T so I am just trying to make things as simple as possible for them.

Any help would be great!!.

[ranman256]

the export to excel code is:

' send the query and the sheet name
'usage : Export2XL("qsMyQuery","MyData")

Public Sub Export2XL(ByVal pvQry, Optional pvCaption)
Dim vDir, vTime, vNam, vFile

If IsMissing(pvCaption) Then pvCaption = pvQry   

vDir = txtPath = Environ(“UserProfile”) & "My Documents"     'get the users mydoc path or fill in a network path to save the file
vNam = pvQry 

vFile = vDir & vNam & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, pvQry, vFile, True, pvCaption

End Sub

[ryanmce92]

How would I go about assigning this to a button though?.

[sdel_nevo]

Hi ryanmce92
this is the code I have that runs on the on Click event of a button that exports a query to excel

the button is called "ExportAssets"

the code is below

Private Sub ExportAssets_Click()
On Error GoTo ExportAssets_Click_Error
DoCmd.OutputTo acOutputQuery, "Asset Details Exported From Asset Database", acFormatXLS, DLookup("FilePath", "Settings", "ID = 1") & "\IT" & "\Reports" & "\Excel" & "\Exported Asset Details" & "\Exported Asset Details - " & Me.TodaysTimeAndDate & ".xls"
Application.FollowHyperlink (DLookup("FilePath", "Settings", "ID = 1")) & "\IT" & "\Reports" & "\Excel" & "\Exported Asset Details" & "\Exported Asset Details - " & Me.TodaysTimeAndDate & ".xls"
On Error GoTo 0
Exit Sub
ExportAssets_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportAssets_Click of VBA Document Form_Dashboard"
End Sub

this outputs the query as an excel file to the folder located on our network in this case a mapped drive mapped as P

p:\IT\Reports\Excel\Exported Asset Details

the file name is then called Exported Asset Details - **with todays time and date***

it then opens the excel sheet by using application.followhyperlink line of code

you could adapt this to suit your needs

hope this helps

Steve

[ryanmce92]

Hi Steve,

Thanks for all this info, I'm sure it will help, I managed to do it myself yesterday by creating a macro and using export with formatting and the message box tools. I should have closed this post but was too happy with myself for getting it correct.

Thanks everyone for your help!!.

[sdel_nevo]

Hi ryanmce92

no problem

take care