How to read from a exel Document in Vb 2010

Posted on 2011-10-27
Last Modified: 2012-05-12
Hi all what i need to  is loop through the Excel Document starting from Row14 and going to EOF as looping i need to move this is a sql connection i have
Question by:awolarczuk
    LVL 13

    Expert Comment


    You can use Excel COM interop, here is the sample you can try,

    for looping of row 14, just replace the code below in the sample

    For j As Integer = 1 To bound0

    Open in new window


    For j As Integer = 14 To bound0

    Open in new window

    Of course you need to fix the code to suite your needs.

    Author Comment

    mate it ia row 14 and down thanks for getting back to me

    What is the best way to read it in to a array and then read it back to a SQL data base

    Author Comment

    ok i got that but i would like to pint the array i just created to a sql data base, how would i do this
    LVL 13

    Accepted Solution

    You mean that you like to insert the row into SQL database not printing.

    I have modified the code but NOT testing it, it is just try to give you idea how it can be done.

    Imports Microsoft.Office.Interop.Excel
    Imports System.Data.SqlClient
    Module Module1
        Sub Main()
    			Dim connetionString As String
    			Dim connection As SqlConnection
    			Dim adapter As New SqlDataAdapter
    			Dim sql As String
    			Dim column as string
    			' Create new Application.
    			Dim excel As Application = New Application
    			' Open Excel spreadsheet.
    			Dim w As Workbook = excel.Workbooks.Open("C:\file.xls")
    			connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
    			connection = New SqlConnection(connetionString)
    			' Loop over all sheets.
    			For i As Integer = 1 To w.Sheets.Count
    			    ' Get sheet.
    			    Dim sheet As Worksheet = w.Sheets(i)
    			    ' Get range.
    			    Dim r As Range = sheet.UsedRange
    			    ' Load all cells into 2d array.
    			    Dim array(,) As Object = r.Value(XlRangeValueDataType.xlRangeValueDefault)
    			    ' Scan the cells.
    			  If array IsNot Nothing Then
    					' Get bounds of the array.
    					Dim bound0 As Integer = array.GetUpperBound(0)
    					Dim bound1 As Integer = array.GetUpperBound(1)
    					' Loop over all elements.
    					For j As Integer = 1 To bound0
    						column =""
    					  For x As Integer = 1 To bound1
    							Select Case x
    								Case 1
    									column = "'" & array(j, x) & "'"
    								Case 2
    									column += "," & "'" & array(j, x) & "'"
    								Case 3
    									column += "," & "'" & array(j, x) & "'"
    							End Select
    				    sql = "INSERT INTO (" & column & ") VALUES (ID, ITEM1, ITEM2)"
    						adapter.InsertCommand = New SqlCommand(sql, connection)
    		    End If
    			' Close.
        End Sub
    End Module

    Open in new window

    LVL 13

    Expert Comment

    Of course you need to modify the SELECT CASE statement and INSERT INTO to suite your need.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now