Create application to extract data from excel sheet

[Philip Wagenaar]

I have an excel sheet that has several lines. Each line is an order. Example:

1 Paper 10
2 Pencils 20
etc...

When the excel sheet is filled out I want the user to press a button and
then the data from orderlines is extracted and placed in a seperate file.
This seperate file and the original excel sheet should be placed in a temp
dir and then an executeble is going to be started to import both into another
application.

My question is:

Should I create a solution based on Visual Studio Tools for Office 2005 or
is creating an Add-In for excel the best way to go?

[Peter Huang [MSFT]]

Hi Philip,

WE can use ado.net to retrieve the data from excel as a datasource.
316934 How To Use ADO.NET to Retrieve and Modify Records in an Excel
Workbook With Visual Basic .NET
http://support.microsoft.com/default...b;EN-US;316934
Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Philip Wagenaar]

Thank you for your quick response.

The KB article you referred to is very helpful. However this is a windows
forms application. I want the user to use the functionality from within Excel.

What is the best way to go? Add-In for excel or Visual Studio Tools for
Office 2005?

""Peter Huang" [MSFT]" wrote:

Hi Philip,

WE can use ado.net to retrieve the data from excel as a datasource.
316934 How To Use ADO.NET to Retrieve and Modify Records in an Excel
Workbook With Visual Basic .NET
http://support.microsoft.com/default...b;EN-US;316934
Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[zacks]

That uses the Microsoft Jet OLEDB driver. Isn't the current recommended
way to do it is with the Microsoft Excel Driver?

[Philip Wagenaar]

You need to specify the xls file in the connectionstring. But I do not know
how the xls file is saved or where. Anyway around this?

"za***@construction-imaging.com" wrote:

That uses the Microsoft Jet OLEDB driver. Isn't the current recommended
way to do it is with the Microsoft Excel Driver?

[Peter Huang [MSFT]]

Hi Philip,

To access to the Excel data, we have two approaches.
1. Using the Microsoft Jet engine, So we can use the ADO in Legacy
application(vb6,vba) or ADO.NET in .NET application.
2. Using Excel Object Modal which is somewhat less efficient for block of
data than above.

But either methods, we need to know where the xls file is, because we need
to load the file so that we can retrieve the data.
If I have any misunderstanding, can you describe your scenario more
detailed?

Thanks!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Homer J Simpson]

"Philip Wagenaar" <ph*************@online.nospam> wrote in message
news:33**********************************@microsof t.com...

I have an excel sheet that has several lines. Each line is an order.
Example:

1 Paper 10
2 Pencils 20
etc...

When the excel sheet is filled out I want the user to press a button and
then the data from orderlines is extracted and placed in a seperate file.

What sort of file?

[Philip Wagenaar]

flat text file. Tab delimited

"Homer J Simpson" wrote:

"Philip Wagenaar" <ph*************@online.nospam> wrote in message
news:33**********************************@microsof t.com...

I have an excel sheet that has several lines. Each line is an order.
Example:

1 Paper 10
2 Pencils 20
etc...

When the excel sheet is filled out I want the user to press a button and
then the data from orderlines is extracted and placed in a seperate file.

What sort of file?

[Peter Huang [MSFT]]

Hi Philip,

Have you tried my suggestion?
If you means a plain text which is Tab delimited, you may try to import it
into excel as a xls file and then use the ADO or ADO.NET to retrieve the
data.

If you still have any concern, please feel free to post here.
Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Homer J Simpson]

"Philip Wagenaar" <ph*************@online.nospam> wrote in message
news:01**********************************@microsof t.com...

flat text file. Tab delimited

I can't imagine doing anything but writing this in Excel VBA. Piece of cake
and exactly what it is meant for.

Just record a macro of doing this by hand then go in and tweak it to make it
more general.

[Philip Wagenaar]

I do not want to use VBA. I want to program this is vb.net. Maybe this is
very simple, but I want to start simple ;-)

"Homer J Simpson" wrote:

"Philip Wagenaar" <ph*************@online.nospam> wrote in message
news:01**********************************@microsof t.com...

flat text file. Tab delimited

I can't imagine doing anything but writing this in Excel VBA. Piece of cake
and exactly what it is meant for.

Just record a macro of doing this by hand then go in and tweak it to make it
more general.

[Philip Wagenaar]

I have the following code behind a button I placed on the excel sheet:

Private Sub btnOrder_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnOrder.Click

Globals.ThisWorkbook.Save()

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=" & Globals.ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim queryString As String = "select * from [OrderLines]"
Using connection As New OleDbConnection(connectionString)
Dim command As New OleDbCommand(queryString, connection)

connection.Open()

Dim reader As OleDbDataReader = command.ExecuteReader()
While reader.Read()
MsgBox(reader(0).ToString())
End While
reader.Close()
End Using

Only when I place data in the named range the message boxes that pop up are
always empty :-(

End Sub

""Peter Huang" [MSFT]" wrote:

Hi Philip,

Have you tried my suggestion?
If you means a plain text which is Tab delimited, you may try to import it
into excel as a xls file and then use the ADO or ADO.NET to retrieve the
data.

If you still have any concern, please feel free to post here.
Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Peter Huang [MSFT]]

Hi Philip,

Based on my test, it seems that I can not reproduce the problem.
You may have a try.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
Dim rg As Excel.Range = NamedRange2.Cells(1, 1)
rg.Value = "1"
Catch ex As Exception
MsgBox(ex.ToString())
End Try
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Globals.ThisWorkbook.Save()

Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Globals.ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim queryString As String = "select * from [Sheet1$]"
Using connection As New OleDb.OleDbConnection(connectionString)
Dim command As New OleDb.OleDbCommand(queryString, connection)

connection.Open()

Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
While reader.Read()
MsgBox(reader(0).ToString())
End While
reader.Close()
End Using
End Sub

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Philip Wagenaar]

How can I make this work with a named range?

""Peter Huang" [MSFT]" wrote:

Hi Philip,

Based on my test, it seems that I can not reproduce the problem.
You may have a try.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
Dim rg As Excel.Range = NamedRange2.Cells(1, 1)
rg.Value = "1"
Catch ex As Exception
MsgBox(ex.ToString())
End Try
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Globals.ThisWorkbook.Save()

Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Globals.ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim queryString As String = "select * from [Sheet1$]"
Using connection As New OleDb.OleDbConnection(connectionString)
Dim command As New OleDb.OleDbCommand(queryString, connection)

connection.Open()

Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
While reader.Read()
MsgBox(reader(0).ToString())
End While
reader.Close()
End Using
End Sub

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Philip Wagenaar]

aaaah!!!

I was starting my project from visual studio debug/run and I could not get
the data I entered in a cell after compile. I was able to get it after I
started the excel sheet outside visual studio

"Philip Wagenaar" wrote:

How can I make this work with a named range?

""Peter Huang" [MSFT]" wrote:

Hi Philip,

Based on my test, it seems that I can not reproduce the problem.
You may have a try.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
Dim rg As Excel.Range = NamedRange2.Cells(1, 1)
rg.Value = "1"
Catch ex As Exception
MsgBox(ex.ToString())
End Try
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Globals.ThisWorkbook.Save()

Dim connectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Globals.ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim queryString As String = "select * from [Sheet1$]"
Using connection As New OleDb.OleDbConnection(connectionString)
Dim command As New OleDb.OleDbCommand(queryString, connection)

connection.Open()

Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
While reader.Read()
MsgBox(reader(0).ToString())
End While
reader.Close()
End Using
End Sub

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Peter Huang [MSFT]]

Hi Philip,

From your description, I understand that when you input a value in the
cell(e.g. cell(2,2)), after you press F5 to run the VSTO project in Debug
mode, you will find that the value in Cell(2,2) is empty.

If I have any misunderstanding, please feel free to post here.

Based on my test, I can not reproduce the problem.

So far I suggest you create a new VSTO project and input a value in
Cell(2,2) and then press F5 to run the Project to see if the value in
cell(2,2)exists.

Also due to the IDE will try to load many symbols for VSTO projects, after
you press F5, please wait until the cursor is not busy.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Philip Wagenaar]

Is it possible but that the location where Globals.ThisWorkbook.Save saves
its file is diffrent from Globals.ThisWorkbook.FullName in Visual Studio
debug mode?

The code I am using:

Private Sub btn_bestellen_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btn_bestellen.Click
Try
Globals.ThisWorkbook.Save()
Catch ex As Exception
MsgBox("Some error message in dutch")
Exit Sub
End Try

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=" & Globals.ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""

Dim ExcelConnection As New
System.Data.OleDb.OleDbConnection(connectionString )
ExcelConnection.Open()
Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from " &
orderRange, ExcelConnection)
Dim ds As New DataSet
da.Fill(ds, "Order")
ds.WriteXml("c:\test2.xml")
ExcelConnection.Close()

End Sub

""Peter Huang" [MSFT]" wrote:

Hi Philip,

From your description, I understand that when you input a value in the
cell(e.g. cell(2,2)), after you press F5 to run the VSTO project in Debug
mode, you will find that the value in Cell(2,2) is empty.

If I have any misunderstanding, please feel free to post here.

Based on my test, I can not reproduce the problem.

So far I suggest you create a new VSTO project and input a value in
Cell(2,2) and then press F5 to run the Project to see if the value in
cell(2,2)exists.

Also due to the IDE will try to load many symbols for VSTO projects, after
you press F5, please wait until the cursor is not busy.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[Peter Huang [MSFT]]

Hi Philip,

Based on my test, Globals.ThisWorkbook.Save() will save the change back
into the original path.
e.g. C:\workbook.xls

Then after changed, the Globals.ThisWorkbook.Save() will save the change to
C:\workbook.xls

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.