473,289 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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

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
1 10421
MMcCarthy
14,534 Expert Mod 8TB
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

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

Similar topics

11
by: Mike MacSween | last post by:
My client has an MS Access database application on her local machine. I have full access to that in terms of changing the design. I've got a simple PHP/MySql application on shared hosting, so no...
5
by: Jonny | last post by:
Hello, I have created a button on my form which when pressed does the following : 1) Run a pre-defined Macro, which is picking up a query and running my data to excel. However, I need the...
3
by: John | last post by:
Is there a way to code the button that's available in the query window--microsoft excel icon that exports to excel. I know transferspreadsheet will do this---but I want the query, which is in a...
5
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
2
by: teneesh | last post by:
I have the following query behind a button in Access. But I'd like for this button, after running the query to export this data to excel. When I look this up online, I find nothing, not sure if I'm...
16
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub...
7
by: kpresidente | last post by:
Hello all, I'm trying to transfer the value of a control on an Access form to an Excel worksheet using VBA. The Access form is a single form with all the controls disabled, so that data is "read...
7
by: TinyTom | last post by:
I really need help with this code. I have made a pretty complicated subform that automatically updates filter criteria based off of inputs in various fields within the search form. What I need to...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.