Create application to extract data from excel sheet

6 Mar 2006 8:07 AM

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?

6 Mar 2006 9:08 AM

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

6 Mar 2006 10:26 AM

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?

Show quoteHide quote

""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.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.
>
>

6 Mar 2006 1:38 PM

zacks

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

6 Mar 2006 3:17 PM

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?

Show quoteHide quote

"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?
>
>

7 Mar 2006 2:52 AM

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.

8 Mar 2006 10:11 PM

Homer J Simpson

"Philip Wagenaar" <philip.wagenaar@online.nospam> wrote in message
news:336E55E1-1286-49EF-A871-0263A8D2DB0D@microsoft.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?

9 Mar 2006 8:13 AM

Philip Wagenaar

flat text file. Tab delimited

Show quoteHide quote

"Homer J Simpson" wrote:

>
> "Philip Wagenaar" <philip.wagenaar@online.nospam> wrote in message
> news:336E55E1-1286-49EF-A871-0263A8D2DB0D@microsoft.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?
>
>
>
>

9 Mar 2006 9:33 AM

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.

10 Mar 2006 8:33 AM

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

Show quoteHide quote

""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.
>
>

13 Mar 2006 3:31 AM

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.

14 Mar 2006 9:53 AM

Philip Wagenaar

How can I make this work with a named range?

Show quoteHide quote

""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.
>
>

14 Mar 2006 10:44 AM

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

Show quoteHide quote

"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.
> >
> >

15 Mar 2006 2:24 AM

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.

15 Mar 2006 8:32 AM

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

Show quoteHide quote

""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.
>
>

16 Mar 2006 7:20 AM

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.

9 Mar 2006 5:08 PM

Homer J Simpson

"Philip Wagenaar" <philip.wagenaar@online.nospam> wrote in message
news:01BCE10D-6CCB-4B51-90C1-4BD836CEAE95@microsoft.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.

10 Mar 2006 8:15 AM

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 ;-)

Show quoteHide quote

"Homer J Simpson" wrote:

>
> "Philip Wagenaar" <philip.wagenaar@online.nospam> wrote in message
> news:01BCE10D-6CCB-4B51-90C1-4BD836CEAE95@microsoft.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.
>
>
>
>