By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Exporting Access query to Excel

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Click()
  2. On Error GoTo Err_Report_Click
  3.  
  4. Dim db As DAO.Database
  5. Dim qdf As DAO.QueryDef
  6. Dim strSQL As String
  7. Set db = CurrentDb
  8. Set qdf = db.QueryDefs("CWW Billing Query")
  9.  
  10. If Me.All_cbox = True Then
  11.     strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
  12.              "FROM [CWW_Data],[CWW_Acct] " & _
  13.              "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
  14.              "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
  15.              "ORDER BY [CWW_Acct].GCWW_Acct;"
  16. ElseIf Me.Loc_cbox = True Then
  17.     strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
  18.              "FROM [CWW_Data],[CWW_Acct] " & _
  19.              "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
  20.              "AND [CWW_Acct].UC_Location = '" & Me.Loc_combo.Value & "'" & _
  21.              "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
  22.              "ORDER BY [CWW_Acct].GCWW_Acct;"
  23. ElseIf Me.SAP_cbox = True Then
  24.     strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
  25.              "FROM [CWW_Data],[CWW_Acct] " & _
  26.              "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
  27.              "AND [CWW_Acct].SAP_Account = '" & Me.SAP_Combo.Value & "'" & _
  28.              "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
  29.              "ORDER BY [CWW_Acct].GCWW_Acct;"
  30. ElseIf Me.Acct_cbox = True Then
  31.     strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
  32.              "FROM [CWW_Data],[CWW_Acct] " & _
  33.              "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
  34.              "AND [CWW_Acct].GCWW_Acct = '" & Me.Acct_combo.Value & "'" & _
  35.              "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
  36.              "ORDER BY [CWW_Acct].GCWW_Acct;"
  37. ElseIf Me.Premises_cbox = True Then
  38.     strSQL = "SELECT [CWW_Data].*,[CWW_Acct].Premises,[CWW_Acct].UC_Location,[CWW_Acct].SAP_Account,[CWW_Acct].Notes " & _
  39.              "FROM [CWW_Data],[CWW_Acct] " & _
  40.              "WHERE [CWW_Data].Billing_Month BETWEEN # " & Me.Bill_Start & " # AND # " & Me.Bill_End & " #" & _
  41.              "AND [CWW_Acct].Premises = '" & Me.Premises_combo.Value & "'" & _
  42.              "AND [CWW_Data].GCWW_Acct = [CWW_Acct].GCWW_Acct " & _
  43.              "ORDER BY [CWW_Acct].GCWW_Acct;"
  44.  
  45. End If
  46.  
  47. Debug.Print strSQL
  48. qdf.SQL = strSQL
  49.  
  50. DoCmd.TransferSpreadsheet acExport, 8, "CWW Billing Query", "c:\test\test.xls", True, "Report"
  51.  
  52. DoCmd.Close acForm, Me.Name
  53.  
  54. Exit Sub
  55.  
  56. Err_Report_Click:
  57.     MsgBox "No records found for the selected query.", vbExclamation, "Information"
  58. End Sub
Thanks
Kaushik
Jan 8 '08 #1
Share this Question
Share on Google+
2 Replies


jaxjagfan
Expert 100+
P: 254
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
Jan 8 '08 #2

P: 2
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
Jan 9 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.