Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,912
- Office Version
- 365
- Platform
- Windows
Hi
I have a recordset from a select * query to an access DB table. I want to be able to filter the recordset (using filter property). The users enter the filter expression in a textbox. After that they have a choice to take the data into a pivot table, or a csv file. The code runs without a hitch (provided valid expression is entered), but when I check the outputs it still returns the unfiltered recordset.
Thoughts?
I have a recordset from a select * query to an access DB table. I want to be able to filter the recordset (using filter property). The users enter the filter expression in a textbox. After that they have a choice to take the data into a pivot table, or a csv file. The code runs without a hitch (provided valid expression is entered), but when I check the outputs it still returns the unfiltered recordset.
Thoughts?
Code:
Private Sub cbtRun_Click()
Dim objRecordset As ADODB.RecordSet
Dim wkb As Workbook, wks As Worksheet
Dim pvc As PivotCache
Dim lngFile As Long, strFile As String
Dim vararrData As Variant, lngRow As Long, lngCol As Long
Set m_clsDB = New clsManageDB
With m_clsDB
.DBpath = Evaluate(ThisWorkbook.Names("str_const_DBPATH").RefersTo)
Set objRecordset = .RunScript(Me.SQL)
End With
If CBool(Len(Me.tbxExpression.Text)) Then
'MsgBox Me.tbxExpression.Text
objRecordset.Filter = Me.tbxExpression.Text
End If
With Me
If .Output = pvt Then
Set wkb = Workbooks.Add
'// delete all but 1 sheet
Set wks = wkb.Sheets(1)
Set pvc = wkb.PivotCaches.Add(xlExternal)
Set pvc.RecordSet = objRecordset
pvc.CreatePivotTable TableDestination:=wks.Range("A1")
ElseIf .Output = CSV Then
If Not CBool(Right(UCase$(.tbxFileName.Text), 3) = "CSV") Then
MsgBox "No csv file, file name required!", vbExclamation
Else
'consider .txt with pipe delim
lngFile = FreeFile
strFile = .tbxFileName.Text
Open strFile For Output As #lngFile
vararrData = Application.Transpose(objRecordset.GetRows)
For lngRow = LBound(vararrData, 1) To UBound(vararrData, 1)
For lngCol = LBound(vararrData, 2) To UBound(vararrData, 2)
Print #lngFile, "'" & vararrData(lngRow, lngCol) & "',";
Next lngCol
Print #lngFile,
Next lngRow
Close #lngFile
End If
End If
End With
Set m_clsDB = Nothing
Unload Me
End Sub
Last edited: