Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automaticly saving formula's to values when saving | Excel Discussion (Misc queries) | |||
Saving Excel 2007 conditional formatting in Excel 2003 format | Excel Discussion (Misc queries) | |||
Saving a spreadsheet without saving the Macro | Excel Programming | |||
editing an excel 97 file within excel 2003 and saving back as exce | Excel Discussion (Misc queries) | |||
Saving a Workbook: Forcing User to Rename before Saving | Excel Programming |