Microsoft KB Archive/172268: Difference between revisions
m (Text replacement - """ to """) |
m (Text replacement - "&" to "&") |
||
Line 144: | Line 144: | ||
' Can't create object | ' Can't create object | ||
If Err = 429 Then | If Err = 429 Then | ||
MsgBox Err & | MsgBox Err & ": " & Error | ||
Exit Sub | Exit Sub | ||
End If | End If |
Latest revision as of 12:30, 21 July 2020
Article ID: 172268
Article Last Modified on 7/14/2004
APPLIES TO
- Microsoft Visual Basic 5.0 Learning Edition
- Microsoft Visual Basic 6.0 Learning Edition
- Microsoft Visual Basic 5.0 Professional Edition
- Microsoft Visual Basic 6.0 Professional Edition
- Microsoft Visual Basic 5.0 Enterprise Edition
- Microsoft Visual Basic 6.0 Enterprise Edition
- Microsoft Visual Basic 4.0 Standard Edition
- Microsoft Visual Basic 4.0 Professional Edition
- Microsoft Visual Basic 4.0 Professional Edition
- Microsoft Visual Basic 4.0 16-bit Enterprise Edition
- Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
This article was previously published under Q172268
SYMPTOMS
When you try to pass a Recordset object to Microsoft Excel using Automation from Microsoft Visual Basic 4.0, it fails with one of the following errors:
-or-
This article describes how you can work around this limitation and successfully pass a Recordset to Microsoft Excel.
RESOLUTION
One workaround is to pass the database and Recordset names as string arguments to an Excel procedure using Automation. The Excel procedure then creates the Database and Recordset objects and can therefore use CopyFromRecordset using its own Recordset object.
Another workaround is to copy the Recordset into an array and pass the array to Excel using Automation. Following is a step-by-step example on how to do this:
- Start Visual Basic. Form1 is created by default.
- Add a command button to Form1.
Add a data control and set its properties as follows:
DatabaseName biblio.mdb RecordSource Authors
Add the following code to the General Declarations section of Form1:
' User defined type to help determine the ' starting cell in the range receiving the recordset Option Explicit Private Type ExlCell row As Long col As Long End Type Private Sub CopyRecords(rs As Recordset, ws As Object, _ StartingCell As ExlCell) Dim SomeArray() As Variant Dim row As Long, col As Long ' You might want to check if rs is not empty ' before re-dimensioning the array rs.MoveLast ReDim SomeArray(rs.RecordCount - 1, rs.Fields.Count - 1) ' Copy rs to some array rs.MoveFirst For row = 0 To rs.RecordCount - 1 For col = 0 To rs.Fields.Count - 1 SomeArray(row, col) = rs.Fields(col).Value ' Excel will be offended if you try setting one ' of its cells to a NULL If IsNull(SomeArray(row, col)) Then _ SomeArray(row, col) = "" Next rs.MoveNext Next ' The range should have the same number of ' rows and cols as in the recordset ws.Range(ws.Cells(StartingCell.row, StartingCell.col), _ ws.Cells(StartingCell.row + rs.RecordCount - 1, _ StartingCell.col + rs.Fields.Count - 1)).Value = SomeArray End Sub Private Sub Command1_Click() Dim stcell As ExlCell Dim objExlApp As Object ' Get an Excel app object reference On Error Resume Next Set objExlApp = GetObject(, "Excel.Application") ' If Excel is not launched start it If Err = 429 Then Err = 0 Set objExlApp = CreateObject("Excel.Application") ' Can't create object If Err = 429 Then MsgBox Err & ": " & Error Exit Sub End If End If On Error GoTo 0 ' Add a new Workbook objExlApp.Workbooks.Add ' Select the first sheet objExlApp.Worksheets("sheet1").Select ' Start fill range at A1 stcell.row = 1 stcell.col = 1 ' Call CopyRecords procedure to populate sheet with array CopyRecords Data1.Recordset, objExlApp.ActiveSheet, stcell ' Show Excel and kill reference objExlApp.Visible = True objExlApp.Interactive = True Set objExlApp = Nothing End Sub
- Start the project and click the command button. Excel should start (if it is not already started) and fill the first sheet of a new workbook with the rows from the Authors table in the biblio.mdb database.
MORE INFORMATION
When you program in Excel using VBA, the most efficient way to fill a sheet's range from a Recordset is by using the CopyFromRecordset method. However, when you try to call the CopyFromRecordset method using Automation from Visual Basic 4.0, it fails with a run-time error. A workaround would seem to pass the Recordset object to a procedure in Excel to allow the Excel procedure to issue the CopyFromRecordset method. However, when you try to pass the Recordset object to the Excel procedure, it also fails with a run-time error.
These limitations do not apply when you use Automation between Visual Basic 5.0 or later and Microsoft Excel 97.
Additional query words: Excel kbVBp
Keywords: kbinterop kbautomation kbprb KB172268