Access query to Excel

[izzyq]

I received this peice of code from a friend to bring in an access query into excel but I am having trouble with an error. "Compile Error: User Defined type not defined". I am not quite sure of the syntax to correct this error. This is the code that I have so far: The error seems to be on the third line.

Option Explicit

Sub GetData23()

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim xlWs As Object


Dim recArray As Variant

Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer

' Set the string to the path of your database
strDB = "\\\\Quartz\\Common\\Comptrol\\Corp_Rep\\Monthend\\2002\\Financial Operating Results\\Working Copies\\Costbudget.mdb"

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

' Open recordset based on Cost and Budget query
rst.Open "Select * from qryCostBudget", cnt

' Set the sheet where data will be copied
Set xlWs = ActiveSheet


' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

' Check version of Excel
If Val(Mid(Excel.Application.Version, 1, InStr(1, Excel.Application.Version, ".&quot - 1)) > 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset

' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets

Else
'EXCEL 97 or earlier: Use GetRows then copy array to Excel

' Copy recordset to an array
recArray = rst.GetRows
'Note: GetRows returns a 0-based array where the first
'dimension contains fields and the second dimension
'contains records. We will transpose this array so that
'the first dimension contains records, allowing the
'data to appears properly when copied to Excel

' Determine number of records

recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array


' Check the array for contents that are not valid when
' copying the array to an Excel worksheet
For iCol = 0 To fldCount - 1
For iRow = 0 To recCount - 1
' Take care of Date fields
If IsDate(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = Format(recArray(iCol, iRow))
' Take care of OLE object fields or array fields
ElseIf IsArray(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = "Array Field"
End If
Next iRow 'next record
Next iCol 'next field

' Transpose and Copy the array to the worksheet,
' starting in cell A2
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
TransposeDim(recArray)
End If

' Auto-fit the column widths and row heights
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit

' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

' Release Excel references
Set xlWs = Nothing

End Sub

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)

Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant

Xupper = UBound(v, 2)
Yupper = UBound(v, 1)

ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X

TransposeDim = tempArray

End Function Help Me Obi Wan Kenobi, You're My Only Hope.

 

[ETID]

You would not need to export the data to excel, (too many steps)...

Create a live link to the table or query from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there (to select your mdb, table or query)
then return your data to excel.

You can opt to use the wizard or MsQuery at this point...
after your comfy with this...I would recommend the MsQuery option.

when data is returned,...you can right click in the data area and select properties to refresh on open...fill formulas etc.


If you set to refresh on open,...then each time the xls is opened it background queries the database for the most current data.