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! 

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
	Rows("1:5").EntireRow.Delete
	Columns("B:C").Replace "'", ""
	End With
	.Save
	.Close
	End With
	End If
	Next Cell
	Application.DisplayAlerts = True
	End Sub

view raw ForEachWbk_TweakAllFiles.bas hosted with ❤ by GitHub

The result?

You can find the interatctive workbook here https://public.tableau.com/views/ApplicationstoBritishEducation/Overview

Happy VBA coding!