Exporting Access query to Excel

[Comandur]

Hi,

I am trying to export an access query to excel. I have made use of transferspreadsheet command to achive this. However i have hardcoded the path and the filename in the VBA code. I am not sure as to how i can make the user enter his ownfile name and select the location where the file is to be written.

I am attaching below my code:

Private Sub Report_Click()
On Error GoTo Err_Report_Click
 
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("CWW Billing Query")
 
If Me.All_cbox = True Then
    strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
             "FROM [CWW_Data],[CWW_Acct] " & _
             "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
             "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
             "ORDER BY [CWW_Acct].GCWW_Acct;"
ElseIf Me.Loc_cbox = True Then
    strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
             "FROM [CWW_Data],[CWW_Acct] " & _
             "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
             "AND [CWW_Acct].UC_Location = '" & Me.Loc_combo.Value & "'" & _
             "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
             "ORDER BY [CWW_Acct].GCWW_Acct;"
ElseIf Me.SAP_cbox = True Then
    strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
             "FROM [CWW_Data],[CWW_Acct] " & _
             "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
             "AND [CWW_Acct].SAP_Account = '" & Me.SAP_Combo.Value & "'" & _
             "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
             "ORDER BY [CWW_Acct].GCWW_Acct;"
ElseIf Me.Acct_cbox = True Then
    strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
             "FROM [CWW_Data],[CWW_Acct] " & _
             "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
             "AND [CWW_Acct].GCWW_Acct = '" & Me.Acct_combo.Value & "'" & _
             "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
             "ORDER BY [CWW_Acct].GCWW_Acct;"
ElseIf Me.Premises_cbox = True Then
    strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
             "FROM [CWW_Data],[CWW_Acct] " & _
             "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
             "AND [CWW_Acct].Premises = '" & Me.Premises_combo.Value & "'" & _
             "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
             "ORDER BY [CWW_Acct].GCWW_Acct;"
 
End If
 
Debug.Print strSQL
qdf.SQL = strSQL
 
DoCmd.TransferSpreadsheet acExport, 8, "CWW Billing Query", "c:\test\test.xls", True, "Report"
 
DoCmd.Close acForm, Me.Name
 
Exit Sub
 
Err_Report_Click:
    MsgBox "No records found for the selected query.", vbExclamation, "Information"
End Sub

Thanks
Kaushik

[jaxjagfan]

I did a similar process several years ago (SAP environment as well). We created a billing folder on the network and pushed the files created to the network folder. We also dynamically named the files based on the Billing Month being processed.

Our network path was similar to:

\\MyServer\Acct\AP\Billing\2007\

Each file was named

Billing_01_2007.xls
Billing_02_2007.xls

(They had wanted the months (Jan, Feb) initially but then realized the numerics allowed for better sorting) If not now they will complain about this later.

Be consistent with locations and naming structures. Never know what an employee will name a file or where they will put it.

MyPath ="\\MyServer\Acct\AP\Billing\2007\"
Make sure to use UNC - not all users may have identical drive mappings.
MyFile = "Billing_ & Format([BillDate],"mm_yyyy") & ".xls"

TransferSpreadsheet .... MyPath & MyFile

[Comandur]

I did a similar process several years ago (SAP environment as well). We created a billing folder on the network and pushed the files created to the network folder. We also dynamically named the files based on the Billing Month being processed.

Our network path was similar to:

\\MyServer\Acct\AP\Billing\2007\

Each file was named

Billing_01_2007.xls
Billing_02_2007.xls

(They had wanted the months (Jan, Feb) initially but then realized the numerics allowed for better sorting) If not now they will complain about this later.

Be consistent with locations and naming structures. Never know what an employee will name a file or where they will put it.

MyPath ="\\MyServer\Acct\AP\Billing\2007\"
Make sure to use UNC - not all users may have identical drive mappings.
MyFile = "Billing_ & Format([BillDate],"mm_yyyy") & ".xls"

TransferSpreadsheet .... MyPath & MyFile

Thank you for the suggestion. However these reports will not be generated every billing month, rather they will be generated by the various at their need. What i would like to do is to pop up a save as dialog box and allow the user to select the location and enter the file name. It there a way to do this?

Thanks
Kaushik