Problem inserting database value of numeric type into Excel

[veer]

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 "APPLICATIO N 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.

[QVeen72]

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.

[veer]

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.

[Killer42]

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.

[veer]

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:

[CODE=vb]xlWs.Cells(i, 10).Resize(recc ount, fldcount) = Transpose(recar ray)

Function Transpose(v As Variant) As Variant
Dim Xupper, Yupper, x, y As Long
Dim temparray As Variant
Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim temparray(Xuppe r, Yupper)

For x = 0 To Xupper
For y = 0 To Yupper
temparray(x, y) = v(y, x)
Next y
Next x

Transpose = temparray
End Function[/CODE]

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.

[QVeen72]

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

[veer]

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(recc ount, fldcount) = Transpose(recar ray)
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

[Killer42]

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.

[QVeen72]

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(recc ount, fldcount) = Transpose(recar ray)


Regards
Veena

[veer]

Hello expert.
Here is my code.
[CODE=vb]Dim con As ADODB.Connectio n
Dim rs As ADODB.Recordset
Dim squery As String
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim reccount As Variant
Dim recarray As Variant
Dim fldcount As Variant
Dim i As Long
i = 1
Dim j As Integer
Dim a, k, s As Variant
Set con = New ADODB.Connectio n
Set rs = New ADODB.Recordset

con.Open "Driver={SQ L Server};Server= " & listserver & "; Database=shrmgm tDb;Uid=sa;Pwd= " & txtpassword & ";"
squery = "select * from output"
rs.Open squery, con, adOpenDynamic, adLockBatchOpti mistic, adCmdText

Set xlApp = CreateObject("E xcel.Applicatio n")
Set xlWb = xlApp.Workbooks .Open("c:\var.x ls")
Set xlWs = xlWb.Worksheets ("sheet1")

xlApp.Visible = True
fldcount = rs.Fields.Count
recarray = rs.GetRows
reccount = UBound(recarray , 2) + 1
xlWs.Cells(i, 10).Resize(recc ount, fldcount) = Transpose(recar ray)
xlWb.SaveAs ("c:\var.xls ")
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing

Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Exit Sub

Function Transpose(v As Variant) As Variant
Dim Xupper, Yupper, x, y As Long
Dim temparray As Variant
Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim temparray(Xuppe r, Yupper)

For x = 0 To Xupper
For y = 0 To Yupper
temparray(x, y) = v(y, x)

Next y
Next x

Transpose = temparray
End Function[/CODE]

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.

[veer]

Hi,
I am sending you the whole code

[CODE=vb]Dim con As ADODB.Connectio n
Dim rs As ADODB.Recordset
Dim squery As String
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim reccount As Variant
Dim recarray As Variant
Dim fldcount As Variant
Dim i As Long
i = 1
Dim j As Integer
Dim a, k, s As Variant
Set con = New ADODB.Connectio n
Set rs = New ADODB.Recordset

con.Open "Driver={SQ L Server};Server= " & listserver & "; Database=shrmgm tDb;Uid=sa;Pwd= " & txtpassword & ";"
squery = "select * from output"
rs.Open squery, con, adOpenDynamic, adLockBatchOpti mistic, adCmdText

Set xlApp = CreateObject("E xcel.Applicatio n")
Set xlWb = xlApp.Workbooks .Open("c:\var.x ls")
Set xlWs = xlWb.Worksheets ("sheet1")

xlApp.Visible = True
fldcount = rs.Fields.Count
recarray = rs.GetRows
reccount = UBound(recarray , 2) + 1
xlWs.Cells(i, 10).Resize(recc ount, fldcount) = Transpose(recar ray)
xlWb.SaveAs ("c:\var.xls ")
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing

Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Exit Sub

Function Transpose(v As Variant) As Variant
Dim Xupper, Yupper, x, y As Long
Dim temparray As Variant
Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim temparray(Xuppe r, Yupper)

For x = 0 To Xupper
For y = 0 To Yupper
temparray(x, y) = v(y, x)

Next y
Next x

Transpose = temparray
End Function[/CODE]

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(recc ount, fldcount) = Transpose(recar ray)


Regards
Veena

[QVeen72]

Hello,

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

Regards
Veena

[veer]

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

[QVeen72]

HI,

How many rows and columns you have for recarrays...?

[veer]

12 rows and 2 columns

HI,

How many rows and columns you have for recarrays...?