Tag Archives: file

VBA Read file in Excel (txt, xml, csv, binary)

1 Star2 Stars3 Stars4 Stars5 Stars (10 votes, average: 4.60 out of 5)
Loading...

You can VBA Read file binary or text data using using the Open operator. For CSV files and other structured data schemas you can use the Jet.OLEDB driver or Microsoft Queries.

Text/binary files are common ways of storing data as opposed to databases or regular Excel files. Looking at various resources I missed a single resource which would demonstrate the various methods for PROPERLY reading files in VBA.

It is important to remember that you shouldn’t read all files using the same approach. Be aware of the structure of the file. If it is a structured CSV use the ADODB connection, if you need to read only a couple of rows read the file row by row or by chunks, else read the whole file. If you want performance – always select the right approach.

Reading text files in VBA

VBA Read text files (line by line)

To read an entire text file line by line use the code below.

VBA Read text files (read whole file)

To read an entire text file in one go (not line by line) use the code below.a

VBA Read specific number of lines from a text file

In cases when you want to read specific lines from a text file you can adapt the line by line read code as below. It allows you to read a certain number of lines (noLines) from a text file from a specific start line number (sLine). If you set noLines to 0 it will read all lines till end of the file.

Reading CSV files in VBA

Reading CSV files (read whole file and process each row)

Reading a text file line by line into a string:

Reading CSV files (whole file to Worksheet)

Read whole file to an Excel Worksheet:

To refresh the CSV upload (in case the CSV was updated) simply run:

Reading binary files in VBA

With Binary files often you will be using objects which are not of fixed byte length like Integers. For example you would want to read Strings from binary files together with other data types. In such cases use the Type object data type when writing to a file. Learn more here.
Below a simple example of reading a file to which a Type data type was saved to, including an Integer and String.

Reading XML files in VBA

Functions needed to read files in VBA

Function Description
FreeFile Get next free file number available for the Open statement / FileOpen function. Using this function is important especially when operating on multiple files simultaneously.
More info here.
BOF(fileNumber) Returns true if you are at the beginning of the file described by the file number.
More info here.
EOF(fileNumber) Returns true if you have reached the end of the file described by the file number. More info here.
Loc(fileNumber) Returns the current read/write position within an open file. More info here.
LOF(fileNumber) Returns the size in bytes of the file represented by the file number. More info here.

VBA Read File Summary

Reading files in VBA is not hard and requires just a few lines of code usually. It is, however, important to use the appropriate approach to read a file in VBA. Not all files need to be read line-by-line which is usually inefficient. Equally so you need not always read the entire file if you just need the first few / last rows. Working with XML files is also not a challenge if you read through my post on how to work with XML files.

VBA Download Files – download files in Excel using VBA

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.71 out of 5)
Loading...

Today’s post will touch on something most analysts and VBA developers might find useful: downloading files using vba from the Web (VBA Download). Excel VBA again proves to be a versatile tool for Analytics – in this case for extracting and storing data. Downloading files can be a useful way to save data extracted from the web and to build your own data repository, or simply to make a backup of any data downloaded from the Web.

Looking to download / upload files from FTP with VBA? Read my blog post on VBA FTP

Excel can be a great tool to harness the data of the Internet. If you are more into the subject of Web Scraping I encourage you to take a look at the Excel Scrape HTML Add-In which let’s you easily download HTML content from most Web Pages without resorting to VBA. In case browser simulation is needed read on my Simple class for using IE automation in VBA.

VBA download file macro

In some cases you will need to download large files (not text/HTML) and will want to be able to control the process of downloading the data e.g. might want to interrupt the process, enable the user to interact with Excel (DoEvent) etc. In these cases the above procedure won’t do. The procedure below may however prove more efficient as it will download the file in 128 byte chunks of data instead of a single stream.

Private Const INTERNET_FLAG_NO_CACHE_WRITE = &H4000000
Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal lpszAgent As String, ByVal dwAccessType As Long, ByVal lpszProxyName As String, ByVal lpszProxyBypass As String, ByVal dwFlags As Long) As Long
Private Declare Function InternetReadBinaryFile Lib "wininet.dll" Alias "InternetReadFile" (ByVal hfile As Long, ByRef bytearray_firstelement As Byte, ByVal lNumBytesToRead As Long, ByRef lNumberOfBytesRead As Long) As Integer
Private Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal sUrl As String, ByVal sHeaders As String, ByVal lHeadersLength As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer

Sub DownloadFile(sUrl As String, filePath As String, Optional overWriteFile As Boolean)
  Dim hInternet, hSession, lngDataReturned As Long, sBuffer() As Byte, totalRead As Long
  Const bufSize = 128
  ReDim sBuffer(bufSize)
  hSession = InternetOpen("", 0, vbNullString, vbNullString, 0)
  If hSession Then hInternet = InternetOpenUrl(hSession, sUrl, vbNullString, 0, INTERNET_FLAG_NO_CACHE_WRITE, 0)
  Set oStream = CreateObject("ADODB.Stream")
  oStream.Open
  oStream.Type = 1

  If hInternet Then
    iReadFileResult = InternetReadBinaryFile(hInternet, sBuffer(0), UBound(sBuffer) - LBound(sBuffer), lngDataReturned)
    ReDim Preserve sBuffer(lngDataReturned - 1)
    oStream.Write sBuffer
    ReDim sBuffer(bufSize)
    totalRead = totalRead + lngDataReturned
    Application.StatusBar = "Downloading file. " & CLng(totalRead / 1024) & " KB downloaded"
    DoEvents

    Do While lngDataReturned <> 0
      iReadFileResult = InternetReadBinaryFile(hInternet, sBuffer(0), UBound(sBuffer) - LBound(sBuffer), lngDataReturned)
      If lngDataReturned = 0 Then Exit Do

      ReDim Preserve sBuffer(lngDataReturned - 1)
      oStream.Write sBuffer
      ReDim sBuffer(bufSize)
      totalRead = totalRead + lngDataReturned
      Application.StatusBar = "Downloading file. " & CLng(totalRead / 1024) & " KB downloaded"
      DoEvents
    Loop

    Application.StatusBar = "Download complete"
    oStream.SaveToFile filePath, IIf(overWriteFile, 2, 1)
    oStream.Close
  End If
  Call InternetCloseHandle(hInternet)
End Sub

See effect below when executing macro:

vba download file
Status Bar: Downloading a large binary file

How it works

The procedure will download the binary file in 128 byte chunks while saving the contents to the data stream and flushing it into the file once completed. In between the chunks you can call “DoEvents” to enable user interaction, inform the user of the download progress Application.StatusBar or do other thing including interrupting the process and closing the connection. In case you want to do a proper Progress Bar and inform the user of the % progress you may want to leverage this solution.

Example

Let us use the procedure above to download a simple text file from AnalystCave.com:

Sub TestDownload()
    DownloadFile "https://analystcave.com/junk.txt", ThisWorkbook.Path & "\junk.txt", True
End Sub

Download the example

Provide your email to get the link to a file with an example usage of this script:

Reading / writing files in VBA

So you know how to download files using VBA. The next step is learning how to read files using VBA.

Versioning Excel files with Excel VBA

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.25 out of 5)
Loading...

Usually when developing Excel solutions you want to version you file often to prevent data loss due to the application crashing etc. You will probably also want to keep the older versions of you files to be able to go back and recover any previously working code. When you do this once or twice in a while this is no issue. But when you are making significant changes in a short amount of time saving new versions is a time-consuming task. Excel versioning is therefore something many deem useful.

That’s why I made myself a very simple VBA method for automatically saving the current Excel xlsm file as a new version while keeping the previous versions of the file. The macro increments the current file version. It is best to set a keyboard shortcut to the macro to save time.

Excel VBA Versioning
Excel VBA Versioning

Versioning Excel Code

The following code will save the ActiveWorkbook as a new Workbook while appending the version number by 1 in the format “_vXXX” where XXX is the version number. The versioning macro will maintain the file extension.

Sub SaveNewVersion()
    Dim fileName As String, index As Long, ext As String
    arr = Split(ActiveWorkbook.Name, ".")
    ext = arr(UBound(arr))
    If InStr(ActiveWorkbook.Name, "_v") = 0 Then
        
        fileName = ActiveWorkbook.Path & "" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & "_v1." & ext
        ActiveWorkbook.SaveAs (fileName)
    Else
        index = CInt(Split(Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - InStr(ActiveWorkbook.Name, "_v") - 1), ".")(0))
        index = index + 1
        fileName = ActiveWorkbook.Path & "" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, "_v") - 1) & "_v" & index & "." & ext
    End If
    ActiveWorkbook.SaveAs (fileName)
End Sub

Download

You can also download the file as a bas file:


The module sets the macro as a CTRL+SHIFT+S shortcut as having this line of code:

Attribute SaveNewVersion.VB_ProcData.VB_Invoke_Func = "Sn14"

Setting up the Versioning Excel Macro

Keyboard shortcut

As mentioned above the macro in the download section is setup by default for CTRL+SHIFT+S shortcut. However, in case you want to change the shortcut. Simply go to the DEVELOPER ribbon and select Macros. Next select the SaveNewVersion macro and click Options.... This will prompt you for a new keyboard shortcut.

Set Excel Macro Shortcut
Set Excel Macro Shortcut

Quick Access Toolbar

Why remember a keyboard shortcut when you can add a neat icon to your Quick Access Toolbar in Excel.

Open the Quick Access Toolbar

Go to File, Options and open the Quick Access Toolbar.

Add versioning VBA macro to the Quick Access Toolbar

Proceed as shown below to Add the SaveNewVersion macro to the Quick Access Toolbar:

Quick Access Toolbar: Add macro
Quick Access Toolbar: Add macro

Optional: Modify the icon

Why stick to a default macro icon when we can make it more pleasant to the eye? Click on the SaveNewVersion macro and hit Modify. Next select a new icon from this window:

Save New Version: Select a new icon
Save New Version: Select a new icon

This is the final effect:
Quick Access Toolbar: Versioning Excel
Quick Access Toolbar: Versioning Excel

Simply hit the icon to save a new version of your Excel file! Remember to save the file in XLS/XLSM/ or XLSB format.

Installing as an Excel AddIn

The above will add the versioning feature to all your Workbooks as long as your Excel file with the SaveNewVersion macro is not moved or deleted!. I strongly recommend that instead you include this file into the AddIns folder before configuring this shortcut.

Save the XLSM file as AddIn

First save the file in XLA or XLAM format, as an Excel AddIn.

Save the file in Microsoft Excel AddIn directory

Save the file in the following directory for it to open automatically on startup:

C:/Users/USERNAME/AppData/Roaming/Microsoft/AddIns