VBA – Working with external files in Excel

Every VBA programmer needs to know how to extract data from and to separate files. This is a really valuable knowledge, if you are building MS Office app, which is to be used by a third-party.

In the current article I will present code for the following three occasions:

• Getting the data from the external file
• Setting the data to the external file
• Deleting and creating the external file (e.g. making a kind of restart)
  • Bonus – a VBA function, called fileExists, checking whether the file in the subroutines exists.

Here comes the code:

Sub GetDataFromFile()

    Dim sTxt    As String
    Dim sText   As String
    Dim sPath   As String

    sPath = "C:\...\New Text Document.txt"

    If Not fileExists(sPath) Then Exit Sub
 
    Open sPath For Input As #1

    Do Until EOF(1)
        Line Input #1, sTxt
        sText = sText & sTxt & vbLf
    Loop

    Close
    If Not CBool(Len(sText)) Then
        MsgBox "No data inside"
        Exit Sub
    End If
    
    sText = Left(sText, Len(sText) - 1)
    Cells(2, 1) = sText

End Sub

Sub SaveCellValueToFile()
    
    Dim sPath   As String
    sPath = "C:\...\New Text Document.txt"
    
    If Not fileExists(sPath) Then Exit Sub
    
    Open sPath For Append As #1
    Print #1, Cells(1, 1)
    Close #1

End Sub

Sub DeleteAndCreate()

    Dim sPath       As String
    Dim intFactor   As Integer
    
    sPath = "C:\...\New Text Document.txt"
    If Not fileExists(sPath) Then Exit Sub
    
    Kill sPath
    
    intFactor = 1 'FreeFile
    Open sPath For Output Access Write As #intFactor
    Close #intFactor

End Sub

Function fileExists(sPath As String) As Boolean

    If Not Dir(sPath) = "" Then
        fileExists = True
    Else
        MsgBox "File was not found."
    End If
    
End Function

Quite a lot of code, but it is somehow fun to play with it a bit – you may enter the data from the A1 cell a few times with the sub SaveCellValueToFile and then display it in cell A2 with the GetDataFromFile. In the same time, the *.txt file is updated. The function is added just to save place from the code. Take a look at the way it is used in the sub routines – the usage of “Not” avoids writing 2-3 lines of code more.

Enjoy it! 🙂