i have a couple of books coming on programming access - but they have not arrived as of yet. i am a fluent excel vba programmer, but i am extending out into access. I am starting with a simple export to a code generated excel file.
My database consists of 1 table (tblErrLog) with 8 fields (numEntry, txtVZID, txtAcctNum, txtRegion, txtComments, EZInfo, txtDate, txtTime)
I have the users enter data into an unbound form and submit it thru a simple sql string. At a random time, i will need to export data into a new excel file. the user will specify a data range, datStart and datEnd. then i will need to know how to use vba to have access export all records where txtDate is within the specified date range.
If anyone could shed some code onto the subject, i would greatly appreciate it, thanks!
3 5512
You could make an append query that writes the user selected records to a temp table. To export those records to Excel, you would use "TransferSpread sheet". Both the append query and TransferSpreads heet can be add and ran from the same macro.
Hope this points you in the right direction,
Bender
I was actually looking for some sort of source code. I am rather confused on the export process. if anyone can provide any assistance, i would greatly appreciate it, thanks!
You could make an append query that writes the user selected records to a temp table. To export those records to Excel, you would use "TransferSpread sheet". Both the append query and TransferSpreads heet can be add and ran from the same macro.
Hope this points you in the right direction,
Bender
i have a couple of books coming on programming access - but they have not arrived as of yet. i am a fluent excel vba programmer, but i am extending out into access. I am starting with a simple export to a code generated excel file.
My database consists of 1 table (tblErrLog) with 8 fields (numEntry, txtVZID, txtAcctNum, txtRegion, txtComments, EZInfo, txtDate, txtTime)
I have the users enter data into an unbound form and submit it thru a simple sql string. At a random time, i will need to export data into a new excel file. the user will specify a data range, datStart and datEnd. then i will need to know how to use vba to have access export all records where txtDate is within the specified date range.
If anyone could shed some code onto the subject, i would greatly appreciate it, thanks!
VBA provides a specific Excel routine for copying a recordset direct to Excel from Access - CopyFromRecordS et. It does not itself copy the field names, but these are easily transferred from the query itself as shown below.
In the Access VBA code you need to create and initialise an Excel application object. Once that is done you can open a named workbook and transfer the data using the CopyFromRecordS et method.
The advantage of using this approach is that you have full programmed control of what you do with the data thereafter - saving the worksheet, formatting cells, inserting worksheets, copying existing sheets, and so on.
The following skeleton extracts show the general principles. I have not shown all Dims or definitions for the variables. - Dim ObjExcel as Excel.Application
-
Dim TheQuery as DAO.Recordset
-
-
Set objExcel as New Excel.Application
-
-
objExcel.Workbooks.Add ' if new workbook wanted, or
-
objExcel.Workbooks.Open (WorkBookPath) ' if existing workbook - WorkBookPath is name and path of workbook to open
-
-
Set TheQuery = CurrentDb.OpenRecordset(QueryName)
-
'QueryName is a string holding the name of the table or query to be copied.
-
-
TheQuery.MoveLast
-
R = TheQuery.RecordCount
-
' recordcount is not used in this extract, but is useful in other contexts.
-
' the recordcount is not accurate unless you have moved to the end of the
-
' recordset first - to allow Access to count the records properly.
-
' If you forget to move back to the first record the RecordSet will be at end of file
-
' which will terminate any loop relying on a While Not TheQuery.EOF condition
-
-
TheQuery.MoveFirst
-
N = TheQuery.Fields.Count
-
-
'Copy the field names to Excel
-
With objExcel.ActiveSheet
-
For I = 0 To N - 1
-
.Cells(StartRow, I + StartColumn) = TheQuery.Fields(I).Name
-
Next I
-
End With
-
-
'Copy the querydata in the row below the field names
-
-
objExcel.Cells(StartRowNo+1,StartColumnNo)._
-
CopyFromRecordset TheQuery
-
-
' StartRowNo and StartColumnNo are numbers - 1 for row 1, 2 for row 2 etc, 1 for column A, 2 for column B etc. Cells (1,1) refers to cell A1.
-
-
TheQuery.Close
-
-
objExcel.DisplayAlerts = False ' turn off user dialogue for file saving
-
objExcel.ActiveWorkbook.SaveAs FileName:=TheFileName ' specify the name of the file
-
objExcel.DisplayAlerts = True
The skeleton is taken from several different procedures and functions I use within an Excel automation class developed to handle copying of queries from Access to Excel. Normally, these are copied then autofilter applied, column widths fitted, and other processing done from within Access directly on the Excel object.
Hope this helps with ideas for what can be done.
Cheers
Stewart
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Gary Wright |
last post by:
I have an Access 2K database split into front and back. Quite often the
users want to do some data analysis that I have not created a report for so
they want to export some subset of the data into and Excel spreadsheet.
Since the data often comes from many different tables, I have decided to
create a temporary Access table, put all the data into it then use the
Docmd.TransferSpreadsheet command to output the table to a spreadsheet.
In...
|
by: Robin Cushman |
last post by:
Hi all,
I need some help -- I'm working with an A2K database, using DAO, and
am trying to read records into a Crystal Report and then export it to
a folder on our network as an Excel spreadsheet. I'm having trouble
with my code at the point at which it hits ".ReadRecords" -- the
module just runs and runs without generating anything. I've gotten
this code to correctly save .rpt files without any data, but not with
data, nor have I been...
|
by: bonehead |
last post by:
Greetings,
I'm using the DoCmd.TransferText method to export the results of a MS
Access query to a csv file. The csv will then be used to load an Oracle
table.
In other systems such as TOAD for Oracle, it's possible to force an
additional comma delimiter after the last column, if the column is empty
on a particular row. Oracle requires this additional comma on empty
rightmost columns, for importing purposes.
|
by: paul.chae |
last post by:
I have a table in Access with about 3000 records. There are ~60 unique
values in the ID field for the 3000 records. What I would like to do
is automatically generate multiple Excel worksheets within a single
workbook with these records. I would end up with around 500
worksheets, 1 for each unique ID value. I was thinking this could be
done if I have an exported flag column in the table, and I search for
the max (or min) on the ID...
|
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:
| |
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...
|
by: CoolFactor |
last post by:
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...
|
by: Pauline |
last post by:
Dear all,
I have an enormous database (Access 2003) containing sales information, and an Excel tool to enable end users to do planning and forecasting. Untill now I would create several queries, export the data to Excel (Excel 2003) and store the Data in the tool for the users to work with. I always have to export all information for a group of users as I never know on what part of the data they want to work. This makes the Excel files very...
|
by: StevoNZ |
last post by:
I've been using Access to export data to Excel all year... this data has been increasing in size as the project nears compleion and now I find that when I export a table (or query) to Excel using the OfficeLinks Tools option "Analyze it with MS Excel" I receive an error message complaining about row limitations. We're only talking about 16,000 - 18,000 rows.
I know that Excel can handle ~64,000 rows when just working within that application....
|
by: =?Utf-8?B?YzY3NjIyOA==?= |
last post by:
Hi all,
I have a question for you.
I have a .csv file which has many lines of data.
Each line has many data fields which are delimited by ",".
Now I need to extract part of data from this file but save it as an excel
file.
The data in this excel file will be imported into an Access database. The
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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();...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |