CopyFromRecordset to Excel not showing filtered data 
Author Message
 CopyFromRecordset to Excel not showing filtered data

Hi,

I'm having two problems

1)
I first Search by lastname using "filter" and set it to datagrid.  For
example I filter by string "Smith", this will show 7 records in the grid.
But when I go to export the 7 records to Excel it loads all the
records from the Stored procedure.  The stored procedure Selects all the
records from a SQL table.
The Export to Excel is below and filter procedure.  Any ideas?  Thanks for
any help.

2)
Also would like the field names on the top of the columns in Excel?

Thanks

**********************************************
  'Search by Last Name
    If txtSearchlast.Text <> "" Then
       Screen.MousePointer = vbHourglass ' Change mouse pointer to
hourglass.

        Set cmd = New ADODB.Command
        cmd.CommandText = "Record_Source"
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection = conn
        'recordset
        Set Rs = New ADODB.Recordset
        Set Rs.Source = cmd
        Rs.CursorType = adOpenStatic
        Rs.LockType = adLockOptimistic
        Rs.Open

        Dim Findlast As String
        bookmarkfindlast = Rs.Bookmark
        Findlast = txtSearchlast.Text
          If Findlast = "" Then
             MsgBox "Please enter a LastName", vbExclamation
             Rs.Filter = adFilterNone
             Rs.Bookmark = bookmarkfindlast
             Screen.MousePointer = vbDefault ' Return mouse pointer to
normal.
           Else
             Rs.Filter = "LastName Like '" & Findlast & "%'"
             Set DGridSearch.DataSource = Rs
             txtSearchlast.Text = ""
             Screen.MousePointer = vbDefault ' Return mouse pointer to
normal.

             lblRecord.Caption = "Record: " & _
             CStr(Rs.AbsolutePosition) & _
             " of " & Str(Rs.RecordCount)

               If Rs.EOF Then
                  Rs.Filter = ""
                  Rs.Bookmark = bookmarkfindlast
                  FrmSearch.Height = 5130
                    With Screen
                      Me.Move (.Width - Width) \ 2, (.Height - Height) \ 2
                    End With
                  MsgBox "No LastName in the database matches your
criteria", vbExclamation
                  txtSearchlast.Text = ""
                  txtSearchlast.SetFocus

                  mnuPrevall.Enabled = False
                  mnuPrintall.Enabled = False
                  mnucvs.Enabled = False
                  mnuExcel.Enabled = False

                  Screen.MousePointer = vbDefault ' Return mouse pointer to
normal.

                Else
                  FrmSearch.Height = 9105
                    With Screen
                      Me.Move (.Width - Width) \ 2, (.Height - Height) \ 2
                    End With

                    mnuPrevall.Enabled = True
                    mnuPrintall.Enabled = True
                    mnucvs.Enabled = True
                    mnuExcel.Enabled = True

                  Screen.MousePointer = vbDefault ' Return mouse pointer to
normal.
               End If
           End If
    End If
*******************************Export to Excel
  Dim oExcel As Excel.Application
   Dim oBook As Excel.Workbook
   Dim oSheet As Excel.Worksheet
   Dim ExcelRs As New ADODB.Recordset
   Screen.MousePointer = vbHourglass ' Change mouse pointer to hourglass.

   Set oExcel = New Excel.Application
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   Set ExcelRs.DataSource = Rs

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

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

    'Close the connection
   'Rs.Close
   'conn.Close
 Screen.MousePointer = vbDefault ' Return mouse pointer to normal



Sat, 29 Nov 2003 23:15:04 GMT  
 CopyFromRecordset to Excel not showing filtered data
When you pass an ADO recordset out of process, properties maintained by the
ADO client cusor engine such as current record, filter and sort are lost.

Consider the options in
http://support.microsoft.com/support/kb/articles/Q295/6/46.asp, some of
which give you column headings and will also give you the recordset as it
exists (filtered) in your VB app.



Sun, 30 Nov 2003 00:33:35 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. CopyFromRecordset Delivering Labels Not Values to Excel?

2. Control will not show results of filter

3. Excel CopyFromRecordset Automation error

4. Datacombo not showing data when not an administrator.

5. DAO and Excel CopyfromRecordset function

6. Report showing sample data not the passed data!

7. Data from A97 to Excel97 using CopyFromRecordSet

8. Could not show Excel figure in Word document

9. Default Printer not showing up in Excel

10. Formula field in sub-report not showing up in Excel export

11. SQLQuery in Crystal Report 4.6 : NOT FILTERING DATA (HELP)

12. Crystal: duplicate data in table need to filter with record selection not suppress field

 

 
Powered by phpBB® Forum Software