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

RE: Create application to extract data from excel sheet

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.aspx?scid=kb;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]

RE: Create application to extract data from excel sheet

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:
[color=blue]
> 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.
>
>[/color]

[zacks@construction-imaging.com]

Re: Create application to extract data from excel sheet

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

[Philip Wagenaar]

Re: Create application to extract data from excel sheet

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

"zacks@construc tion-imaging.com" wrote:
[color=blue]
> That uses the Microsoft Jet OLEDB driver. Isn't the current recommended
> way to do it is with the Microsoft Excel Driver?
>
>[/color]

[Peter Huang [MSFT]]

Re: Create application to extract data from excel sheet

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 misunderstandin g, 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]

Re: Create application to extract data from excel sheet


"Philip Wagenaar" <philip.wagenaa r@online.nospam > wrote in message
news:336E55E1-1286-49EF-A871-0263A8D2DB0D@mi crosoft.com...[color=blue]
>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.[/color]

What sort of file?

[Philip Wagenaar]

Re: Create application to extract data from excel sheet

flat text file. Tab delimited

"Homer J Simpson" wrote:
[color=blue]
>
> "Philip Wagenaar" <philip.wagenaa r@online.nospam > wrote in message
> news:336E55E1-1286-49EF-A871-0263A8D2DB0D@mi crosoft.com...[color=green]
> >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.[/color]
>
> What sort of file?
>
>
>
>[/color]

[Peter Huang [MSFT]]

Re: Create application to extract data from excel sheet

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]

Re: Create application to extract data from excel sheet


"Philip Wagenaar" <philip.wagenaa r@online.nospam > wrote in message
news:01BCE10D-6CCB-4B51-90C1-4BD836CEAE95@mi crosoft.com...
[color=blue]
> flat text file. Tab delimited[/color]

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]

Re: Create application to extract data from excel sheet

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:
[color=blue]
>
> "Philip Wagenaar" <philip.wagenaa r@online.nospam > wrote in message
> news:01BCE10D-6CCB-4B51-90C1-4BD836CEAE95@mi crosoft.com...
>[color=green]
> > flat text file. Tab delimited[/color]
>
> 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.
>
>
>
>[/color]

[Philip Wagenaar]

Re: Create application to extract data from excel sheet

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.EventArg s) Handles btnOrder.Click

Globals.ThisWor kbook.Save()

Dim connectionStrin g As String = "Provider=Micro soft.Jet.OLEDB. 4.0;"
& _
"Data Source=" & Globals.ThisWor kbook.FullName & ";" & _
"Extended Properties=""Ex cel 8.0;HDR=NO"""
Dim queryString As String = "select * from [OrderLines]"
Using connection As New OleDbConnection (connectionStri ng)
Dim command As New OleDbCommand(qu eryString, connection)

connection.Open ()

Dim reader As OleDbDataReader = command.Execute Reader()
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:
[color=blue]
> 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.
>
>[/color]

[Peter Huang [MSFT]]

Re: Create application to extract data from excel sheet

Hi Philip,

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

Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Try
Dim rg As Excel.Range = NamedRange2.Cel ls(1, 1)
rg.Value = "1"
Catch ex As Exception
MsgBox(ex.ToStr ing())
End Try
End Sub

Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button2.Click
Globals.ThisWor kbook.Save()

Dim connectionStrin g As String =
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & Globals.ThisWor kbook.FullName & ";" & _
"Extended Properties=""Ex cel 8.0;HDR=NO"""
Dim queryString As String = "select * from [Sheet1$]"
Using connection As New OleDb.OleDbConn ection(connecti onString)
Dim command As New OleDb.OleDbComm and(queryString , connection)

connection.Open ()

Dim reader As OleDb.OleDbData Reader = command.Execute Reader()
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]

Re: Create application to extract data from excel sheet

How can I make this work with a named range?

""Peter Huang" [MSFT]" wrote:
[color=blue]
> Hi Philip,
>
> Based on my test, it seems that I can not reproduce the problem.
> You may have a try.
>
> Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
> System.EventArg s) Handles Button1.Click
> Try
> Dim rg As Excel.Range = NamedRange2.Cel ls(1, 1)
> rg.Value = "1"
> Catch ex As Exception
> MsgBox(ex.ToStr ing())
> End Try
> End Sub
>
> Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As
> System.EventArg s) Handles Button2.Click
> Globals.ThisWor kbook.Save()
>
> Dim connectionStrin g As String =
> "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=" & Globals.ThisWor kbook.FullName & ";" & _
> "Extended Properties=""Ex cel 8.0;HDR=NO"""
> Dim queryString As String = "select * from [Sheet1$]"
> Using connection As New OleDb.OleDbConn ection(connecti onString)
> Dim command As New OleDb.OleDbComm and(queryString , connection)
>
> connection.Open ()
>
> Dim reader As OleDb.OleDbData Reader = command.Execute Reader()
> 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.
>
>[/color]

[Philip Wagenaar]

Re: Create application to extract data from excel sheet

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:
[color=blue]
> How can I make this work with a named range?
>
> ""Peter Huang" [MSFT]" wrote:
>[color=green]
> > Hi Philip,
> >
> > Based on my test, it seems that I can not reproduce the problem.
> > You may have a try.
> >
> > Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
> > System.EventArg s) Handles Button1.Click
> > Try
> > Dim rg As Excel.Range = NamedRange2.Cel ls(1, 1)
> > rg.Value = "1"
> > Catch ex As Exception
> > MsgBox(ex.ToStr ing())
> > End Try
> > End Sub
> >
> > Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As
> > System.EventArg s) Handles Button2.Click
> > Globals.ThisWor kbook.Save()
> >
> > Dim connectionStrin g As String =
> > "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> > "Data Source=" & Globals.ThisWor kbook.FullName & ";" & _
> > "Extended Properties=""Ex cel 8.0;HDR=NO"""
> > Dim queryString As String = "select * from [Sheet1$]"
> > Using connection As New OleDb.OleDbConn ection(connecti onString)
> > Dim command As New OleDb.OleDbComm and(queryString , connection)
> >
> > connection.Open ()
> >
> > Dim reader As OleDb.OleDbData Reader = command.Execute Reader()
> > 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.
> >
> >[/color][/color]