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

Export Access query to Excel using a command button on an Access form

P: 1
MY CODE IS NEAR THE BOTTOM
I want to export this Access query into Excel using a command button on an Access form in the following way I describe below.

Below you will find the simple query I am trying to export to Excel using a command in an Access Form.

RowID strFY AccountID CostElementWBS
1 2008 1 7
2 2008 1 7

I want to export the 1st record of this query to an excel workbook in the following way:

In Workheet "Sheet1," I want the data pertaining to field strFY to go to Cell "A1," and then I want the data pertaining to field AccountID to go to Cell "A2."

Then in Worksheet "Sheet2," I want the data pertaining to field CostElementWBS to go to Cell "B1."

FOR THE SECOND RECORD IN THIS TABLE:

I want to export the 2nd record of this table to a new excel workbook in the following way:

In Workheet "Sheet1," I want the data pertaining to field strFY to go to Cell "A1," and then I want the data pertaining to field AccountID to go to Cell "A2." Then in Worksheet "Sheet2," I want the data pertaining to field CostElementWBS to go to Cell "B1."

Here is my code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdauto_Click()
  5. On Error GoTo err_Handler
  6.  
  7.    MsgBox ExportRequest, vbInformation, "Finished"
  8.  
  9. exit_Here:
  10.    Exit Sub
  11. err_Handler:
  12.    MsgBox Err.Description, vbCritical, "Error"
  13.    Resume exit_Here
  14. End Sub
  15.  
  16.  
  17. Public Function ExportRequest() As String
  18.    On Error GoTo err_Handler
  19.  
  20.    ' Excel object variables
  21.    Dim appExcel As Excel.Application
  22.    Dim wbk As Excel.Workbook
  23.    Dim wks As Excel.Worksheet
  24.  
  25.    Dim sTemplate As String
  26.    Dim sTempFile As String
  27.    Dim sOutput As String
  28.  
  29.    Dim dbs As DAO.Database
  30.    Dim rst As DAO.Recordset
  31.    Dim sSQL As String
  32.    Dim lRecords As Long
  33.    Dim iRow As Integer
  34.    Dim iCol As Integer
  35.    Dim iFld As Integer
  36.  
  37.    Const cTabOne As Byte = 1
  38.    Const cTabTwo As Byte = 2
  39.    Const cStartRow As Byte = 3
  40.    Const cStartColumn As Byte = 1
  41.  
  42.    DoCmd.Hourglass True
  43.  
  44.    ' set to break on all errors
  45.    Application.SetOption "Error Trapping", 0
  46.  
  47.    ' start with a clean file built from the template file
  48.    sTemplate = CurrentProject.Path & "\Test 1.xls"
  49.  
  50.    ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
  51.    Set appExcel = New Excel.Application
  52.    appExcel.Visible = True
  53.    Set wbk = appExcel.Workbooks.Add(sTemplate)
  54.    Set wks = appExcel.Worksheets(cTabOne)
  55.  
  56.    sSQL = "select * from qry_12"
  57.    Set dbs = CurrentDb
  58.    Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
  59.    If Not rst.BOF Then rst.MoveFirst
  60.  
  61.    ' For this template, the data must be placed on the 4th row, third column.
  62.    ' (these values are set to constants for easy future modifications)
  63.    iCol = cStartColumn
  64.    iRow = cStartRow
  65.  
  66.  
  67.    Do Until rst.EOF
  68.       iFld = 0
  69.       lRecords = lRecords + 1
  70.       Me.Repaint
  71.  
  72.       For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
  73.          wks.Cells(iRow, iCol) = rst.Fields(iFld)
  74.  
  75.          If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
  76.             wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
  77.          End If
  78.  
  79.          wks.Cells(iRow, iCol).WrapText = False
  80.          iFld = iFld + 1
  81.       Next
  82.  
  83.       wks.Rows(iRow).EntireRow.AutoFit
  84.       iRow = iRow + 1
  85.       rst.MoveNext
  86.    Loop
  87.  
  88.    ExportRequest = "Total of " & lRecords & " rows processed."
  89.  
  90.    ' My users appreciate when I resize the columns to fit the data.
  91.     wks.Cells.Select
  92.     wks.Cells.EntireColumn.AutoFit
  93.  
  94.     ' Set the focus back at the first cell
  95.     wks.Range("A1").Select
  96.  
  97. exit_Here:
  98.    ' Cleanup all objects  (resume next on errors)
  99.    On Error Resume Next
  100.    Set wks = Nothing
  101.    Set wbk = Nothing
  102.    Set appExcel = Nothing
  103.    Set rst = Nothing
  104.    Set dbs = Nothing
  105.    DoCmd.Hourglass False
  106.    Exit Function
  107.  
  108. err_Handler:
  109.    ExportRequest = Err.Description
  110.    Resume exit_Here
  111.  
  112. End Function
  113.  
Also on that command button when I click on it, it executes a function that exports data from a query to an new excel file. That parts works fine but what I want is to click on the button again and hope that it executes the same function but gives the new excel workbook a different name instead of the same name as the first time I clicked on the button.


A step by step process would be much appreciated. I've researching this for a the past 3 days with no luck, so I thank you many times over for your assistance.

Thank you.
Sep 3 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
Change your file name to include a timestamp

Expand|Select|Wrap|Line Numbers
  1. ' start with a clean file built from the template file
  2. sTemplate = CurrentProject.Path & "\Test 1" & Now() & ".xls"
  3.  
Sep 5 '07 #2

Post your reply

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