473,897 Members | 2,525 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

1 New Member
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 10525
MMcCarthy
14,534 Recognized Expert Moderator MVP
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
4211
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 direct access to the db server. I'd like to give her the facility to export the information in her local Access application to the shared PHP/MySql site. From one command button (or similar) in the Access application.
5
4098
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 data to export into Excel in a certain format, i.e it needs to begin importing at cell A4, and in truth it would be great
3
11942
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 pivot table view, to be exported as a pivot, not just a data list. Does this code exist? Is it transferspreadsheet but with a twist?
5
31933
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
9784
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 having I'd be most appreciative. The database is already constructed, I'm just wanting to export the data to an excel file. In short, I'm hoping to export two Tables (or queries...not sure which to use - they both seem to have the same data) in...
2
2061
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 explaining it correctly in order to receive the right answers. Here's the query - how do i add a command to export it to excel? sqltext = "SELECT i.AcYear, i.Eid, i.EidStaffCode, i.EvalteeID, e.Evalrecno, dbo.Clinic.clinicname, " & _ ...
16
5199
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 Storage_Click() On Error GoTo Err_Storage_Click
7
13195
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 only." I have a button on the form which opens the Excel worksheet, which then basically serves as the input form for the Access database. What I need is to somehow get the primary key (EstimateID) of the current record on the Access form...
7
4678
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 do is design a command button that will kick out the information that is stored in the subform to an excel document. Ideally I would like to use the DoCmd.OutputTo function so that the user could choose their output type, but right now I'll take...
0
9837
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11250
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10849
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10476
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7183
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5872
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4697
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4292
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3299
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.