Learn how to a build a cloud-first strategyRegister Now


How to read from a exel Document in Vb 2010

Posted on 2011-10-27
Medium Priority
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
  • 3
  • 2
LVL 13

Expert Comment

ID: 37042822

You can use Excel COM interop, here is the sample you can try, http://www.dotnetperls.com/excel-vbnet

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

ID: 37042846
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

ID: 37042893
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

khairil earned 2000 total points
ID: 37043351
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

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month20 days, 14 hours left to enroll

810 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