The following VBA function enables you to read files, for instance a text file, into memory (pass the read file’s content to a variable for instance) to further use within your application.
Instead of reading each line, one by one, the following approach reads the entire file in one go. So this should be faster than other methods.
'---------------------------------------------------------------------------------------
' Procedure : ReadFile
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Read (text) file all into memory in a single shot rather than line by line
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile : Full path and filename of the file that is to be read
'
' Usage:
' ~~~~~~
' MyTxt = ReadFile("c:\tmp\test.txt")
' MyTxt = ReadFile("c:\tmp\test.sql")
' MyTxt = ReadFile("c:\tmp\test.csv")
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2012-05-14 Initial Public Release
' 2 2021-09-25 Updated Header
' Updated Error Handler
' Code Cleanup
'---------------------------------------------------------------------------------------
Function ReadFile(ByVal sFile As String) As String
On Error GoTo Error_Handler
Dim iFileNumber As Integer
Dim sFileContent As String
iFileNumber = FreeFile
Open sFile For Binary Access Read As iFileNumber
sFileContent = Space(LOF(iFileNumber))
Get #iFileNumber, , sFileContent
Close iFileNumber
ReadFile = sFileContent
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ReadFile" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
The above can be used with any text file type, so: txt, dat, csv, ini, … as illustrated in the Usage section of the function header.
I have done further improvements to this function and further testing, so be sure to read all about it by referring to:
The Best Approach To Reading Files In VBA
Years ago, I develop a simple function to read a ‘text’ file using VBA so I could pass the ‘text’ file’s content to a VBA variable to then be able to work with. You can consult that article by referring to: More recently, having over the years used a variety of different ways to read files, I was curious if one approach was better (from a performance standpoint)?
