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.

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:

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! 🙂

Tagged with: ,