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

Export data to Excel from 4 tables based on Date criteria

P: 91
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
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,287
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

P: 91
Thxs ChipR for your suggestion. It does help a great deal.
Cheers.
Kashif
Aug 7 '09 #3

Post your reply

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