473,770 Members | 5,880 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Export data to Excel from 4 tables based on Date criteria

91 New Member
Hi,

I want to export data from 4 Access tables to Excel using VBA. The user selects 2 different DATES from the form & based on DATEs criteria all record from 4 tables should be extracted to excel.
I can extract data from Table 1 to excel, which has the DATE field. But other 3 tables doesn't have this DATE field. I was wondering how can I get data in Excel from other 3 tables??
All 4 tables have 1 field in common "REFNO". This field is the primary key in Table 1.
Below is the code for data export to Excel, in my form.
Expand|Select|Wrap|Line Numbers
  1. Dim strExcelFile As String
  2. Dim strWorksheet As String
  3. Dim strDB As String
  4. Dim strTable As String
  5. Dim FDate As Date
  6. Dim TDate As Date
  7.  
  8. Dim objDB As Database
  9.  
  10. FDate = Me.txtFromDate
  11. TDate = Me.txtToDate
  12.  
  13. strExcelFile = "c:\Event.xls"
  14. strWorksheet = "WorkSheet1"
  15. strDB = "C:\04Aug09.mdb"
  16. strTable = "tblMaster"
  17. Set objDB = OpenDatabase(strDB)
  18. If Dir(strExcelFile) <> "" Then Kill strExcelFile
  19. objDB.Execute _
  20. "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
  21. "].[" & strWorksheet & "] FROM " & "[" & strTable & "] where [Event Date] between #" & FDate & "# AND #" & TDate & "#"
  22. objDB.Close
  23. Set objDB = Nothing
  24.  
Thanks.
Aug 7 '09 #1
2 4331
ChipR
1,287 Recognized Expert Top Contributor
For your other tables, try selecting records with:

strSQL = "SELECT * FROM myTable2 WHERE REFNO IN (SELECT REFNO FROM myTable1 WHERE [Event Date] BETWEEN etc.)

Here is some code that I use, if you wanted to specify writing to the excel sheet yourself.
Expand|Select|Wrap|Line Numbers
  1. Dim ExcelApp As Object
  2. Dim book As Object
  3. Dim sheet As Object
  4. Dim db As Object
  5. Dim records As Object
  6.  
  7.     'create excel spreadsheet
  8.     Set ExcelApp = CreateObject("Excel.Application")
  9.     'ExcelApp.Visible = True
  10.     Set book = ExcelApp.Workbooks.Add
  11.     Set sheet = ExcelApp.Sheets(1)
  12.  
  13.     'write data to the spreadsheet
  14.     sheet.Cells(1, 1) = "Pay Period Ending"
  15.     sheet.Cells(1, 2) = PayPeriodCombo.Column(1)    
  16.  
  17.     Set db = CurrentDb
  18.     strSQL = "SELECT * FROM " & strTable & "WHERE etc.
  19.     Set records = db.openrecordset(strSQL)
  20.  
  21.     i = 2
  22.     while not records.EOF
  23.         sheet.Cells(i, 1) = records!Field1
  24.         sheet.Cells(i, 2) = records!Field2
  25.         etc.
  26.         i = i + 1
  27.         records.MoveNext
  28.     wend
  29.  
  30.     'resize columns
  31.     sheet.Columns.AutoFit
  32.  
  33.     'save spreadsheet, turn off alerts for overwrite, etc
  34.     ExcelApp.DisplayAlerts = False
  35.     book.SaveAs strFileName
  36.     book.Close False
  37.     ExcelApp.DisplayAlerts = True
  38.     Set book = Nothing
  39.     ExcelApp.Quit

Hope that helps.
Aug 7 '09 #2
kashif73
91 New Member
Thxs ChipR for your suggestion. It does help a great deal.
Cheers.
Kashif
Aug 7 '09 #3

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

Similar topics

4
14792
by: Paolo | last post by:
Friends, I need help with some code to export different tables to a single spreadsheet in Excel. My excel file is named REPORT and the spreadsheet is named CLIENTS. I do have the code to export a single table to Excel but have problems with multimple tables. Thanks.
4
3131
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...
7
5146
by: Keon | last post by:
Hoi, I'm using a database with alot of records in 1 table (more than 3000). If i want to export this table to excel i only get it till record 2385. Do someone know how i can solve this probleme? To export my tables i use folowing code: StTotaal = stLocatieName & "\Archief\" & Year(Date) & Month(Date) & Day(Date) & "_Gegevens.xls" 'location and name where the table must be saved
2
3476
by: Regnab | last post by:
I've got my code working so that it'll count the number of columns in the table and move across (eg Range A-P and then range Q-W). Problem is when I get to the end of the single letters and get into AA etc. Have got an idea of how I could do it by using Chr() and having an ongoing counter which is divided by 26 when it goes past Z to work out location. However, I'd dearly like there to be an easier way. Any ideas? Another problem I'm...
4
33467
by: Hans [DiaGraphIT] | last post by:
Hi! I want to export a dataset to an excel file. I found following code on the net... ( http://www.codeproject.com/csharp/Export.asp ) Excel.ApplicationClass excel = new ApplicationClass(); excel.Application.Workbooks.Add(true); DataTable table = DATASETNAME.Tables;
1
9778
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
3119
by: tasmontique | last post by:
I am working on an access 2002 flight schedule database. I am new to access but have some basic understanding of sql and vb6 code. I have learned a lot from this website. Thanks much Hopefully you can help me with this one. This database handles a varying number of flights for a varying number of days for a varying number of months. I have a number of tables as follows. I tried to normalize to the best of my ability.
3
7162
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
2
6414
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the method.... it creates 6 sheets # region Namespaces using System;
0
10232
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
10059
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
9873
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
8891
agi2029
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...
0
6682
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
5313
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...
0
5454
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3578
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2822
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.