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.

External files and outside temperature do not have a lot of common.
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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 |
1 2 3 4 5 6 7 8 9 10 11 12 |
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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |
1 2 3 4 5 6 7 8 9 |
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! 🙂