by Duncan MacFarlane (1 Submission)
Category: Microsoft Office Apps/VBA
Compatability: Visual Basic 3.0
Difficulty: Intermediate
Date Added: Wed 3rd February 2021
Rating: 
(17 Votes)
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.
VBcoders
'-------------------------------------------------
'
'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
