Here's one that's been bugging me for about a week now...

I have a form that allows users to filter records, simple enough. But I want 
to give them the option to export the filtered records to Excel. I don't 
want to use the docmd.outputTo due to it won't filter the records, it puts 
all of the records in the file. I've looked around and found some code 
(actually that I'm already using), but the problem is that it outputs 
EVERYTHING on the form. My goal is to output only the fields that are on the 
form.

I found another bit of code (following) that does what I need it to do in 
the way of only exporting the data and field names, but I've inadvertantly 
deleted part of one line (the Set rs=currentDB) and commented it out.

Dim xlApp As Object
Dim xlBook As Object
Dim rs As DAO.Recordset
Dim sql As String
Dim i As Integer

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add

sql = Forms("frmProjectEDISearch").Form.RecordSource 'Your record source if 
not a subform
'Set rs = CurrentDb.

For i = 1 To rs.Fields.Count
xlBook.Sheets(1).Cells(1, i) = rs.Fields(i - 1).Name 'Write Field names to 
Excel
Next i
xlBook.Sheets(1).Cells(2, 1).CopyFromRecordset rs 'Import the recordset data 
through Excel

' You can add whatever other formatting you want by running Excel VBA 
throught the xlApp object

xlApp.Visible = True

Set xlApp = Nothing
Set xlBook = Nothing
Set rs = Nothing


I know that the rs statement is for the Record Source. But...I'd like to 
somehow have that statment pull in the filtered data. Most everything I've 
seen with the OpenRecordset command uses a table or query. The filter is on 
the form and changes dynamically based on what's entered.

Any help would be greatly appreciated.

Thanks.

Jeff 

Jeff, what I do is to create a query to use for the export, and then write 
the SQL property of the QueryDef before I use TransferSpreadsheet so that it 
outputs the desired records.

This kind of thing:

    Dim strWhere As String
    Dim strFile As String
    Const strcStub = "SELECT Table1.* FROM Table1 " & vbCrLf
    Const strcTail = "ORDER BY SomeField;"
    Const strcExportQuery = "Query1"    'Name of the query for exports.

    If Me.FilterOn Then
        strWhere = "WHERE " & Me.Filter & vbCrLf
    End If
    dbEngine(0)(0).QueryDefs(strcExportQuery).SQL = strcStub & strWhere & 
strcTail

    strFile = "C:\Data\MyExport.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
        strcExportQuery, strFile

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff Garrison" <JGarrison@bmcsolutions.com> wrote in message
news:uqZIYGJKIHA.1168@TK2MSFTNGP02.phx.gbl...
> Here's one that's been bugging me for about a week now...
>
> I have a form that allows users to filter records, simple enough. But I 
> want to give them the option to export the filtered records to Excel. I 
> don't want to use the docmd.outputTo due to it won't filter the records, 
> it puts all of the records in the file. I've looked around and found some 
> code (actually that I'm already using), but the problem is that it outputs 
> EVERYTHING on the form. My goal is to output only the fields that are on 
> the form.
>
> I found another bit of code (following) that does what I need it to do in 
> the way of only exporting the data and field names, but I've inadvertantly 
> deleted part of one line (the Set rs=currentDB) and commented it out.
>
> Dim xlApp As Object
> Dim xlBook As Object
> Dim rs As DAO.Recordset
> Dim sql As String
> Dim i As Integer
>
> Set xlApp = CreateObject("Excel.Application")
> Set xlBook = xlApp.Workbooks.Add
>
> sql = Forms("frmProjectEDISearch").Form.RecordSource 'Your record source 
> if not a subform
> 'Set rs = CurrentDb.
>
> For i = 1 To rs.Fields.Count
> xlBook.Sheets(1).Cells(1, i) = rs.Fields(i - 1).Name 'Write Field names to 
> Excel
> Next i
> xlBook.Sheets(1).Cells(2, 1).CopyFromRecordset rs 'Import the recordset 
> data through Excel
>
> ' You can add whatever other formatting you want by running Excel VBA 
> throught the xlApp object
>
> xlApp.Visible = True
>
> Set xlApp = Nothing
> Set xlBook = Nothing
> Set rs = Nothing
>
>
> I know that the rs statement is for the Record Source. But...I'd like to 
> somehow have that statment pull in the filtered data. Most everything I've 
> seen with the OpenRecordset command uses a table or query. The filter is 
> on the form and changes dynamically based on what's entered.
>
> Any help would be greatly appreciated.
>
> Thanks.
>
> Jeff 

Allen -

I've never used the QueryDefs before, so I'm not entirely sure how to
use them.

Here's the code I changed it to...

    Dim strWhere As String
    Dim strFile As String

    Const strcStub = "SELECT tblEDIOrderLinks.*,
tblEDIDispatchFile.DF_ORDER, tblEDIDispatchFile.DF_CUSTNUM,
tblProjectMain.prjProjectName, tblContacts.contactFullName AS TechName
" & vbCrLf
    Const strcTail = "ORDER BY tblEDIOrderLinks.Order_EDI_ID;"
    Const strcExportQuery = "EDI Filter Export"    'Name of the query
for exports.


    If Me.FilterOn Then
        strWhere = "WHERE " & Me.Filter & vbCrLf
    End If
    DBEngine(0)(0).QueryDefs(strcExportQuery).sql = strcStub &
strWhere & strcTail


    strFile = "C:\Data\MyExport.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
        strcExportQuery, strFile

When I place the code in the OnClick and execute, I get the following
error...

Run-tiime error '3265':

Item not found in this collection.

What am I missing?

Thanks

Jeff

On Nov 16, 8:03 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> Jeff, what I do is to create a query to use for the export, and then write
> the SQL property of the QueryDef before I use TransferSpreadsheet so that it
> outputs the desired records.
>
> This kind of thing:
>
>     Dim strWhere As String
>     Dim strFile As String
>     Const strcStub = "SELECT Table1.* FROM Table1 " & vbCrLf
>     Const strcTail = "ORDER BY SomeField;"
>     Const strcExportQuery = "Query1"    'Name of the query for exports.
>
>     If Me.FilterOn Then
>         strWhere = "WHERE " & Me.Filter & vbCrLf
>     End If
>     dbEngine(0)(0).QueryDefs(strcExportQuery).SQL = strcStub & strWhere &
> strcTail
>
>     strFile = "C:\Data\MyExport.xls"
>     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
>         strcExportQuery, strFile
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jeff Garrison" <JGarri...@bmcsolutions.com> wrote in message
>
> news:uqZIYGJKIHA.1168@TK2MSFTNGP02.phx.gbl...
>
>
>
> > Here's one that's been bugging me for about a week now...
>
> > I have a form that allows users to filter records, simple enough. But I
> > want to give them the option to export the filtered records to Excel. I
> > don't want to use the docmd.outputTo due to it won't filter the records,
> > it puts all of the records in the file. I've looked around and found some
> > code (actually that I'm already using), but the problem is that it outputs
> > EVERYTHING on the form. My goal is to output only the fields that are on
> > the form.
>
> > I found another bit of code (following) that does what I need it to do in
> > the way of only exporting the data and field names, but I've inadvertantly
> > deleted part of one line (the Set rs=currentDB) and commented it out.
>
> > Dim xlApp As Object
> > Dim xlBook As Object
> > Dim rs As DAO.Recordset
> > Dim sql As String
> > Dim i As Integer
>
> > Set xlApp = CreateObject("Excel.Application")
> > Set xlBook = xlApp.Workbooks.Add
>
> > sql = Forms("frmProjectEDISearch").Form.RecordSource 'Your record source
> > if not a subform
> > 'Set rs = CurrentDb.
>
> > For i = 1 To rs.Fields.Count
> > xlBook.Sheets(1).Cells(1, i) = rs.Fields(i - 1).Name 'Write Field names to
> > Excel
> > Next i
> > xlBook.Sheets(1).Cells(2, 1).CopyFromRecordset rs 'Import the recordset
> > data through Excel
>
> > ' You can add whatever other formatting you want by running Excel VBA
> > throught the xlApp object
>
> > xlApp.Visible = True
>
> > Set xlApp = Nothing
> > Set xlBook = Nothing
> > Set rs = Nothing
>
> > I know that the rs statement is for the Record Source. But...I'd like to
> > somehow have that statment pull in the filtered data. Most everything I've
> > seen with the OpenRecordset command uses a table or query. The filter is
> > on the form and changes dynamically based on what's entered.
>
> > Any help would be greatly appreciated.
>
> > Thanks.
>
> > Jeff- Hide quoted text -
>
> - Show quoted text -

That all looks okay.

You'll probably find it works if you try it again now.
Otherwise try:
    CurrentDb
in place of:
    dbEngine(0)(0)

CurrentDb is always up to date, whereas dbEngine(0)(0) doesn't know about 
the query you just created (which is why I say it will probably work if you 
close the database and try again.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jtgarrison@gmail.com> wrote in message
news:89e221fd-95ad-440c-b6f1-beef2661e0d1@41g2000hsh.googlegroups.com...
> Allen -
>
> I've never used the QueryDefs before, so I'm not entirely sure how to
> use them.
>
> Here's the code I changed it to...
>
>    Dim strWhere As String
>    Dim strFile As String
>
>    Const strcStub = "SELECT tblEDIOrderLinks.*,
> tblEDIDispatchFile.DF_ORDER, tblEDIDispatchFile.DF_CUSTNUM,
> tblProjectMain.prjProjectName, tblContacts.contactFullName AS TechName
> " & vbCrLf
>    Const strcTail = "ORDER BY tblEDIOrderLinks.Order_EDI_ID;"
>    Const strcExportQuery = "EDI Filter Export"    'Name of the query
> for exports.
>
>
>    If Me.FilterOn Then
>        strWhere = "WHERE " & Me.Filter & vbCrLf
>    End If
>    DBEngine(0)(0).QueryDefs(strcExportQuery).sql = strcStub &
> strWhere & strcTail
>
>
>    strFile = "C:\Data\MyExport.xls"
>    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
>        strcExportQuery, strFile
>
> When I place the code in the OnClick and execute, I get the following
> error...
>
> Run-tiime error '3265':
>
> Item not found in this collection.
>
> What am I missing?
>
> Thanks
>
> Jeff
>
> On Nov 16, 8:03 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>> Jeff, what I do is to create a query to use for the export, and then 
>> write
>> the SQL property of the QueryDef before I use TransferSpreadsheet so that 
>> it
>> outputs the desired records.
>>
>> This kind of thing:
>>
>>     Dim strWhere As String
>>     Dim strFile As String
>>     Const strcStub = "SELECT Table1.* FROM Table1 " & vbCrLf
>>     Const strcTail = "ORDER BY SomeField;"
>>     Const strcExportQuery = "Query1"    'Name of the query for exports.
>>
>>     If Me.FilterOn Then
>>         strWhere = "WHERE " & Me.Filter & vbCrLf
>>     End If
>>     dbEngine(0)(0).QueryDefs(strcExportQuery).SQL = strcStub & strWhere &
>> strcTail
>>
>>     strFile = "C:\Data\MyExport.xls"
>>     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
>>         strcExportQuery, strFile
>>
>> "Jeff Garrison" <JGarri...@bmcsolutions.com> wrote in message
>>
>> news:uqZIYGJKIHA.1168@TK2MSFTNGP02.phx.gbl...
>>
>>
>>
>> > Here's one that's been bugging me for about a week now...
>>
>> > I have a form that allows users to filter records, simple enough. But I
>> > want to give them the option to export the filtered records to Excel. I
>> > don't want to use the docmd.outputTo due to it won't filter the 
>> > records,
>> > it puts all of the records in the file. I've looked around and found 
>> > some
>> > code (actually that I'm already using), but the problem is that it 
>> > outputs
>> > EVERYTHING on the form. My goal is to output only the fields that are 
>> > on
>> > the form.
>>
>> > I found another bit of code (following) that does what I need it to do 
>> > in
>> > the way of only exporting the data and field names, but I've 
>> > inadvertantly
>> > deleted part of one line (the Set rs=currentDB) and commented it out.
>>
>> > Dim xlApp As Object
>> > Dim xlBook As Object
>> > Dim rs As DAO.Recordset
>> > Dim sql As String
>> > Dim i As Integer
>>
>> > Set xlApp = CreateObject("Excel.Application")
>> > Set xlBook = xlApp.Workbooks.Add
>>
>> > sql = Forms("frmProjectEDISearch").Form.RecordSource 'Your record 
>> > source
>> > if not a subform
>> > 'Set rs = CurrentDb.
>>
>> > For i = 1 To rs.Fields.Count
>> > xlBook.Sheets(1).Cells(1, i) = rs.Fields(i - 1).Name 'Write Field names 
>> > to
>> > Excel
>> > Next i
>> > xlBook.Sheets(1).Cells(2, 1).CopyFromRecordset rs 'Import the recordset
>> > data through Excel
>>
>> > ' You can add whatever other formatting you want by running Excel VBA
>> > throught the xlApp object
>>
>> > xlApp.Visible = True
>>
>> > Set xlApp = Nothing
>> > Set xlBook = Nothing
>> > Set rs = Nothing
>>
>> > I know that the rs statement is for the Record Source. But...I'd like 
>> > to
>> > somehow have that statment pull in the filtered data. Most everything 
>> > I've
>> > seen with the OpenRecordset command uses a table or query. The filter 
>> > is
>> > on the form and changes dynamically based on what's entered.
>>
>> > Any help would be greatly appreciated.
>>
>> > Thanks.
>>
>> > Jeff- Hide quoted text -
>>
>> - Show quoted text -
>