Modules & VBA :: Export Query Results To Excel With Specific File Name?
Jul 8, 2014
I have a form with a list of names. When a specific name is selected from the list, a button is clicked and a query is run with the specific name as the criteria/filter.
I've written code to export these same query results to an Excel sheet, and I want the Excel file name saved with the name selected in the form.
Below is the code that I've written that doesn't work.
In VBA I have set a timer on a form to run a query and export to a specific folder as an excel file.
If I open the database as 'File Open' and open the form and let the timer run it exports perfectly.
As soon as I put the database into runtime - the Timer code kicks in and starts running but as soon as it hits the export line. It stops and then does nothing
So I press a button on my Form1 and my tbl_customers table is exported onto a specific sheet in a templated Excel file "customer-template" that I have created.
This file has formulas on another sheet that based on the imported data.
The file is then saved to a specific location C:AccessCustomersHistory with the file name based on a date that was criteria from my original form E.g. "customers 11-02-15"
Im trying to export a query to specific columns in excel and im using this code.
Code:
Dim objXL As Object Dim xlWB As Object Dim xlWS As Object
[Code].....
I tried it on other querys and it works but doesn't work on this specific one. I get an error on the "set rst = currentDb" line. And to my knowledge it doesn't work because I have a between two dates filter in it. So when I run it, I get an error saying I have too few parameters, expecting 2.
Also it seems to be exporting the lookup id's not the value when exporting(on the query that it did work on)
I have an access program that is stored on a SharePoint site. I want to be able to export report to the computer even if I just open the access program as read only. Here's my code:
Code:
Private Sub btnExport_Click() On Error GoTo btnExport_Click_Err Dim db As DAO.Database Dim strSQL As String Dim qdfTemp As DAO.QueryDef Dim qryFilter As DAO.QueryDef
I have a table having fields, Group, Subgroup, Emails.
I want to make a searchable form that asks user to select Group and select sub group. And click on some Export button to export the results in to excel file.
As per my data in table, There are some subgroups in group and many emails in subgroups.
i want to make an excel file from a query and then export it to mail. i want to make an excel and then send it as an attachhment. also i want to insert the mail address to manually because each time it is sent to a different person.
I have the following code to export a query into a excel file:
Code: Dim outputFileName As String outputFileName = "C:AccountSpreadsheet est.xls" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_Search_Invoices", outputFileName, True Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.Workbooks.Open "C:AccountSpreadsheet est.xls", True, False Set xlApp = Nothing
This works almost exactly how I want it to work.
The only thing wrong is that the columns are all the same width and they are all to narrow.
Is there a way to make the columns automatically become the width of the longest text within them (exactly what happens when you double click on the side of a squashed column in excel)?
I want to export a query into a specific worksheet in Excel. Have tried DoCmd.TransferSpreadsheet acExport, but it appears you can't specify an existing worksheet or cell range with a query. Some have said tables only. I want to assign this export task to a command button.
The variables are:
Query name = TrainingDataQ Excel workbook path & name = C:UsersmeDesktop2015AccessExportTest.xlsm Desired Excel worksheet = RawData
I think thats all you will need to know. The data in the query varies but would be no more than about 500 rows.
Also for the next time i want to run the export, some code to open that same excel file and delete the data in the RawData worksheet so that i can export new data from Access?
I have successfully put together some VBA code that will accept a user's input into a textbox on a form. A cmdFindprinter button is clicked and the query is ran. The user is asked via an inputbox for their login ID so that the query is exported to a text file on the desktop. The query then pops up in a new tab and also automatically exports the queried record to a text file on the desktop. I have upward of 30 users using this form at any given time and I need the following automated.
What I would like to do is to have the text file go into a specific format. For example:
IP address: Serial number: Location:
The name of one of the queries is "Xerox IP Query", the field names would be "IP Address", "SerialNumber", and "Site Name".
This is what comes up in the text file right now:
"CXF345946","157.229.243.58","123 Happy Ave"
I'd like to remove the quotes and have the info fall into place as shown in the example above.
This is the code so far:
Code: Private Sub cmdFindprinter_Click() On Error GoTo cmdFindprinter_Click_Err Dim strPath As String userNT = InputBox("Please enter your NT ID", "ServiceBase Xerox Printer Query", "Enter your NT ID") strPath = "C:Users" & userNT & "DesktopPrinterQuery.txt" If Heading = 0 Then Exit Sub
I need some simple code that will copy an Excel file or a table in Access to a specific location on an FTP server. I would think this would be a very simple task, but I have yet to find any sample code that is *simple*. I have seen lots of code that requires downloading this dll or that mda, but the examples don't work. There must be something built into MS Access 2010 that will allow a file to be uploaded to an FTP site.
All the variables are known:
The FTP location (it never changes) The FTP Username and Password (they never change) The destination folder on the FTP site (it never changes) The File type (it never changes) The File name (available from the form in Access from which this will be executed)
I can either produce an output file, then copy it to the FTP site, or I can export the table directly to the FTP site with the file name for that day.
This seems to be a very simple task with no simple solution. Currently I am using an FTP app to get the file to the FTP site, but I would like to automate this. The process that creates the output file is already automated, so I would just like to add this to the existing code as its own module.
just made all that above up and none of it is a real function/command in VBA, but is just the kind of thing I'm looking for.
I would think that since I can download and XML file from an FTP site that it should be child's play to upload a simple file to an FTP site, but I can't figure it out.
How to export a MS Access table or Query to and Excel file and it works great. How to make this code Export multiple table into one Excel file/
here is the Function: HTML Code: Sub ExportData_Sheet_Basic() On Error GoTo ExportData_Error 'DAO objects to get the data Dim db As DAO.Database Dim rs As DAO.Recordset
I have the form (PROJECT DETAILS) that displays a specific record's information, one record at a time.I would like to click a button to export 5 specific fields (name, phone, address) of that form ("Project Details"), based on the PK "AssignNr", and then insert those fields in an already created Excel form that has those fields blank, but other fields filled in.
And then, as part of the same macro, I want to have the Print pop-up come up in the Excel form to select where/how that file will get printed.I've tried exporting from the Project Details form, but it exports all the records. And then I am not sure how / where to code the vlookup formula (if that's even necessary) to take the data from the exported file, into the existing Excel form that needs to be filled in.
Can this even be done in one macro, or do I need to create two - one to export data from Access to Excel. And then another in Excel to vlookup the details from the exported file, into the existing Excel file.
It would be nice if I didn't have to put in what the vlookup criteria is. (i.e. AssignNr (which is the PK)) - if it could just be taken from the PROJECT DETAILS form's current record selection.
I am trying to export a query or table to a location that the user selects. So each time the export button is clicked the folder will change. But I want to be able to select the folder.
The code I have below is saving but not to the folder I want it to.
Private Sub CommandBtn_Click() Dim fileSelection As Object Dim strPath As String Set fileSelection = Application.FileDialog(4) With fileSelection .AllowMultiSelect = False If .Show = True Then
hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel. he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order. i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work. Dou you have any idea. thanks in advance
i export a union query to excel by the following code:
Code: Private Sub Befehl0_Click() 'bersicht aufrufen Dim xlApp As Object ' Excel.Application Dim xlBook As Object ' Excel.Workbook Dim xlSheet As Object ' Excel.Worksheet Dim rst As DAO.Recordset
[code]....
How can i delte the first row of the querry? without doing a new querry?
I am trying to export a table in access based on a unique field called Group_Name. Say my table has 100 records. 10 of those records belong to Group1, 10 belong to Group 2, and so on. What I want to do is export those groups individually to an excel file and have that file named somting like Group1_Premium Detail Report.
Here is the Code I have so far. I have a feeling I am close. When I run the code it does not seem to like strrsql2
Dim strsql As String Dim strsql2 As String Dim strfilename As String Dim strpath As String strpath = "C:UsersDesktopHome"
I was having a wee look around the Export Threads and came across the following VBA Code I thought would be handy to export a Query to a new Excel workbook, and although it did export to excel, it didn't filter the query to show only the things I was wanting. Now I've managed to have it not work at all!
Code: Private Sub cmdOK_Click() 'Step 1: Declare your variables Dim MyDatabase As DAO.Database Dim MyQueryDef As DAO.QueryDef Dim MyRecordset As DAO.Recordset Dim strSQL As String Dim i As Integer
have a query which I would like to export to excel 2010 and would like separate files saved using a unique field called [Brokerage]. The code below exports the query however does not export separate worksheets as I am missing something perhaps the OutputTo function.
Private Sub Commission_Excel_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim MyFileName As String Dim temp As String Dim mypath As String
I have 3 queries that provide the same printer information. Each one is queried by a different field: IP address, asset tag, and serial number. This may not mean anything in the long run, though I figured it is worth mentioning.
The users need to be able to quickly query a printer utilizing one of those criteria and then copy and paste it into our ticketing system. Is there a way to automatically export the record from the query to a text file? I have extensively searched online and have tried to come up with something but I have found that I don't know where to start. This is the code for the query:
Code: Dim intCount As Integer intCount = 0 If DCount("Location", "Phone numbers Query") > 0 Then intCount = intCount + 1 DoCmd.OpenQuery "", acViewNormal, acReadOnly
I am a relative newbie to VBA, and not very familiar with loops, but I need to add a loop to my function that exports a query with criteria contained in a bound ComboBox on a form. I've gotten my code to work fine without the loop, but I would like to export one file for each item "Team_ID" contained in the ComboBox without the user having to manually select and re-run the function each time. Here is what my code currently looks like:
Code:
Option Compare Database Option Explicit Public Function CreateQCChartsforReports() As Boolean Dim qdf As DAO.QueryDef Dim strSQLStatic As String Dim BookName As String Dim BookName2 As String Dim intCounter As Integer Dim cboCode As ComboBox
Suppose there's a simple query which has two fields, "year" and "graduates", where "year" can be grouped by "2012", "2013", etc and "graduates" are individual names.
How can I export the list of graduates to multiple excel files, with the filename based on "year"?
I have tried to set the output file in macro as "c:desktop" & query.year & ".xls"
But it's not working and the output filename is exactly "&query.year&.xls "and the file contains all year and all names.
