Binaryworld - How to read data from Excel file ... [ VB -> Excel ]

Many times we have requirements to load data from CSV file, text file or Excel file...

In this article I will show a sample code which reads a specified rows and columns from excel file.

Click here to copy the following block

Private Sub Command1_Click()
  ImportExcelToSQL "c:\temp\diamond_upload.xls", 0, 20, True
End Sub

'//filepath : excel file path which you want to load
'//maxrows_to_read : How many rows to read from excel file
'//maxcols_to_read : How many cols to read from excel file
'//exitonblankrow : If true then when first cell is blank read is stopped

Sub ImportExcelToSQL(filepath, maxrows_to_read, maxcols_to_read, exitonblankrow)
  '//Use for Late Binding --//Intelisense wont be available at design time
  Dim objExcel 'As Excel.Application

  Dim r, c, cellVal, sLine
  bolTemp = False

  Set objExcel = CreateObject("Excel.Application")
  objExcel.Workbooks.Open filepath
  'objExcel.Workbooks.Open filepath & "\" & "test.xls"

  objExcel.DisplayAlerts = False
  objExcel.Sheets(1).Select
  objExcel.Sheets(1).Activate

  If maxcols_to_read <= 0 Then
    maxcols_to_read = objExcel.Columns.Count
  End If

  If maxrows_to_read <= 0 Then
    maxrows_to_read = objExcel.Rows.Count
  End If

  For r = 1 To maxrows_to_read
    '//if first cell of N'th row ="" means end of records
    '//this this logic according to your requirements
    If exitonblankrow <> 0 And objExcel.Cells(r, 1) = "" Then
      Exit For
    End If

    For c = 1 To maxcols_to_read
      cellVal = objExcel.Cells(r, c)
      '//TODO : Write your code to dump values to database
      sLine = sLine & ", " & cellVal
    Next

    sLine = sLine & vbCrLf
    '//TODO : Write your code to dump values to database

    WriteDebug sLine

    Me.Caption = r
    DoEvents
  Next

  objExcel.Workbooks.Close
  objExcel.DisplayAlerts = True
  objExcel.Quit
  Set objExcel = Nothing
End Sub

Sub WriteDebug(sLine)
  'txtLog = txtLog & vbCrLf
  Debug.Print sLine
End Sub