Creating a Temporary Recordset for Output

[FRIDL125]

In my database, I have an existing query that I want to use with several different forms specifying date ranges.

Using VBA, is there any way to do the following:

1) Reference the query
2) Run an SQL statement on the query to obtain a subset of records that match my date criteria
3) Create a temporary recordset to export to Excel

This is the code selecting the subset of records...

strSQL = &quot;SELECT * from qryInstalled Where Prod >= &quot; & dtStart & &quot; And Prod <= &quot; & dtEnd & &quot;&quot;
Set rstTempFile = dbs.OpenRecordset(strSQL)

TIA,

Lisa

 

[BillPower]

This should get you started:


Dim strSQL As String, fldCount As Long, iCol As Long, dbs As Database, rstTempFile As Recordset
Dim xlApp As Object, xlWb As Object, xlWs As Object
Set dbs = CurrentDb
strSQL = &quot;SELECT * from qryInstalled Where Prod >= #&quot; & Me!dtStart & &quot;# And Prod <= #&quot; & Me!dtEnd & &quot;#&quot;

Set rstTempFile = dbs.OpenRecordset(strSQL)
Set xlApp = CreateObject(&quot;Excel.Application&quot
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets(&quot;Sheet1&quot
xlApp.Visible = True
xlApp.UserControl = True
fldCount = rstTempFile.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rstTempFile.Fields(iCol - 1).Name
Next
xlWs.Cells(2, 1).CopyFromRecordset rstTempFile
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit
rstTempFile.Close
Set rstTempFile = Nothing
Set dbs = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Take note of the #'s in the SQL, this identifies a date.

The code will copy you recordset into a new excel workbook and then autofit columns and rows. Let me know how you get on.

 

[FRIDL125]

This works like a charm! Thank you! One last question... I would like to automatically save this report to a specific folder so that someone else can automatically pick it up at a specified time.

How would I do this in conjunction with the code you provided?

The code I am using with a different report looks like:

RptDate = Format(Date, &quot;mmddyy&quot
strFileName = &quot;\\Change Control\Change Control &quot; & RptDate & &quot;.xls&quot;

I am using the above code with the DoCmd.OutputTo method

TIA...

Lisa