Help inserting function into Excel

[dakota97]

Hi all,

Im having a problem inserting a functin into an Excel worksheet. What Im doing is taking information from my DB and inserting it into an Excel worksheet. Im able to get the values for each field and transfer them successfully, however the problem Im running into is inserting a function to calculate the total of a column in the worksheet once the data is inserted. My code is as follows:


Copy the ProductSales.xls file to the REPORTS folder, and rename it
according to the transaction ID number
Dim strPath As String = Application.StartupPath()
If Not IO.Directory.Exists(strPath & "\Reports") Then
IO.Directory.CreateDirectory(strPath & "\Reports")
End If
Dim strDate As String = Date.Now.ToShortDateString
strDate = strDate.Replace("/", "-")
Dim strNewFile As String = strPath & "\Reports\DailySales" & strDate & ".xls"

Try
strTransNum = DateTime.Now.ToString("MM/dd/yy") & "0001"
strTransNum = Convert.ToDecimal(strTransNum.Replace("/", ""))
If strTransNum.Length < 10 Then
strTransNum = "0" & strTransNum
End If

Retrieve the product information from tblTransaction
DbConn.Open()
Dim DbCommand As New OleDbCommand("SELECT * FROM tblTransaction WHERE TransId >=" & strTransNum & "", DbConn)
Dim DbReader As OleDbDataReader = DbCommand.ExecuteReader
If DbReader.HasRows Then
IO.File.Copy("DailySales.xls", strNewFile)

Open the new .xls file and update the cells with the information
for the report
Dim xlsConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strNewFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim conn As New System.Data.OleDb.OleDbConnection(xlsConn)
conn.Open()
Dim XlsCommand As New OleDbCommand
Dim i As Integer = 6
Do While DbReader.Read
If DbReader("ProdId") Is System.DBNull.Value Then
Exit Do
Else
If i / 56 = 1 Or i / 56 = 2 Or i / 56 = 3 Or i / 56 = 4 Or i / 56 = 5 Or i / 56 = 6 Or i / 56 = 7 Or i / 56 = 8 Or i / 56 = 9 Or i / 56 = 10 Or i / 56 = 11 Or i / 56 = 12 Or i / 56 = 13 Or i / 56 = 14 Or i / 56 = 15 Or i / 56 = 16 Or i / 56 = 17 Or i / 56 = 18 Or i / 56 = 19 Or i / 56 = 20 Then
Insert the column headings for the next page of the report
XlsCommand.CommandText = "UPDATE [Sheet1$A" & i & ":A" & i & "] SET F1 =Item Number"
XlsCommand.Connection = conn
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$B" & i & ":B" & i & "] SET F1 =Date Sold"
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$C" & i & ":C" & i & "] SET F1 =Qty"
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$D" & i & "" & i & "] SET F1 =Total"
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$E" & i & ":E" & i & "] SET F1 =Transaction ID"
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$F" & i & ":F" & i & "] SET F1 =Customer Account"
XlsCommand.ExecuteNonQuery()
i += 2
Else

Update the cells with the information for the report
XlsCommand.CommandText = "UPDATE [Sheet1$A" & i & ":A" & i & "] SET F1 =" & DbReader("ProdId") & ""
XlsCommand.Connection = conn
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$B" & i & ":B" & i & "] SET F1 =" & Convert.ToDateTime(DbReader("Timestamp")).ToShortDateString & ""
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$C" & i & ":C" & i & "] SET F1 =" & DbReader("Qty") & ""
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$D" & i & "" & i & "] SET F1 =" & DbReader("Price") & ""
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$E" & i & ":E" & i & "] SET F1 =" & DbReader("TransId") & ""
XlsCommand.ExecuteNonQuery()
XlsCommand.CommandText = "UPDATE [Sheet1$F" & i & ":F" & i & "] SET F1 =" & DbReader("AccountNum") & ""
XlsCommand.ExecuteNonQuery()
i += 1
End If
End If
Loop

-----RIGHT HERE IS MY PROBLEM!!!----------------------------------------

i += 1
XlsCommand.CommandText = "UPDATE [Sheet1$D" & i & "" & i & "] SET F1 ==SUM(D6" & i & ")"
XlsCommand.ExecuteNonQuery()

--------------------------------------------------------------------------

conn.Close()
XlsCommand.Dispose()
DbConn.Close()
DbCommand.Dispose()
DbReader.Close()


What I can see that it is doing is adding a single quote to the text before it enters the string into the cell. I opened up the worksheet after I set a breakpoint, and looked at the value that was entered into the cell. The example that I can give you is as follows:

=SUM(D610)

Obviously Excel is interpreting this as a text string and not a function. Any ideas on how I can eliminate the at the beginning?

Thanks in advance,

Chris