Excel 2003 vs 2007

[Bernie R.]

I have an Excel spreadsheet that draws data from Access. When we changed
from 2003 to 2007, the VB code/macro no longer draws the data from Access.
The error I now get is a Compile error: User-defined type not defined. The
line where I have "***" is where the error occurred.

Appreciate anyone's assistance. I am not familiar with visual basic and did
not write the macro. The person who wrote it is no longer with us.

If this isn't the right forum for this, I apologize and please point me in
the right direction.


Private Sub LoadData()
On Error GoTo ErrHandler
strConn2 = Me.Worksheets("LookUps").Cells(3, 6) 'Access file location,
in cell F3
strSQL = Me.Worksheets("LookUps").Cells(6, 6) 'Source table in cell F6
intDownloadCmd = Me.Worksheets("LookUps").Cells(3, 8) '1 or 0: 1 will
cause download
If intDownloadCmd <> 1 Then
Exit Sub
End If
Application.Calculation = xlCalculationManual
Dim rowctr, colctr, intColIndex As Integer
*** Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rstCount As New ADODB.Recordset
cnn.Open strConn1 & strConn2
rstCount.Open "Select Count(*) as RowCount from " & strSQL, cnn
rowctr = CInt(rstCount("RowCount"))
rstCount.Close
If rowctr < 1 Then
Err.Raise Number:=vbObjectError + 1000, _
Source:="LoadData", _
Description:="No records."
End If
rst.Open strSQL, cnn
colctr = rst.Fields.Count
Dim mySheet As Worksheet
Set mySheet = Sheets(SheetName)
'clear a 10,000-row by 250-column block of cells
mySheet.Range(mySheet.Cells(1, 1), mySheet.Cells(10000, 250)).Clear
'put in the column headers from the field names
For intColIndex = 0 To colctr - 1
'fields are 0-based, cell cols are 1-based
mySheet.Cells(1, intColIndex + 1).Value = rst.Fields(intColIndex).Name
Next
rst.MoveFirst
With mySheet
.Range("A2").CopyFromRecordset rst
End With
SubExit:
If rstCount.State = adStateOpen Then rstCount.Close
If rst.State = adStateOpen Then rst.Close
If cnn.State = adStateOpen Then cnn.Close
Set rstCount = Nothing
Set rst = Nothing
Set cnn = Nothing
Application.Calculate
Application.Calculation = xlCalculationAutomatic
Exit Sub

ErrHandler:
MsgBox Err.Description
GoTo SubExit
End Sub

 

[Bob Phillips]

It sounds as though you don't have a reference (Tools>References) to the
ActiveX Data Objects library.

 

[B Lynn B]

In the VBA window, while you have the workbook project selected, go to menu
item Tools, then References. Scroll a ways down until you come to several
items in a row that read "Microsoft ActiveX Data Objects 2.[x] Library", and
check one of them. I know for sure that 2.7 works with my Offc Prof version
of Excel 2007 - it may be that the others do as well, but that one should be
a safe bet. Save the workbook when you're done.

If you should happen to have any other files that use this object type,
you'll need to reference the library separately for each of them. It doesn't
seem to persist for the Excel application in general. (or at least that's
what I've experienced).

 

[Bernie R.]

I believe you are correct; however, I don't have access to "References". We
did this a year ago with 2003. Is it possible to do this on another computer
and then put it back on our server?

In the VBA window, while you have the workbook project selected, go to menu
item Tools, then References. Scroll a ways down until you come to several
items in a row that read "Microsoft ActiveX Data Objects 2.[x] Library", and
check one of them. I know for sure that 2.7 works with my Offc Prof version
of Excel 2007 - it may be that the others do as well, but that one should be
a safe bet. Save the workbook when you're done.

If you should happen to have any other files that use this object type,
you'll need to reference the library separately for each of them. It doesn't
seem to persist for the Excel application in general. (or at least that's
what I've experienced).

I have an Excel spreadsheet that draws data from Access. When we changed
from 2003 to 2007, the VB code/macro no longer draws the data from Access.
The error I now get is a Compile error: User-defined type not defined. The
line where I have "***" is where the error occurred.

Appreciate anyone's assistance. I am not familiar with visual basic and did
not write the macro. The person who wrote it is no longer with us.

If this isn't the right forum for this, I apologize and please point me in
the right direction.


Private Sub LoadData()
On Error GoTo ErrHandler
strConn2 = Me.Worksheets("LookUps").Cells(3, 6) 'Access file location,
in cell F3
strSQL = Me.Worksheets("LookUps").Cells(6, 6) 'Source table in cell F6
intDownloadCmd = Me.Worksheets("LookUps").Cells(3, 8) '1 or 0: 1 will
cause download
If intDownloadCmd <> 1 Then
Exit Sub
End If
Application.Calculation = xlCalculationManual
Dim rowctr, colctr, intColIndex As Integer
*** Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rstCount As New ADODB.Recordset
cnn.Open strConn1 & strConn2
rstCount.Open "Select Count(*) as RowCount from " & strSQL, cnn
rowctr = CInt(rstCount("RowCount"))
rstCount.Close
If rowctr < 1 Then
Err.Raise Number:=vbObjectError + 1000, _
Source:="LoadData", _
Description:="No records."
End If
rst.Open strSQL, cnn
colctr = rst.Fields.Count
Dim mySheet As Worksheet
Set mySheet = Sheets(SheetName)
'clear a 10,000-row by 250-column block of cells
mySheet.Range(mySheet.Cells(1, 1), mySheet.Cells(10000, 250)).Clear
'put in the column headers from the field names
For intColIndex = 0 To colctr - 1
'fields are 0-based, cell cols are 1-based
mySheet.Cells(1, intColIndex + 1).Value = rst.Fields(intColIndex).Name
Next
rst.MoveFirst
With mySheet
.Range("A2").CopyFromRecordset rst
End With
SubExit:
If rstCount.State = adStateOpen Then rstCount.Close
If rst.State = adStateOpen Then rst.Close
If cnn.State = adStateOpen Then cnn.Close
Set rstCount = Nothing
Set rst = Nothing
Set cnn = Nothing
Application.Calculate
Application.Calculation = xlCalculationAutomatic
Exit Sub

ErrHandler:
MsgBox Err.Description
GoTo SubExit
End Sub

 

[B Lynn B]

I'm not exactly sure what it means that you "don't have access". Are you
able to view the Microsoft VB editing screen, but the "References" item is
greyed out on the Tools menu item? It's been a while since I did an
Office/Excel install, and haven't ever used a version other than
"Professional", so don't know at what point that feature is determined. If
you have a company help desk, that would be a good place to start.

However, to answer your question - yes, this can be done on any computer
where the installed Excel version has ability to add reference libraries.
When you add it and save the file, the reference goes with the file. It
shouldn't make a difference where you store the file.

I believe you are correct; however, I don't have access to "References". We
did this a year ago with 2003. Is it possible to do this on another computer
and then put it back on our server?

In the VBA window, while you have the workbook project selected, go to menu
item Tools, then References. Scroll a ways down until you come to several
items in a row that read "Microsoft ActiveX Data Objects 2.[x] Library", and
check one of them. I know for sure that 2.7 works with my Offc Prof version
of Excel 2007 - it may be that the others do as well, but that one should be
a safe bet. Save the workbook when you're done.

If you should happen to have any other files that use this object type,
you'll need to reference the library separately for each of them. It doesn't
seem to persist for the Excel application in general. (or at least that's
what I've experienced).

I have an Excel spreadsheet that draws data from Access. When we changed
from 2003 to 2007, the VB code/macro no longer draws the data from Access.
The error I now get is a Compile error: User-defined type not defined. The
line where I have "***" is where the error occurred.

Appreciate anyone's assistance. I am not familiar with visual basic and did
not write the macro. The person who wrote it is no longer with us.

If this isn't the right forum for this, I apologize and please point me in
the right direction.


Private Sub LoadData()
On Error GoTo ErrHandler
strConn2 = Me.Worksheets("LookUps").Cells(3, 6) 'Access file location,
in cell F3
strSQL = Me.Worksheets("LookUps").Cells(6, 6) 'Source table in cell F6
intDownloadCmd = Me.Worksheets("LookUps").Cells(3, 8) '1 or 0: 1 will
cause download
If intDownloadCmd <> 1 Then
Exit Sub
End If
Application.Calculation = xlCalculationManual
Dim rowctr, colctr, intColIndex As Integer
*** Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rstCount As New ADODB.Recordset
cnn.Open strConn1 & strConn2
rstCount.Open "Select Count(*) as RowCount from " & strSQL, cnn
rowctr = CInt(rstCount("RowCount"))
rstCount.Close
If rowctr < 1 Then
Err.Raise Number:=vbObjectError + 1000, _
Source:="LoadData", _
Description:="No records."
End If
rst.Open strSQL, cnn
colctr = rst.Fields.Count
Dim mySheet As Worksheet
Set mySheet = Sheets(SheetName)
'clear a 10,000-row by 250-column block of cells
mySheet.Range(mySheet.Cells(1, 1), mySheet.Cells(10000, 250)).Clear
'put in the column headers from the field names
For intColIndex = 0 To colctr - 1
'fields are 0-based, cell cols are 1-based
mySheet.Cells(1, intColIndex + 1).Value = rst.Fields(intColIndex).Name
Next
rst.MoveFirst
With mySheet
.Range("A2").CopyFromRecordset rst
End With
SubExit:
If rstCount.State = adStateOpen Then rstCount.Close
If rst.State = adStateOpen Then rst.Close
If cnn.State = adStateOpen Then cnn.Close
Set rstCount = Nothing
Set rst = Nothing
Set cnn = Nothing
Application.Calculate
Application.Calculation = xlCalculationAutomatic
Exit Sub

ErrHandler:
MsgBox Err.Description
GoTo SubExit
End Sub