Develop Office Application using .Net

[den 2005]

Hi everybody,

I was redirected here from another forum. Installed in my pc is VS 2002
and C# 2005 Express Edition, VB.Net 2005 Express Edition and Web Developer
Express Edition and MS Office 2000. I like to know how can create an
application to interact with office application say an Excel application both
reading and writing data (create Excel app) programatically. What do I need
to install? Can you provide some sample codes or documents on how exactly to
do this? Thanks.

den2005

 

[Cindy Meister]

Hi Den

Glad you found this group

I don't have any of the Express editions installed, and I had to find a
machine I can put it on. I'm going to do this with you in VB.NET Express, as
that will be easiest for you since you can pretty much take the Office VBA
directly over. It's d/l right now, but it will take a while...

I'll follow-up here as soon as I have the interface in front of me so that I
can tell you where to do what.
-
-- Cindy

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?ZGVuIDIwMDU=?=,

I like to know how can create an
application to interact with office application say an Excel application both
reading and writing data (create Excel app) programatically. What do I need
to install? Can you provide some sample codes or documents on how exactly to
do this?

OK, start by creating a new Windows Application project in VB.NET Express.

In the Solution Explorer on the right, right-click top entry and choose "Add
REference". Click the COM tab in the dialog box that opens and locate the entry
for Microsoft Excel (on your machine it would be Microsoft Excel 9.0).

Since you won't have any PIAs installed for Office 2000 this may take quite a
bit of time. Visual Studio will be generating a set of IAs for you. When it's
finished, right-click "My Project" and choose "Open". Click the "References" tab
on the left and look at the listing. Do you see entries of the type "COM"? What
are they?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[den 2005]

Thanks. Cindy.

Under Type "COM", there are 3 of them,
1. Microsoft Excel 9.0 Object Library - Ver 1.3.0.0
2. Microsoft Office 9.0 Object Library - Ver 2.1.0.0
3. Microsoft Visual Basic for Applications Extensibility 5.3 - ver 5.3.0.0

Dennis

 

[den 2005]

HI,

How would you commit changes made in textbox controls or datagrid cells
to the excel application? How eactly to get and set values of these cells of
loaded Excel application? I am experimenting on Worksheet.Cell(x,y)...Thanks.

Dennis

 

[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?? Thanks.

***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

 

[Cindy M -WordMVP-]

Hi Dennis,

It would be helpful if you explained HOW it didn't work. Also, it's a VERY good
idea to put all calls to a COM object in try...catch blocks. Otherwise, it's not
easy to trouble-shoot.

As a general rule, to assign values to a range in Excel:
Dim rng as Excel.Range = sheet.Range("A1")
rng.Value2 = "abc"

Note the use of Value2

At this point, it might be worthwhile to go to the excel.programming newsgroup,
where you'll find the Excel experts. If you want to do a "data dump", as you
propose, there are more efficient ways than to go "cell-to-cell". Excel has some
special data handling methods, but I'm not an expert on them.

Tip: to get a useful response from that seeped-in-vba group, it's best to
formulate the question in more general (rather than .NET) terms to begin with.

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?? Thanks.

***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

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[den 2005]

Thanks for info, Cindy.
I post the problm in another forum.


Dennis