D
den 2005
Hi,
I am experimenting how to update the values of Excel application as its
values being changed in a datagrid. It does not work. Here is the code.
Hopefully, someone could spot what is wrong with it...How would you get the
columns used and rows used??
Basically, I like to get data and update the data or add new data and save
changes to the execl application. How would I do that the shortest and
fastest way. Thanks.
Using VS 2002 (C# or VB.Net) and Office 2000
***Code***
Dim ctr As Integer = dgvData.ColumnCount
Dim cell1 As String, cell2 As String, cell3 As String, cell4 As
String, cell5 As String
Dim dt As Data.DataTable
If (ds.HasChanges()) Then
dt = ds.Tables(0).GetChanges()
Dim exApp As New Excel.Application()
exApp.GetOpenFilename(txtFilePath.Text.Trim(), "Excel Files
(*.xls)|*.xls", "Practice", Nothing, Nothing)
Dim wkbook As Excel.Workbook =
exApp.Workbooks.Open(txtFilePath.Text.Trim)
wkbook.Activate()
'wkbook.Worksheets()
'exApp.ActiveWorkbook.Sheets(1).Select()
Dim sheet As Worksheet = wkbook.Sheets(1)
'Dim rng As Range = exApp.Range("A1")
'Dim r As Integer = exApp.Rows.Count
'Dim r As Int16 = sheet.Rows.Count
'Dim c As Integer = exApp.Columns.Count
'Dim c As Int16 = sheet.Columns.Count
Dim r As Int16 = 24
Dim c As Int16 = 5
Dim x As Int16, y As Int16 = 1
For x = 1 To r
'Check if values change before changing the cells in the excel app
cell1 = sheet.Cells(x, y)
If cell1 <> dgvData.Rows(x).Cells(y).Value Then
sheet.Cells(x, y) = dgvData.Rows(x).Cells(y).Value
End If
cell2 = sheet.Cells(x, y + 1).ToString()
If cell2 <> dgvData.Rows(x).Cells(y + 1).Value Then
sheet.Cells(x, y + 1) = dgvData.Rows(x).Cells(y + 1).Value
End If
cell3 = sheet.Cells(x, y + 2).ToString()
If cell1 <> dgvData.Rows(x).Cells(y + 2).Value Then
sheet.Cells(x, y + 2) = dgvData.Rows(x).Cells(y + 2).Value
End If
cell4 = sheet.Cells(x, y + 3).ToString()
If cell1 <> dgvData.Rows(x).Cells(y + 3).Value Then
sheet.Cells(x, y + 3) = dgvData.Rows(x).Cells(y + 3).Value
End If
cell5 = sheet.Cells(x, y + 4).ToString()
If cell1 <> dgvData.Rows(x).Cells(y + 4).Value Then
sheet.Cells(x, y + 4) = dgvData.Rows(x).Cells(y + 4).Value
End If
Next
End If
*******
Dennis
I am experimenting how to update the values of Excel application as its
values being changed in a datagrid. It does not work. Here is the code.
Hopefully, someone could spot what is wrong with it...How would you get the
columns used and rows used??
Basically, I like to get data and update the data or add new data and save
changes to the execl application. How would I do that the shortest and
fastest way. Thanks.
Using VS 2002 (C# or VB.Net) and Office 2000
***Code***
Dim ctr As Integer = dgvData.ColumnCount
Dim cell1 As String, cell2 As String, cell3 As String, cell4 As
String, cell5 As String
Dim dt As Data.DataTable
If (ds.HasChanges()) Then
dt = ds.Tables(0).GetChanges()
Dim exApp As New Excel.Application()
exApp.GetOpenFilename(txtFilePath.Text.Trim(), "Excel Files
(*.xls)|*.xls", "Practice", Nothing, Nothing)
Dim wkbook As Excel.Workbook =
exApp.Workbooks.Open(txtFilePath.Text.Trim)
wkbook.Activate()
'wkbook.Worksheets()
'exApp.ActiveWorkbook.Sheets(1).Select()
Dim sheet As Worksheet = wkbook.Sheets(1)
'Dim rng As Range = exApp.Range("A1")
'Dim r As Integer = exApp.Rows.Count
'Dim r As Int16 = sheet.Rows.Count
'Dim c As Integer = exApp.Columns.Count
'Dim c As Int16 = sheet.Columns.Count
Dim r As Int16 = 24
Dim c As Int16 = 5
Dim x As Int16, y As Int16 = 1
For x = 1 To r
'Check if values change before changing the cells in the excel app
cell1 = sheet.Cells(x, y)
If cell1 <> dgvData.Rows(x).Cells(y).Value Then
sheet.Cells(x, y) = dgvData.Rows(x).Cells(y).Value
End If
cell2 = sheet.Cells(x, y + 1).ToString()
If cell2 <> dgvData.Rows(x).Cells(y + 1).Value Then
sheet.Cells(x, y + 1) = dgvData.Rows(x).Cells(y + 1).Value
End If
cell3 = sheet.Cells(x, y + 2).ToString()
If cell1 <> dgvData.Rows(x).Cells(y + 2).Value Then
sheet.Cells(x, y + 2) = dgvData.Rows(x).Cells(y + 2).Value
End If
cell4 = sheet.Cells(x, y + 3).ToString()
If cell1 <> dgvData.Rows(x).Cells(y + 3).Value Then
sheet.Cells(x, y + 3) = dgvData.Rows(x).Cells(y + 3).Value
End If
cell5 = sheet.Cells(x, y + 4).ToString()
If cell1 <> dgvData.Rows(x).Cells(y + 4).Value Then
sheet.Cells(x, y + 4) = dgvData.Rows(x).Cells(y + 4).Value
End If
Next
End If
*******
Dennis