By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,986 Members | 1,522 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,986 IT Pros & Developers. It's quick & easy.

Problem inserting database value of numeric type into Excel

100+
P: 198
Hello expert.
I made a program in which the data from an SQL table is transferred to an Excel sheet.
When the data type of a field is Integer it works fine, but when the data type of a field is "numeric" it produces the error "APPLICATION ERROR OR OBJECT DEFINED ERROR" but I can't change the data type of the table from Numeric to Integer.
Please provide some idea.

Thanks in advance.
Dec 15 '07 #1
Share this Question
Share on Google+
14 Replies


QVeen72
Expert 100+
P: 1,445
Hi.

I'm not sure if the error has got to do anything with the data type of the Field.
Did you check if the field is Null?
Before populating an Excel cell check for Not Null.

Regards,
Veena.
Dec 15 '07 #2

100+
P: 198
Hello expert.
I am not properly understanding how I can check the Excel cell for not null.
Please provide some method.
Thanks.


I'm not sure if the error has got to do anything with the data type of the Field.
Did you check if the Field is Null?
Before populating the Excel cell check for Not Null.
Dec 17 '07 #3

Expert 5K+
P: 8,434
I am not properly understanding how I can check the Excel cell for not null.
Veena meant to check that the value is not null, before you copy it to the cell in Excel.

Can you tell us what value is in the field, that you are trying to copy to Excel? It might help if you showed us the code that does the copy, too.
Dec 17 '07 #4

100+
P: 198
Hello expert.
I'm accessing two columns i,e entry and record. Both contain integer value
e,g entry=777and record=78.
The code for copying the records from table to Excel is:

Expand|Select|Wrap|Line Numbers
  1. xlWs.Cells(i, 10).Resize(reccount, fldcount) = Transpose(recarray) 
  2.  
  3. Function Transpose(v As Variant) As Variant
  4.     Dim Xupper, Yupper, x, y As Long
  5.     Dim temparray As Variant
  6.     Xupper = UBound(v, 2)
  7.     Yupper = UBound(v, 1)
  8.     ReDim temparray(Xupper, Yupper)
  9.  
  10.     For x = 0 To Xupper
  11.         For y = 0 To Yupper
  12.             temparray(x, y) = v(y, x)
  13.         Next y
  14.     Next x
  15.  
  16.     Transpose = temparray
  17. End Function
Is it helpful or I send the whole program code?




Veena meant to check that the value is not null, before you copy it to the cell in Excel.

Can you tell us what value is in the field, that you are trying to copy to Excel? It might help if you showed us the code that does the copy, too.
Dec 17 '07 #5

QVeen72
Expert 100+
P: 1,445
Hi Veer,

So in which line you are getting the error (for Numeric as you said)..?
What you are trying to do with the above code..?
Say, if the value is > 64k, and you are trying to Loop through all the Rows, you may get error. For Excel, 64k is Max Limit for Rows/columns..(per Sheet)


Regards
Veena
Dec 17 '07 #6

100+
P: 198
Hi Veena,
when i execute it works fine through the function and function return the value and insert into excel sheet
xlWs.Cells(i, 10).Resize(reccount, fldcount) = Transpose(recarray)
this is the line where i am getting error


Hi Veer,

So in which line you are getting the error (for Numeric as you said)..?

Regards
Veena
Dec 17 '07 #7

Expert 5K+
P: 8,434
So you're saying that if either field (record or entry) is of type Numeric, this code produces an error. And if they're both type Integer it doesn't. Correct?

However, none of this code refers to either of these fields, which seems surprising. Perhaps you should show us the rest of the code.
Dec 17 '07 #8

QVeen72
Expert 100+
P: 1,445
Hi,

So when you get the error, can you tell what are the Values of all the Variables in this line :

xlWs.Cells(i, 10).Resize(reccount, fldcount) = Transpose(recarray)


Regards
Veena
Dec 17 '07 #9

100+
P: 198
Hello expert.
Here is my code.
Expand|Select|Wrap|Line Numbers
  1. Dim con As ADODB.Connection
  2.             Dim rs As ADODB.Recordset
  3.             Dim squery As String
  4.             Dim xlApp As Object
  5.             Dim xlWb As Object
  6.             Dim xlWs As Object
  7. Dim reccount As Variant
  8.             Dim recarray As Variant
  9.             Dim fldcount As Variant
  10.             Dim i As Long
  11.             i = 1
  12.             Dim j As Integer
  13.             Dim a, k, s  As Variant
  14. Set con = New ADODB.Connection
  15.         Set rs = New ADODB.Recordset
  16.  
  17.         con.Open "Driver={SQL Server};Server=" & listserver & "; Database=shrmgmtDb;Uid=sa;Pwd=" & txtpassword & ";"
  18.                    squery = "select * from output"
  19.            rs.Open squery, con, adOpenDynamic, adLockBatchOptimistic, adCmdText
  20.  
  21.             Set xlApp = CreateObject("Excel.Application")
  22.             Set xlWb = xlApp.Workbooks.Open("c:\var.xls")
  23.             Set xlWs = xlWb.Worksheets("sheet1")
  24.  
  25.      xlApp.Visible = True
  26.      fldcount = rs.Fields.Count
  27.      recarray = rs.GetRows
  28.      reccount = UBound(recarray, 2) + 1
  29.   xlWs.Cells(i, 10).Resize(reccount, fldcount) = Transpose(recarray)
  30.   xlWb.SaveAs ("c:\var.xls")
  31. rs.Close
  32.     con.Close
  33.     Set rs = Nothing
  34.     Set con = Nothing
  35.  
  36.         Set xlWs = Nothing
  37.         Set xlWb = Nothing
  38.         Set xlApp = Nothing
  39.         Exit Sub
  40.  
  41. Function Transpose(v As Variant) As Variant
  42.     Dim Xupper, Yupper, x, y As Long
  43.     Dim temparray As Variant
  44.     Xupper = UBound(v, 2)
  45.     Yupper = UBound(v, 1)
  46.     ReDim temparray(Xupper, Yupper)
  47.  
  48.         For x = 0 To Xupper
  49.         For y = 0 To Yupper
  50.         temparray(x, y) = v(y, x)
  51.  
  52.         Next y
  53.         Next x
  54.  
  55.     Transpose = temparray
  56. End Function


So you're saying that if either field (record or entry) is of type Numeric, this code produces an error. And if they're both type Integer it doesn't. Correct?

However, none of this code refers to either of these fields, which seem surprising. Perhaps you should show us the rest of the code.
Dec 17 '07 #10

100+
P: 198
Hi,
I am sending you the whole code

Expand|Select|Wrap|Line Numbers
  1. Dim con As ADODB.Connection
  2. Dim rs As ADODB.Recordset
  3. Dim squery As String
  4. Dim xlApp As Object
  5. Dim xlWb As Object
  6. Dim xlWs As Object
  7. Dim reccount As Variant
  8. Dim recarray As Variant
  9. Dim fldcount As Variant
  10. Dim i As Long
  11. i = 1
  12. Dim j As Integer
  13. Dim a, k, s As Variant
  14. Set con = New ADODB.Connection
  15. Set rs = New ADODB.Recordset
  16.  
  17. con.Open "Driver={SQL Server};Server=" & listserver & "; Database=shrmgmtDb;Uid=sa;Pwd=" & txtpassword & ";"
  18. squery = "select * from output"
  19. rs.Open squery, con, adOpenDynamic, adLockBatchOptimistic, adCmdText
  20.  
  21. Set xlApp = CreateObject("Excel.Application")
  22. Set xlWb = xlApp.Workbooks.Open("c:\var.xls")
  23. Set xlWs = xlWb.Worksheets("sheet1")
  24.  
  25. xlApp.Visible = True
  26. fldcount = rs.Fields.Count
  27. recarray = rs.GetRows
  28. reccount = UBound(recarray, 2) + 1
  29. xlWs.Cells(i, 10).Resize(reccount, fldcount) = Transpose(recarray)
  30. xlWb.SaveAs ("c:\var.xls")
  31. rs.Close
  32. con.Close
  33. Set rs = Nothing
  34. Set con = Nothing
  35.  
  36. Set xlWs = Nothing
  37. Set xlWb = Nothing
  38. Set xlApp = Nothing
  39. Exit Sub
  40.  
  41. Function Transpose(v As Variant) As Variant
  42. Dim Xupper, Yupper, x, y As Long
  43. Dim temparray As Variant
  44. Xupper = UBound(v, 2)
  45. Yupper = UBound(v, 1)
  46. ReDim temparray(Xupper, Yupper)
  47.  
  48. For x = 0 To Xupper
  49. For y = 0 To Yupper
  50. temparray(x, y) = v(y, x)
  51.  
  52. Next y
  53. Next x
  54.  
  55. Transpose = temparray
  56. End Function


Hi,

So when you get the error, can you tell what are the Values of all the Variables in this line :

xlWs.Cells(i, 10).Resize(reccount, fldcount) = Transpose(recarray)


Regards
Veena
Dec 17 '07 #11

QVeen72
Expert 100+
P: 1,445
Hello,

I did ask you for values of variables (i, reccount, fldcount, recarray)
at the time of error

Regards
Veena
Dec 17 '07 #12

100+
P: 198
Hello
i=2
reccount=12
fldcount=2
recarray= both row and columns






Hello,

I did ask you for values of variables (i, reccount, fldcount, recarray)
at the time of error

Regards
Veena
Dec 17 '07 #13

QVeen72
Expert 100+
P: 1,445
HI,

How many rows and columns you have for recarrays...?
Dec 17 '07 #14

100+
P: 198
12 rows and 2 columns


HI,

How many rows and columns you have for recarrays...?
Dec 17 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.