User defined type not defined - Error
-
Hi, I am facing this error for the following code. please reply and do the needful Private Sub Command0_Click() Dim DbAdo As ADODB.Connection Dim RsAdo As ADODB.Recordset Set DbAdo = CurrentProject.Connection Set RsAdo = New ADODB.Recordset Dim stDocName As String Dim stDocName1 As String Dim stDocName2 As String Dim stDocName3 As String Dim i, j As Integer Dim str1 As String Dim K As Integer stDocName = "Personal" RsAdo.Open stDocName, DbAdo, adOpenStatic, adLockPessimistic Dim createExcel As New excel.Application Dim Wbook As excel.Workbook Dim Wsheet As excel.Worksheet Set Wbook = createExcel.Workbooks.Add Set Wsheet = Wbook.Worksheets("Sheet1") Wsheet.Name = "Test" i = 0 j = 0 'Wsheet.Cells(1, 1) = "Hosp ID : " & Me.HCO 'Wsheet.Cells(2, 1) = "Process : " & Me.ThisPdProcNum 'Wsheet.Cells(3, 1) = "Processing Period : " & Me.ThisPdStartDate & " - " & Me.ThisPdStopDate 'Wsheet.Cells(1, 1).Font.Bold = True 'Wsheet.Cells(2, 1).Font.Bold = True 'Wsheet.Cells(3, 1).Font.Bold = True 'Wsheet.Cells(5, 1) = "GL Research" 'Wsheet.Cells(8, 1) = "Unmapped Cost Centres" 'Wsheet.Cells(5, 1).Font.Bold = True 'Wsheet.Cells(5, 1).Font.Size = "20" 'Wsheet.Cells(8, 1).Font.Bold = True 'Wsheet.Cells(1, 1).EntireColumn.ColumnWidth = "10" 'Wsheet.Range("A:IV").Interior.ColorIndex = 2 'Wsheet.Range("A:IV").Interior.Pattern = xlSolid 'Wsheet.Range("A:IV").Interior.PatternColorIndex = xlAutomatic For i = 0 To RsAdo.Fields.Count - 1 With Wsheet.Cells(9, i + 1) .Font.Size = "9" .Font.Bold = True .Interior.ColorIndex = 15 .EntireColumn.ColumnWidth = 15 .EntireColumn.AutoFit .HorizontalAlignment = xlHAlignCenter .Value = RsAdo.Fields(i).Name End With Next i If (RsAdo.RecordCount > 0) Then RsAdo.MoveFirst cnt = 10 Do Until RsAdo.EOF Wsheet.Range("A" & Trim(Str(cnt))).Value = RsAdo("sno") Wsheet.Range("B" & Trim(Str(cnt))).Value = RsAdo("name") Wsheet.Range("C" & Trim(Str(cnt))).Value = RsAdo("age") 'Wsheet.Range("D" & Trim(Str(cnt))).Value = RsAdo("CCDESCR") 'Wsheet.Range("H" & Trim(Str(cnt))).Value = RsAdo("On CC RD XREF") 'Wsheet.Range("I" & Trim(Str(cnt))).Value = RsAdo("On New Base") 'Wsheet.Range("J" & Trim(Str(cnt))).Value = RsAdo("On New Fin") RsAdo.MoveNext Wsheet.Range("A" & Trim(Str(cnt))).Borders.LineStyl
-
Hi, I am facing this error for the following code. please reply and do the needful Private Sub Command0_Click() Dim DbAdo As ADODB.Connection Dim RsAdo As ADODB.Recordset Set DbAdo = CurrentProject.Connection Set RsAdo = New ADODB.Recordset Dim stDocName As String Dim stDocName1 As String Dim stDocName2 As String Dim stDocName3 As String Dim i, j As Integer Dim str1 As String Dim K As Integer stDocName = "Personal" RsAdo.Open stDocName, DbAdo, adOpenStatic, adLockPessimistic Dim createExcel As New excel.Application Dim Wbook As excel.Workbook Dim Wsheet As excel.Worksheet Set Wbook = createExcel.Workbooks.Add Set Wsheet = Wbook.Worksheets("Sheet1") Wsheet.Name = "Test" i = 0 j = 0 'Wsheet.Cells(1, 1) = "Hosp ID : " & Me.HCO 'Wsheet.Cells(2, 1) = "Process : " & Me.ThisPdProcNum 'Wsheet.Cells(3, 1) = "Processing Period : " & Me.ThisPdStartDate & " - " & Me.ThisPdStopDate 'Wsheet.Cells(1, 1).Font.Bold = True 'Wsheet.Cells(2, 1).Font.Bold = True 'Wsheet.Cells(3, 1).Font.Bold = True 'Wsheet.Cells(5, 1) = "GL Research" 'Wsheet.Cells(8, 1) = "Unmapped Cost Centres" 'Wsheet.Cells(5, 1).Font.Bold = True 'Wsheet.Cells(5, 1).Font.Size = "20" 'Wsheet.Cells(8, 1).Font.Bold = True 'Wsheet.Cells(1, 1).EntireColumn.ColumnWidth = "10" 'Wsheet.Range("A:IV").Interior.ColorIndex = 2 'Wsheet.Range("A:IV").Interior.Pattern = xlSolid 'Wsheet.Range("A:IV").Interior.PatternColorIndex = xlAutomatic For i = 0 To RsAdo.Fields.Count - 1 With Wsheet.Cells(9, i + 1) .Font.Size = "9" .Font.Bold = True .Interior.ColorIndex = 15 .EntireColumn.ColumnWidth = 15 .EntireColumn.AutoFit .HorizontalAlignment = xlHAlignCenter .Value = RsAdo.Fields(i).Name End With Next i If (RsAdo.RecordCount > 0) Then RsAdo.MoveFirst cnt = 10 Do Until RsAdo.EOF Wsheet.Range("A" & Trim(Str(cnt))).Value = RsAdo("sno") Wsheet.Range("B" & Trim(Str(cnt))).Value = RsAdo("name") Wsheet.Range("C" & Trim(Str(cnt))).Value = RsAdo("age") 'Wsheet.Range("D" & Trim(Str(cnt))).Value = RsAdo("CCDESCR") 'Wsheet.Range("H" & Trim(Str(cnt))).Value = RsAdo("On CC RD XREF") 'Wsheet.Range("I" & Trim(Str(cnt))).Value = RsAdo("On New Base") 'Wsheet.Range("J" & Trim(Str(cnt))).Value = RsAdo("On New Fin") RsAdo.MoveNext Wsheet.Range("A" & Trim(Str(cnt))).Borders.LineStyl
Sounds like you have a user defined datatype in your database that Excel does not understand. See which column(s) it is and cast them to their underlying type. I suspect if you step through in debug you will easily find the offending column.
Bob Ashfield Consultants Ltd
-
Hi, I am facing this error for the following code. please reply and do the needful Private Sub Command0_Click() Dim DbAdo As ADODB.Connection Dim RsAdo As ADODB.Recordset Set DbAdo = CurrentProject.Connection Set RsAdo = New ADODB.Recordset Dim stDocName As String Dim stDocName1 As String Dim stDocName2 As String Dim stDocName3 As String Dim i, j As Integer Dim str1 As String Dim K As Integer stDocName = "Personal" RsAdo.Open stDocName, DbAdo, adOpenStatic, adLockPessimistic Dim createExcel As New excel.Application Dim Wbook As excel.Workbook Dim Wsheet As excel.Worksheet Set Wbook = createExcel.Workbooks.Add Set Wsheet = Wbook.Worksheets("Sheet1") Wsheet.Name = "Test" i = 0 j = 0 'Wsheet.Cells(1, 1) = "Hosp ID : " & Me.HCO 'Wsheet.Cells(2, 1) = "Process : " & Me.ThisPdProcNum 'Wsheet.Cells(3, 1) = "Processing Period : " & Me.ThisPdStartDate & " - " & Me.ThisPdStopDate 'Wsheet.Cells(1, 1).Font.Bold = True 'Wsheet.Cells(2, 1).Font.Bold = True 'Wsheet.Cells(3, 1).Font.Bold = True 'Wsheet.Cells(5, 1) = "GL Research" 'Wsheet.Cells(8, 1) = "Unmapped Cost Centres" 'Wsheet.Cells(5, 1).Font.Bold = True 'Wsheet.Cells(5, 1).Font.Size = "20" 'Wsheet.Cells(8, 1).Font.Bold = True 'Wsheet.Cells(1, 1).EntireColumn.ColumnWidth = "10" 'Wsheet.Range("A:IV").Interior.ColorIndex = 2 'Wsheet.Range("A:IV").Interior.Pattern = xlSolid 'Wsheet.Range("A:IV").Interior.PatternColorIndex = xlAutomatic For i = 0 To RsAdo.Fields.Count - 1 With Wsheet.Cells(9, i + 1) .Font.Size = "9" .Font.Bold = True .Interior.ColorIndex = 15 .EntireColumn.ColumnWidth = 15 .EntireColumn.AutoFit .HorizontalAlignment = xlHAlignCenter .Value = RsAdo.Fields(i).Name End With Next i If (RsAdo.RecordCount > 0) Then RsAdo.MoveFirst cnt = 10 Do Until RsAdo.EOF Wsheet.Range("A" & Trim(Str(cnt))).Value = RsAdo("sno") Wsheet.Range("B" & Trim(Str(cnt))).Value = RsAdo("name") Wsheet.Range("C" & Trim(Str(cnt))).Value = RsAdo("age") 'Wsheet.Range("D" & Trim(Str(cnt))).Value = RsAdo("CCDESCR") 'Wsheet.Range("H" & Trim(Str(cnt))).Value = RsAdo("On CC RD XREF") 'Wsheet.Range("I" & Trim(Str(cnt))).Value = RsAdo("On New Base") 'Wsheet.Range("J" & Trim(Str(cnt))).Value = RsAdo("On New Fin") RsAdo.MoveNext Wsheet.Range("A" & Trim(Str(cnt))).Borders.LineStyl