Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 vs 2007
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 vs 2007
It sounds as though you don't have a reference (ToolsReferences) to the
ActiveX Data Objects library. --- HTH Bob Phillips "Bernie R." <Bernie wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 vs 2007
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." wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 vs 2007
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? "B Lynn B" wrote: 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." wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 vs 2007
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. "Bernie R." wrote: 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? "B Lynn B" wrote: 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." wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 versus Excel 2003 & Excel 97-2003 fully compatible | Excel Worksheet Functions | |||
Excel opens two windows (2007 & 2003 format): I want only 2003. | Excel Discussion (Misc queries) | |||
Excel 2003 help doesn't work. I got both Excel 2003 and 2007 insta | Excel Discussion (Misc queries) | |||
Excel 2007, I write macros in 2003 is 2007 similar for VBA? Pros andcons please | Excel Programming | |||
How to instantiate Excel 2003 when both 2003 and 2007 are installe | Excel Programming |