Export Access Query to Excel Spreadsheet 1

[Dutt]

Hello All,
I'm trying to export a Query from a Access 2000 database to a Excel spreadsheet. The user will enter two dates that he/she would like to the data between. EX. All data between 10-Aug-01 To 20-Aug-01. I'm not really sure how to go about doing this, I'm relatively new to VBA.

Here is what I have started

'open database
Dim db As DAO.Database
Dim qryRDT As QueryDef
Set db = CurrentDb

'set the dates the user entered into variables
txtSDate.SetFocus
SDate = txtSDate.Text
txtEDate.SetFocus
EDate = txtEDate.Text

' Generate report based on what option user has selected
DoCmd.OpenQuery "RDT", acViewDesign
'Include all these records in the report
Set qryRDT = db.QueryDefs("RDT&quot
qryRDT.SQL = "SELECT [Project ID Master].[Project ID]," _
& "[Project ID Master].[Project Health]," _
& "[Project ID Master].[Project/Service/Description]," _
& "[Project ID Master].[Client BC Owner]," _
& "[Project ID Master].[Project Start Date]," _
& "[Project ID Master].[Project Owner]," _
& "[Project ID Master].[Project Manager]," _
& "[Project ID Master].[Billed To Date]," _
& "FROM [Project ID Master]" _
& "WHERE (([Project ID Master].[Project Start Date]) AND (([Project ID Master].[Project Close Date]) Between " & Chr(35) & SDate & Chr(35) & " AND " & Chr(35) & EDate & Chr(35) & &quot)" _
& "ORDER BY [Project ID Master].[Project Start Date];"

DoCmd.OpenQuery "RDT", acViewPreview
DoCmd.RunMacro "OutputWeeklyReport"


MsgBox "Emport Complete", vbInformation, "Export Status"
' Close db
db.Close
Set db = Nothing

Any thoughts or advice would be great!

Thanks
Dutt

 

[Chance1234]

why are you creating the query each time? if the query is set up and parenthisis are tehre for your between date doobrie then all you need to do is export the query. and it will prompt for the criteria sue the docmd.outputto

 

[Dutt]

So if I only want to create the query in code and export it in code what would I have to do? Would the code look like this:

'open database
Dim db As DAO.Database
Dim qryRDT As QueryDef
Set db = CurrentDb

'set the dates the user entered into variables
txtSDate.SetFocus
SDate = txtSDate.Text
txtEDate.SetFocus
EDate = txtEDate.Text

' Generate report based on what option user has selected
DoCmd.OpenQuery "RDT", acViewDesign
'Include all these records in the report
Set qryRDT = db.QueryDefs("RDT&quot
qryRDT.SQL = "SELECT [Project ID Master].[xwave PS ID]," _
& "[Project ID Master].[Project Health]," _
& "[Project ID Master].[Project/Service/Description]," _
& "[Project ID Master].[Client BC Owner]," _
& "[Project ID Master].[Project Start Date]," _
& "[Project ID Master].[xwave Owner]," _
& "[Project ID Master].[Project Manager]," _
& "[Project ID Master].[Billed To Date]," _
& "FROM [Project ID Master]" _
& "WHERE (([Project ID Master].[Project Start Date]) AND (([Project ID Master].[Project Close Date]) Between " & Chr(35) & SDate & Chr(35) & " AND " & Chr(35) & EDate & Chr(35) & &quot)" _
& "ORDER BY [Project ID Master].[Project Start Date];"

DoCmd.OutputTo acOutputQuery, , acFormatXLS, , No


MsgBox "Export Complete", vbInformation, "Export Status"
' Close db
db.Close
Set db = Nothing

 

[Chance1234]

reference the escel libary in references and you shoudl eb able to adpat the below code below for your tables. if you want to see this working creating a table called mytable with three fields ID, Forename, surname and jsut put any old crap in there




Sub bob()
Dim xlApp As Object ' Declare variable to hold the reference.
Dim xlbk As Workbook
Dim xlsht As Worksheet


Dim mydb As Database
Dim myrec As Recordset

Dim x As Integer

Set xlApp = CreateObject("Excel.Application&quot
Set xlbk = xlApp.Workbooks.Add
Set xlsht = xlbk.Worksheets(1)

Set mydb = CurrentDb()
Set myrec = mydb.OpenRecordset("SELECT * FROM mytable&quot

x = 1

Do Until myrec.EOF = True

xlsht.Cells(x, 1) = myrec![ID]
xlsht.Cells(x, 2) = myrec![ForeName]
xlsht.cells(x,3) = myrec![Surname]

x = x + 1




myrec.MoveNext


Loop




xlApp.Visible = True






End Sub

 

[Dutt]

Thanks the code works great, I had to modify it a bit but the push help a lot! The only thing is I need is a IF statment for the Dates. Is there a BETWEEN statement in VBA?, I know SQL has one, but which one should I use:

txtSDate.SetFocus
SDate = txtSDate.Text
txtEDate.SetFocus
EDate = txtEDate.Text

Set rstMaster = dbs.OpenRecordset("SELECT * FROM [Project ID Master]" _
& "WHERE (([Project ID Master].[Project Start Date]) AND (([Project ID Master].[Project Close Date]) Between " & Chr(35) & SDate & Chr(35) & " AND " & Chr(35) & EDate & Chr(35) & &quot),dbOpenDynaset)


I need all data with the dates between SDate and EDate.

 

[Chance1234]

change the code to this add in

Dim MySQL as String

Mysql = "("SELECT * FROM [Project ID Master] "
mysql = mysql & " WHERE (([Project ID Master].[Project Start Date]) "
mysql = mysql & " AND (([Project ID Master].[Project Close Date]) "
myssql = mydql & "Between #" & SDate &"# AND #" & & EDate & "#); "

and change myrec to

set myrec = mydb.openrecordset(mysql)

 

[Dutt]

Thank you very much for the help!! Everything worked out great!

Dutt

 

[Dutt]

For some reason when I execute the code and the Date is
20 Aug 00 and 30 Aug 00 its goes into the loop but if its 20 Aug 01 and 30 Aug 01 its doesn't. Can you see anything wrong with my code, I've been looking and can't spot anything. Can you see anything?

Dim xlApp As Object ' Declare variable to hold the reference.
Dim xlbk As Workbook 'Declare Excel Workbook and worksheet
Dim xlsht As Worksheet
Dim mydb As DAO.Database 'database
Dim myrec As DAO.Recordset
Dim x As Integer 'counter
Dim MySQL As String
Set xlApp = CreateObject("Excel.Application&quot 'Create and open Excel
Set xlbk = xlApp.Workbooks.Add
Set xlsht = xlbk.Worksheets(1)
Set mydb = CurrentDb() 'Open current database
'User will imput the start and end date of teh query they would like

txtSDate.SetFocus
SDate = txtSDate.Text 'Start Date
txtEDate.SetFocus
EDate = txtEDate.Text 'End/Close Date

'Set up the Column Headings
xlsht.cells(1, 1) = "Project Status"
xlsht.cells(1, 2) = "Project Type"
xlsht.cells(1, 3) = "Project ID"
xlsht.cells(1, 4) = "CSP Description"
xlsht.cells(1, 5) = "Business Owner"
xlsht.cells(1, 6) = "Project Creation Date"
xlsht.cells(1, 7) = "Manager"
xlsht.cells(1, 8) = "Project Manager"

'make counter = 2 -->1st line is teh headings
x = 2

'Set query in the variable MySQL
MySQL = ("SELECT * FROM [Project ID Master]&quot
MySQL = MySQL & " WHERE (([Project ID Master].[Project Start Date]) "
MySQL = MySQL & " AND (([Project ID Master].[Project Close Date]) "
MySQL = MySQL & "BETWEEN #" & SDate & "# AND #" & EDate & "#)); "

Set myrec = mydb.OpenRecordset(MySQL) 'Set the Recordset as MySQL

myrec.MoveFirst 'Move to the first record
Do Until myrec.EOF = True ' Output the data to the excel spreadsheet
xlsht.cells(x, 1) = myrec![Project Status]
xlsht.cells(x, 2) = myrec![Project Type]
xlsht.cells(x, 3) = myrec![xwave PS ID]
xlsht.cells(x, 4) = myrec![Project/Service/Description]
xlsht.cells(x, 5) = myrec![Client BC Owner]
xlsht.cells(x, 6) = myrec![Project Start Date]
xlsht.cells(x, 7) = myrec![xwave Owner]
xlsht.cells(x, 8) = myrec![Project Manager]
'Add one to counter
x = x + 1
myrec.MoveNext ',ove to teh next record
Loop
xlApp.Visible = True ' open the worksheet will the results
End Sub

 

[Dutt]

For some reason when I execute the code and the Date is
20 Aug 00 and 30 Aug 00 its goes into the loop but if its 20 Aug 01 and 30 Aug 01 its doesn't. Can you see anything wrong with my code, I've been looking and can't spot anything. Can you see anything?

Dim xlApp As Object ' Declare variable to hold the reference.
Dim xlbk As Workbook 'Declare Excel Workbook and worksheet
Dim xlsht As Worksheet
Dim mydb As DAO.Database 'database
Dim myrec As DAO.Recordset
Dim x As Integer 'counter
Dim MySQL As String
Set xlApp = CreateObject("Excel.Application&quot 'Create and open Excel
Set xlbk = xlApp.Workbooks.Add
Set xlsht = xlbk.Worksheets(1)
Set mydb = CurrentDb() 'Open current database
'User will imput the start and end date of teh query they would like

txtSDate.SetFocus
SDate = txtSDate.Text 'Start Date
txtEDate.SetFocus
EDate = txtEDate.Text 'End/Close Date

'Set up the Column Headings
xlsht.cells(1, 1) = "Project Status"
xlsht.cells(1, 2) = "Project Type"
xlsht.cells(1, 3) = "Project ID"
xlsht.cells(1, 4) = "CSP Description"
xlsht.cells(1, 5) = "Business Owner"
xlsht.cells(1, 6) = "Project Creation Date"
xlsht.cells(1, 7) = "Manager"
xlsht.cells(1, 8) = "Project Manager"

'make counter = 2 -->1st line is teh headings
x = 2

'Set query in the variable MySQL
MySQL = ("SELECT * FROM [Project ID Master]&quot
MySQL = MySQL & " WHERE (([Project ID Master].[Project Start Date]) "
MySQL = MySQL & " AND (([Project ID Master].[Project Close Date]) "
MySQL = MySQL & "BETWEEN #" & SDate & "# AND #" & EDate & "#)); "

Set myrec = mydb.OpenRecordset(MySQL) 'Set the Recordset as MySQL

myrec.MoveFirst 'Move to the first record
Do Until myrec.EOF = True ' Output the data to the excel spreadsheet
xlsht.cells(x, 1) = myrec![Project Status]
xlsht.cells(x, 2) = myrec![Project Type]
xlsht.cells(x, 3) = myrec![Project PS ID]
xlsht.cells(x, 4) = myrec![Project/Service/Description]
xlsht.cells(x, 5) = myrec![Client BC Owner]
xlsht.cells(x, 6) = myrec![Project Start Date]
xlsht.cells(x, 7) = myrec![Project Owner]
xlsht.cells(x, 8) = myrec![Project Manager]
'Add one to counter
x = x + 1
myrec.MoveNext 'move to the next record
Loop
xlApp.Visible = True ' open the worksheet will the results
End Sub

 

[Chance1234]

your using the AND operator, so for a record to come out in your SQL, the record needs to have a field ![Project Start Date] between those two dates AND the [Project Close Date] msut also be between those two dates. if you have a record where the start date is 4/8/01 and the end date is 9/9/01 that record wont come out, also if you haev a record where teh start date is say 31/7/01 and the end date is 4/8/01 that wont come through either. that is the only thing i can think off.

add a line in your code that says debug.print mysql

run your code, press control G and in the debug window youll have your full sQl statement, now goto qurys, new query ,design view SQL and copy that into there, then you shouls be able to work out what is going wrong,.

 

[Dutt]

Thanks a bunch! I didn't see it at all. Now that you point it out it makes perfect sense. I have only been programming for less then 6 months, thanks for taking the time to help!!!!!