How Do I Quickly Tweak All Files in a Folder?


I recently got hooked on Tableau Public. It’s pretty awesome!

One of the downsides, however, is the need to find yourself some interesting “open” datasets to visualize.

As the number of prospective students at British universities has long been of interest to me, I thought I’d check out if UCAS had any publicly available data to crunch

Luckily for me, they did

Yet, the data format of those files was not what I would call an analyst delight – there were 6 extra rows on top of each dataset, which even the Data Interpreter in Tableau Public couldn’t handle

If I were to make a comprehensive analysis out of this data, I would have to spend at least an hour on data preparation – i.e. getting rid of the 6 extra lines from each of the 103 files

Good that I’ve got some VBA tricks up my sleeve! stillgotit

After using Power Query to generate a list of all the files saved in that folder, I fired up the following code, which would loop through every workbook on the list, open it, delete the first 6 rows and save it


Sub TweakAllFiles()
Dim Cell As Range
Dim path As String
Dim file As String
Dim fullfilepath As String
'written by Angelina Teneva, 2017
Application.DisplayAlerts = False
For Each Cell In ThisWorkbook.Worksheets("UCAS").Range("A2:A103")
file = Cell.Value
path = Cell.Offset(0, 6).Value
fullfilepath = path & file
If file Like "*2017*" Then
Workbooks.Open Filename:=fullfilepath, ReadOnly:=False, UpdateLinks:=False
With ActiveWorkbook
With ActiveSheet
Columns("B:C").Replace "'", ""
End With
End With
End If
Next Cell
Application.DisplayAlerts = True
End Sub

The result?


You can find the interatctive workbook here

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.