Read Excel Spread Sheet

Submitted on: 1/10/2015 10:45:00 PM
By: Duncan MacFarlane (from psc cd)  
Level: Intermediate
User Rating: By 17 Users
Compatibility: VB 6.0
Views: 871
     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.

'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)
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()
End Function

Public Function exitExcel()
msExcelApp.Quit End Function

Public Function clearExcelObjects() Set msExcelWorksheet = Nothing
Set msExcelWorkbook = Nothing
Set msExcelApp = Nothing
End Function

Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)

Other User Comments

 There are no comments on this submission.

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)

To post feedback, first please login.