Updating an excel spreadsheet from SQL server using ADO

[mattyp75]

I am attempting to insert values in an SQL server 2000 database table into an Excel spreadsheet. I've tried the same code on an Access database (with the connection string modified) and it works fine, but when I run it on my SQL server table it falls over with a Run time error - 1004 Application defined or object defined error. The data type of the column it seems to have exception to is of type decimal(10,2). It seems fine if the column is a varchar. Can anyone explain why this is happening?

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Dim strconn As String
Dim strSQL As String
Dim sumCnt As Integer
Dim sumTrn As Long, sumCst As Long

cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDatabase;Data Source=ExpServer"
strSQL = "SELECT * FROM dbo.Final ORDER BY ReportCategoryID"
cnn.Open
rst.Open strSQL, cnn

Dim ExcelAp As Excel.Application
Dim ExcelWb As Excel.Workbook
Dim ExcelWs As Excel.Worksheet

Set ExcelAp = CreateObject("Excel.Application&quot
Set ExcelWb = ExcelAp.Workbooks.Open("d:\project\Reports\Test.xls&quot
Set ExcelWs = ExcelWb.Worksheets(1)


Dim strWSName As String
strWSName = InputBox("Please enter the Worksheet title", "Worksheet Title Inputbox&quot
ExcelWs.Name = strWSName
ExcelAp.Visible = True
sumCnt = 0
sumTrn = 0
sumCst = 0
Row = 8
rst.MoveFirst
Do While Not (rst.EOF Or rst.BOF)

ExcelWs.Cells(Row, 10).Value = rst.Fields(2).Value
ExcelWs.Cells(Row, 2).Value = rst.Fields(3).Value
ExcelWs.Cells(Row, 6).Value = rst.Fields(4).Value


sumCst = sumCst + rst.Fields(2).Value
sumCnt = sumCnt + rst.Fields(3).Value
sumTrn = sumTrn + rst.Fields(4).Value



Row = Row + 1

rst.MoveNext

Loop

ExcelWs.Cells(17, 2).Value = sumCnt
ExcelWs.Cells(17, 1).Value = sumTrn
ExcelWs.Cells(17, 6).Value = sumCst



'Save the sheet to C:\Test.xls directory.
ExcelWs.SaveAs ("d:\temp\TestNew.xls&quot 'this is not case sensitive

'close workbooks
ExcelWb.Close

ExcelAp.Quit

'Close all objects
Set ExcelWs = Nothing
Set ExcelWb = Nothing
Set ExcelAp = Nothing

rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing

 

[MarkSweetland]

I'm guessing here, but all your variables are declared as either integers or long data types. Could it be that you are trying to save a SQL Decimal(precision, scale) data type in a cell which is referenced by another formula cell that expects an integer? The decimal data type precision is the number of digits in a number; scale is the number of digits to the right of the decimal point. For example, the number 123.45 has a precision of 5 and a scale of 2.

If you are storing integers in a SQL server varchar column it will return a string with that integer--which probably is promoted by Excel (and VB for that matter) to an integer for any calculations. However, if you store an integer in a decimal(10,2) column, SQL will return the number with the prescribe scale (####.00) regardless if any fraction is stored.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach