WelshWarrior


I am displaying info in a DataGridView from a spreadsheet that is then altered using buttons by the user. The spareadsheet is then saved back to the spreadsheet using another button. All this is working fine - no probs.

Problem is that there is a Macro in all of the cells in 1 column that keeps a running total by using the cell comment to store a value to stop circular reference problems. The info saves back into this cell from VB but the macro seems to not work properly in that only the last value saved it shown. My code is:


Code Block

Public Class frmViewer

Dim excelCon2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\SanctionsDb\2007-08\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")
Dim da2 As New OleDb.OleDbDataAdapter("Select * from [Points$] where ClsGrp Like '" & ClsGrp & "'", excelCon2)
Dim ds2 As New DataSet


Public Sub frmViewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim count As Integer


count = 0 ' count amount of pupils in class
lblForm.Text = ClsGrp

da2.Fill(ds2)
DataGridView1.DataSource = ds2.Tables(0)

DataGridView1.Columns("StudID").ReadOnly = True
DataGridView1.Columns("StudID").Width = "50"
DataGridView1.Columns("StudName").ReadOnly = True
DataGridView1.Columns("ClsGrp").ReadOnly = True
DataGridView1.Columns("ClsGrp").Width = "45"
DataGridView1.Columns("Pastoral").ReadOnly = True
DataGridView1.Columns("Assessment").ReadOnly = True
DataGridView1.Columns("Assessment").Width = "70"
DataGridView1.Columns("House").ReadOnly = True
DataGridView1.Columns("SessionTotal").ReadOnly = True
DataGridView1.Columns("SessionTotal").Width = "90"
DataGridView1.Columns("Target").ReadOnly = True
DataGridView1.Columns("Target").Width = "70"
DataGridView1.Columns("CumTotal").Width = "70"
'DataGridView1.Columns("CumTotal").ReadOnly = True
DataGridView1.Columns("OnTrip").ReadOnly = True
DataGridView1.Columns("OnTrip").Width = "70"

DataGridView1.CurrentCell.Value = Nothing
For Each row As DataGridViewRow In DataGridView1.Rows
count = count + 1

Next
lblTotal.Text = count 'add the count to the label


Dim update As New OleDb.OleDbCommand
Dim sb As New System.Text.StringBuilder("update [points$] SET ", 1024)

'assume StudID as the primary key

For Each c As DataColumn In ds2.Tables(0).Columns
If Array.IndexOf(New String() {"StudID", "Target", "SessionTotal", "OnTrip"}, c.ColumnName) = -1 Then
sb.AppendFormat("[{0}] = ,", c.ColumnName)
update.Parameters.Add(String.Format("@{0}", c.ColumnName), OleDb.OleDbType.VarWChar, 255, String.Format("{0}", c.ColumnName))

End If
Next
If sb.Chars(sb.Length - 1) = ","c Then sb.Remove(sb.Length - 1, 1)

'put the primary key in WHERE clause
sb.Append(" WHERE StudID = ")
update.Parameters.Add("@StudID", OleDb.OleDbType.VarWChar, 255, "StudID")

update.CommandText = sb.ToString()
update.Connection = excelCon2

da2.UpdateCommand = update
excelCon2.Close()
DecideColour(DataGridView1)


End Sub

Private Sub cmdExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExit.Click
LoginForm1.Close()
Me.Close()
excelCon2.Close()

End Sub

Private Sub cmdupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdupdate.Click

da2.Update(ds2.Tables(0))

Dim xlPath As String
Dim myExcel As Object
myExcel = CreateObject("Excel.Application")

xlPath = "S:\SanctionsDb\2007-08\points.xls"
myExcel.Visible = False
myExcel.Workbooks.Open(xlPath) 'open file
myExcel.DisplayAlerts = False
myExcel.ActiveWorkbook.SaveAs(FileName:=xlPath, CreateBackup:=True)

myExcel.ActiveWorkbook.Close(False) 'close file
myExcel.quit()
myExcel = Nothing

ds2.Clear()
da2.Fill(ds2)
DataGridView1.Refresh()
Me.Refresh()

DecideColour(DataGridView1)

End Sub

Private Sub cmdSanctions_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSanctions.Click
DataGridView1.Rows(DataGridView1.CurrentCell.RowIndex).Cells("Sanctions").Value = "2"

Dim row As DataGridViewRow
For Each row In DataGridView1.Rows
row.Cells("CumTotal").Value = row.Cells("SessionTotal").Value

Next
DecideColour(DataGridView1)
OnTrip(DataGridView1)
End Sub

Private Sub cmdAward_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAward.Click

For Each row As DataGridViewRow In DataGridView1.Rows
'Debug.Write(row.Index)
row.Cells("Points").Value = "2"
row.Cells("CumTotal").Value = row.Cells("SessionTotal").Value
Next
DecideColour(DataGridView1)
OnTrip(DataGridView1)
End Sub

Private Sub cmdClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClear.Click
Clear(DataGridView1)
End Sub

End Class

And the Macro code (provided by Microsoft) is:

Code Block

The Auto_Open name forces this macro to run every time
' the workbook containing this macro is opened.

Sub Auto_Open()
' Every time a cell's value is changed,
' the RunningTotal macro runs.
Application.OnEntry = "RunningTotal"
End Sub

'----------------------------------------------------------
' This macro runs each time the value of a cell changes.
' It adds the current value of the cell to the value of the
' cell comment. Then it stores the new total in the cell comment.
Sub RunningTotal()

On Error GoTo errorhandler ' Skip cells that have no comment.

With Application.Caller

' Checks to see if the cell is a running total by
' checking to see if the first 4 characters of the cell
' comment are "RT= ". NOTE: there is a space after the equal
' sign.
If Left(.Comment.Text, 4) = "RT= " Then

' Change the cell's value to the new value in the cell
' plus the old total stored in the cell comment.
RT = .Value + Right(.Comment.Text, Len(.Comment.Text) - 4)
.Value = RT

' Store the new total in the cell note.
.Comment.Text Text:="RT= " & RT
End If
End With

Exit Sub ' Skip over the errorhandler routine.

errorhandler: ' End the procedure if no comment in the cell.
Exit Sub

End Sub

'--------------------------------------------------------------
' This macro sets up a cell to be a running total cell.
Sub SetComment()
With ActiveCell
' Set comment to indicate that a running total is present.
' If the ActiveCell is empty, multiplying by 1 will
' return a 0.
.AddComment
.Comment.Text Text:="RT= " & (ActiveCell * 1)
End With
End Sub[/code]

If anyone could help, I would be very greatful! Wink

Thanks in advance




Re: Problem saving to spreadsheet with Macros in 1 column

Bruno Yu - MSFT


Move the thread from Visual Basic General. There are more exports on Visual Basic for Applications in this forum and I believe you can get the satisfying answers in this forum.

Thanks again for your question.