creating a button that exports to excel

[tommy_mo]

Hello All-

I'm at the end of a long project that has one last step. I was hoping that someone here would be generous enough to help. I'm pretty much a novice at Access and am sure that my question can be easily answered. So here is the problem.

I have data that is loaded from a PDA to a form in Access. The data can then be viewed in a list box which is great. I also need to be able to save the data from the list box, using a button, as an excel spreadsheet. Is there anyone here that is willing to guide me to a solution?? Thanks in advance -Tom

 

[KenHigg]

One way is to use an outputTo macro or command...

 

[tommy_mo]

I was able to get this fas using someone elses building blocks. It works fairly well, but there is no way for the user to designate diffrent save to locations or file names. Is there a way I can add a textbox or filed on the form I'm using to name the file? Thanks to anyone who is willing to help. -Tom

Private Sub cmdExportToExcel_Click()
On Error GoTo Err_cmdExportToExcel_Click

    Dim strQueryName As String
    Dim strExcelDetail As String
    Dim strDTSaved As String
    
    
    strDTSaved = Format(Date, "ddMM") ' Date and Time string to add on the "save as name".
    strQueryName = "LightlogExportToExcel"
    
    strExcelDetail = "\\Odo\odo_shared\MOD\Raw\PDA Light Log\test\ Light Log" & " " & strDTSaved & ".xls" ' This excel file will be saved as C:\Temp\Output Current Date and Time .xls  
    
    DoCmd.OutputTo acQuery, strQueryName, "MicrosoftExcel(*.xls)", strExcelDetail, True, ""
     

Exit_cmdExportToExcel_Click:
    Exit Sub

Err_cmdExportToExcel_Click:
    MsgBox Err.Description
    Resume Exit_cmdExportToExcel_Click
    
End Sub