Export Recordset to Excel

[charlienilmag]

I'm currently building an inventory program to keep track of hardware.
I've successfully built a search function using multiple parameters.
I'm able to see the results in the recordset as well. What I need to
know is how to export that searched (filtered I guess?) data into
Excel. I've found a couple of topics that guided me in the right
direction, but wasn't quite what I needed. Again, I'd just like to see
the results from the search. Any help would be greatly appreciated.
Thanks in advance.

The code I'm currently using is giving me the entire database table in
Excel. I just want the searched for data.

Private Sub Export_Click()

'Create a Recordset from all the records in the Orders table
Dim Inventory_Database As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Inventory_Database = "C:\Documents and Settings\cnilmag\My
Documents\Inventory\Inventory Program\Inventory_Database.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Inventory_Database & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Inventory_Database", , adCmdTable)

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit

'Close the connection
rs.Close
conn.Close
End Sub

 

[tina]

if you're just dumping the data into a new, blank workbook, it might be a
lot easier to just use the TransferSpreadsheet action in VBA. i don't know
how your "multiple-parameter search function" is set up in Access, but
essentially what you need to do is use those parameters in a SELECT query to
pull the dataset you want. in the TransferSpreadsheet action, just refer to
the query instead of a table.

hth

 

[charlienilmag]

Thanks for the input Tina. I tried what you suggested before but I
have the search function set up like an adhoc query, so I can't write a
standard sql query. I've included below what I've done to setup my
search button. I've left off a few text boxes for easier viewing. TIA

Private Sub cmdSearch_Click()

Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT [Inventory_Database].AssetID,
[Inventory_Database].Username, [Inventory_Database].Email,
[Inventory_Database].City, [Inventory_Database].Address,
[Inventory_Database].Region, [Inventory_Database].Make,
[Inventory_Database].Model, [Inventory_Database].CPU,
[Inventory_Database].RAM, [Inventory_Database].HDDTotal,
[Inventory_Database].HDDFree, [Inventory_Database].WindowsVersion FROM
[Inventory_Database]"


'Build the WHERE clause from the non-Null boxes.
If Not IsNull(Me.txtAsset) Then
strSql = strSql & "(AssetID = """ & Me.txtAsset & """) AND "
End If

If Not IsNull(Me.txtUsername) Then
strSql = strSql & "(Username = """ & Me.txtUsername & """) AND
"
End If

If Not IsNull(Me.txtEmail) Then
strSql = strSql & "(Email = """ & Me.txtEmail & """) AND "
End If

lngLen = Len(strSql) - 5
'Chop off the trailiing " AND ".
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

Form_Inventory_Database.RecordSource = strSql

 

[tina]

Thanks for the input Tina. I tried what you suggested before but I

have the search function set up like an adhoc query, so I can't write a
standard sql query.

true enough, but you've already done the hard part in constructing the SQL
statement, in order to set the form's RecordSource. just add the SQL
statement to the QueryDefs collection, output it, and then delete it, as

CurrentDb.CreateQueryDef "qryTemp", strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryTemp", "C:\Book1.xls"
DoCmd.DeleteObject acQuery, "qryTemp"

hth

Thanks for the input Tina. I tried what you suggested before but I
have the search function set up like an adhoc query, so I can't write a
standard sql query. I've included below what I've done to setup my
search button. I've left off a few text boxes for easier viewing. TIA

Private Sub cmdSearch_Click()

Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT [Inventory_Database].AssetID,
[Inventory_Database].Username, [Inventory_Database].Email,
[Inventory_Database].City, [Inventory_Database].Address,
[Inventory_Database].Region, [Inventory_Database].Make,
[Inventory_Database].Model, [Inventory_Database].CPU,
[Inventory_Database].RAM, [Inventory_Database].HDDTotal,
[Inventory_Database].HDDFree, [Inventory_Database].WindowsVersion FROM
[Inventory_Database]"


'Build the WHERE clause from the non-Null boxes.
If Not IsNull(Me.txtAsset) Then
strSql = strSql & "(AssetID = """ & Me.txtAsset & """) AND "
End If

If Not IsNull(Me.txtUsername) Then
strSql = strSql & "(Username = """ & Me.txtUsername & """) AND
"
End If

If Not IsNull(Me.txtEmail) Then
strSql = strSql & "(Email = """ & Me.txtEmail & """) AND "
End If

lngLen = Len(strSql) - 5
'Chop off the trailiing " AND ".
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

Form_Inventory_Database.RecordSource = strSql

 

[charlienilmag]

Thanks for your help Tina. I'm still not getting the correct results.
I'm a noob so forgive me if I'm a little hard-headed. I created the
statement like you suggested and I'm still receiving all data, instead
of just the search results. I mean obviously I would get all results
from looking at my sql statement. So how do I account for the search
function I created?

Dim strSQL As String

strSQL = "SELECT [Inventory_Database].Description
,[Inventory_Database].AssetID, [Inventory_Database].Username,
[Inventory_Database].Email, [Inventory_Database].City,
[Inventory_Database].Address, [Inventory_Database].Region,
[Inventory_Database].Make, [Inventory_Database].Model,
[Inventory_Database].CPU, [Inventory_Database].RAM,
[Inventory_Database].HDDTotal, [Inventory_Database].HDDFree,
[Inventory_Database].WindowsVersion FROM [Inventory_Database]"


CurrentDb.CreateQueryDef "qryTemp", strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryTemp", "C:\Book1.xls"
DoCmd.DeleteObject acQuery, "qryTemp"

 

[tina]

you've built a SQL statement to use as the RecordSource of the form. if you
want to *always* export that filtered dataset to Excel, then just put the
Export code in the same procedure so you can use the strSQL variable.

if the user may choose to export the dataset or not, once the form is
displaying the filtered data, then try changing the code slightly, to

CurrentDb.CreateQueryDef "qryTemp", Me.RecordSource

either way, you're using the SQL statement that you already created with the
user-picked criteria.

hth

 

[charlienilmag]

Thanks so much Tina. Brilliant! Don't know what I would've done
without you. I'd give you a hug if I could LOL.

 

[tina]

you're very welcome (even without the hug)!