Read Excel Spread Sheet

The purpose of the following code is to provide you with a series of prototype functions to open and retreive data from a MS Excel spread sheet. The following code should be inserted into a new module named, for example, "modReadExcel". Passing variables will set the Excel File Name to open, the active Excel Sheet, recover data (data is returned as a string variable), close and exit Excel and clear the memory. These Prototype function simplify the entire process and gives your program(s) less coding or what I refer to as Clutter.



This code provides you with the basics of opening and reading an excel spreadsheet. I will be updating it in the future with the more advanced features if and when I encounter them.

Original Author: Duncan MacFarlane

Code


'-------------------------------------------------
'

'Excel Spread Sheet Read Prototype Functions

'

'---------------------------------------------
'

' By Duncan MacFarlane

' MacFarlane System Solutions

' A Privately owned business operated
'  from personal residence

'

' Copyright MacFarlane System Solutions
'  2001

'

'---------------------------------------------
'

' The following functions simplify
'  the process of opening,

'  retrieving, closing, exiting

'  Excel and clearing the memory of
'  the excel objects.

'

'---------------------------------------------
'

' The Syntax of the following functions
'  are as follows:

'

'  excelFile([String - File Name Including Full Path])

'  Sets the current file to open

' excelPassword([String - Excel
'  Read Only Password], [String -
'  Excel Write Password]

'  if no password is used on the
'  file discard the use of this
'  function

' openExcelFile

'  No variables are passed, opens
'  file set by excelFile function

' setActiveSheet([Integer - Sheet
'  number of sheet to read from,
'  starting from 1]

'  Sets the active sheet to read
'  from

'  [String - Data input returned] =
' readExcel([Integer - Row],
'  [Integer - Column])

'  Reads the content of a cell and
'  returns the data to the calling
'  location

' closeExcelFile

'  Closes the active Excel File

' exitExcel

'  Exits MS Excel

' clearExcelObjects

'  Clear the memory of the Excel
'  Application objects

'---------------------------------------------



Dim excelFileName As String


Dim readPassword As String


Dim writePassword As String


Dim msExcelApp As Excel.Application


Dim msExcelWorkbook As Excel.Workbook


Dim msExcelWorksheet As Excel.Worksheet



Public Function excelFile(fileName As String)


  Let excelFileName = fileName


End Function



Public Function excelPassword(rdExcel As String, wtExcel As String)
  Let readPassword = rdExcel

  Let writePassword = rdExcel
End Function



Public Function openExcelFile()


  Set msExcelApp = GetObject("", "excel.application")


  msExcelApp.Visible = False


  If readPassword = "" And writePassword = "" Then


    Set msExcelWorkbook = Excel.Workbooks.Open(excelFileName)


  Else


    Set msExcelWorkbook = Excel.Workbooks.Open(excelFileName, , , , readPassword, writePassword)


  End If


End Function



Public Function setActiveSheet(excelSheet As Integer)


  Set msExcelWorksheet = msExcelWorkbook.Worksheets.Item(excelSheet)


End Function



Public Function readExcel(Row As Integer, Col As Integer) As String


  readExcel = msExcelWorksheet.Cells(Row, Col)
End Function



Public Function, closeExcelFile()


  msExcelWorkbook.Close


End Function



Public Function exitExcel()


  msExcelApp.Quit
End Function



Public Function clearExcelObjects()
  Set msExcelWorksheet = Nothing


  Set msExcelWorkbook = Nothing


  Set msExcelApp = Nothing


End Function