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

Export from access to excel

P: 3
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!
Feb 14 '08 #1
Share this Question
Share on Google+
3 Replies


MindBender77
100+
P: 234
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 "TransferSpreadsheet". Both the append query and TransferSpreadsheet can be add and ran from the same macro.

Hope this points you in the right direction,
Bender
Feb 14 '08 #2

P: 3
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 "TransferSpreadsheet". Both the append query and TransferSpreadsheet can be add and ran from the same macro.

Hope this points you in the right direction,
Bender
Feb 14 '08 #3

Expert Mod 2.5K+
P: 2,545
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 - CopyFromRecordSet. 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 CopyFromRecordSet 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.

Expand|Select|Wrap|Line Numbers
  1. Dim ObjExcel as Excel.Application
  2. Dim TheQuery as DAO.Recordset
  3.  
  4. Set objExcel as New Excel.Application
  5.  
  6. objExcel.Workbooks.Add ' if new workbook wanted, or
  7. objExcel.Workbooks.Open (WorkBookPath) ' if existing workbook - WorkBookPath is name and path of workbook to open
  8.  
  9. Set TheQuery = CurrentDb.OpenRecordset(QueryName)
  10. 'QueryName is a string holding the name of the table or query to be copied.
  11.  
  12. TheQuery.MoveLast
  13. R = TheQuery.RecordCount
  14. ' recordcount is not used in this extract, but is useful in other contexts. 
  15. ' the recordcount is not accurate unless you have moved to the end of the 
  16. ' recordset first - to allow Access to count the records properly.
  17. ' If you forget to move back to the first record the RecordSet will be at end of file
  18. ' which will terminate any loop relying on a While Not TheQuery.EOF condition
  19.  
  20. TheQuery.MoveFirst
  21. N = TheQuery.Fields.Count
  22.  
  23. 'Copy the field names to Excel
  24. With objExcel.ActiveSheet
  25.         For I = 0 To N - 1
  26.             .Cells(StartRow, I + StartColumn) = TheQuery.Fields(I).Name
  27.         Next I
  28. End With
  29.  
  30. 'Copy the querydata in the row below the field names
  31.  
  32. objExcel.Cells(StartRowNo+1,StartColumnNo)._
  33. CopyFromRecordset TheQuery 
  34.  
  35. ' 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.
  36.  
  37. TheQuery.Close
  38.  
  39. objExcel.DisplayAlerts = False ' turn off user dialogue for file saving
  40. objExcel.ActiveWorkbook.SaveAs FileName:=TheFileName ' specify the name of the file
  41. 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
Feb 14 '08 #4

Post your reply

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