Saving Changes to Excel App
 

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

--
MCP Year 2005, Philippines

Saving Changes to Excel App
 

hi,
Is there any other shorter way to do save the data back to excel
application? This works. Thanks in advanced.

*** Code ***
'Getting Cells Rows w/Data
Dim r as Int16 = sheet.UsedRange.Rows.Count

For x = 2 To r
range1 = sheet.Range("A" & x)
range2 = sheet.Range("B" & x)
range3 = sheet.Range("C" & x)
range4 = sheet.Range("D" & x)
range5 = sheet.Range("E" & x)

t = range1.Rows.Count
cell1 = range1.Value
If cell1 < dgvData.Rows(x - 2).Cells(0).Value.ToString() Then
range1.Value = dgvData.Rows(x - 2).Cells(0).Value
End If
cell2 = range2.Value
If cell2 < dgvData.Rows(x - 2).Cells(1).Value.ToString() Then
range2.Value = dgvData.Rows(x - 2).Cells(1).Value
End If
cell3 = range3.Value
If cell3 < dgvData.Rows(x - 2).Cells(2).Value.ToString() Then
range3.Value = dgvData.Rows(x - 2).Cells(2).Value
End If
cell4 = range4.Value
If cell4 < dgvData.Rows(x - 2).Cells(3).Value.ToString() Then
range4.Value = dgvData.Rows(x - 2).Cells(3).Value
End If
cell5 = range5.Value
If cell5 < dgvData.Rows(x - 2).Cells(4).Value.ToString() Then
range5.Value = dgvData.Rows(x - 2).Cells(4).Value
End If
Next

exApp.ActiveWorkbook.Save()
exApp.ActiveWorkbook.Close(True)
******

den2005
--
MCP Year 2005, Philippines


"den 2005" wrote:

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

--
MCP Year 2005, Philippines