Using Excel in VB.NET

[DRapper]

Hello,

How do I open an existing Excel file and display the information in a form using VB.NET

 

[jonbatts]

Check this out

How To Query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual Basic .NET

 

[lucyv]

Is it possible to use OleDbSchemaGuid.Columns to obtain all of the columns/fields in a excel worksheet? If so, what is the syntax? I've been researching for this but haven't found anything yet.

Thanks in advance.

-lucyv

 

[lucyv]

Never mind, I fixed my problem. OleDBSchemaGuid.Columns does work with MS Excel, I just had an error in my connectionstring.

pstrConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties=""Excel 8.0;HDR=[COLOR=red]Yes[/color];IMEX=1"""

I originally had HDR=No;

 

[Bvisual]

here is the code i have got but i only have one problem with it:
at the end the file wile be saved but then you get an anoying message "Do you want to save changes ..." and you have to answer "YES"

now the question is how to get rid of this messagebox????

for the rest is the code OK !

'SAVE DATA INTO existing EXEL FILE
        'Creating Excel application object
        Dim EXL As New Excel.Application
        'Checking if Excel object initiated succesfully or not
        If EXL Is Nothing Then
            MsgBox("Couldn't start Excel")
            Exit Function
        End If
        'Creating Excel Worksheet
        Dim WSheet As New Excel.Worksheet
        opening worksheet to excel workbooks
  
       
        WSheet = EXL.Workbooks.Open("[b]source[/b]").Worksheets.Item(1)

        'Writing values in Work Sheet
        With WSheet

            .....................

        End With
        Try
            'Saving .xls file with Test.xls name 
            Dim File2 As System.IO.File
            Dim PATH2 As String = "[b]source[/b]"

                       WSheet.SaveAs(PATH2)

        Catch
        End Try
        'setting up caption that "File Created"
        Me.Text = "File Created"
        'closing down workbook
        EXL.Workbooks.Close()

        EXL.Quit()

        While System.Runtime.InteropServices.Marshal.ReleaseComObject(EXL) <> 0
        End While

        EXL = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()


    End Function