[RESOLVED] Help with Recordset export to Excel

[kxcntry99]

Hi everyone,

I have been trying to export my recordset to excel and I keep getting a type mismatch error, but I am not sure why.

Here is my code...

Dim oApp As Object
Dim oWB As Object
 
Set oApp = CreateObject("Excel.application")
 
        oApp.Visible = True
        Set oWB = oApp.workbooks.Add
        oWB.sheets(1).Cells(1, 1).copyfromrecordset rsrpt

I have been searching for an answer on the forums here but havn't found one. Also it looks like a lot of the excel export code here keeps the spreadsheet closed and invisible after the export. This is not important to me since the user will likely work with the file some and save it on their own when they are done.

the error points to the .copyfromrecordset line and specificially says "runtime error 13 type mismatch" I get as far as an open instance of excel but no data inserted in it.

any help you can provide would be great!

Thanks

Oh BTW I am using an ADO connection to an access database and VB6

[si_the_geek]

How is rsrpt declared? (it should be an ADODB.Recordset)
What code do you use to put the data in to it?

..and which version of Excel are you using?


I'm not sure what you meant by "keeps the spreadsheet closed and invisible after the export.", but you will probably find the answer in the first half of my Excel tutorial (link below).

[kxcntry99]

Si,

I am using excel 2003 but it needs to work for XP too since a lot of my office uses that. Here is the code for the recordset and the export:

Private Sub ExprtExcel_Click()
Dim oApp As Object
Dim oWB As Object
 
Set oApp = CreateObject("Excel.application")
 
        oApp.Visible = True
        Set oWB = oApp.workbooks.Add
        oWB.sheets(1).Cells(1, 1).copyfromrecordset rsrpt
    
End Sub
 
Private Sub Form_Load()
SCARViewer.WindowState = vbMaximized
Dim rsrpt As ADODB.Recordset
 
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= H:\SCA\SCA2_3.mdb"
cn.Open
Set rsrpt = New ADODB.Recordset
rsrpt.CursorLocation = adUseClient
rsrpt.Open sqlrpt, cn, adOpenKeyset, adLockPessimistic, cmdtext
Set DataGrid1.DataSource = rsrpt
DataGrid1.Refresh
 
End Sub

let me know if you see anything funky

[si_the_geek]

The first thing I noticed was something very "funky" indeed, your recordset does not exist! Well, it does, but only in Form_Load.. it cannot be used in ExprtExcel_Click or any other subs.

The solution is to move the declaration of rsrpt to the declarations section of the form.

Please do yourself a massive favour, and put Option Explicit at the very top of your code (in the Declarations section), as then you will get an obvious error when you make this kind of mistake. Note that VB can put it in for you automatically for any new forms/modules/etc.. just go to "Tools" -> "Options", and tick "require variable declarations".



Other "funky" things I spotted:

You dont seem to be closing any of your objects when you are finished with them, which is a very bad thing indeed; it leads to memory wastage (some memory is "locked" and cannot be used until you reboot), and for database objects (recordset & connection) can lead to database corruption.


The code you have is valid for all versions of Excel from 97 onwards... but it is far from ready. You need to close/show the workbook when you are finished, and also close the objects etc. The different ways of doing this (depending on what you want to do) are shown in my tutorial.

[kxcntry99]

thanks SI,

after your first post I took a look at your tutorial and I noticed the same thing about not closing anything, I have fixed that and I will now try moving the DB connection to the correct place in general.

thanks for the help I will let you know how it turns out.

thanks

[kxcntry99]

Thanks si, once I put the rsrpt dim statement in the general section it works perfectly!!!