This is part 2 in which I explain how I solved the problem. Make sure to read the previous blog posts. Taken together, these show I though through and ultimately solved the problem.
Query Tables to the Rescue!
To solve this problem, I stumbled on something I rarely use, and that’s our good ol’ friend Query Tables. I don’t think they go by Query Tables anymore, but that’s what what they’re called in the code, so that’s the terminology I’m going to use here.
You might have used Query Tables as a manual feature before. For instance, to import a CSV file into Excel using Query Tables you would simply select From Text in the Get External Data Group from on the Data tab. See the snapshot below. These are Query Tables.
This functionality is really meant to create a connection between Excel and some other data source. The idea being you can manipulate and analyze data using Excel as your business logic and presentation layer.
But I didn’t need any of that here. I never expected the data in the underlying CSV file to change, so a connection that could refresh wasn’t necessary. Furthermore, unless you’re using these connections with PowerPivot, Excel has a bad history linking to other files.
But what Query Tables brings to the table (pun intended) is that they can dump an entire table’s worth of data rather quickly into a worksheet tab in one fell swoop. Try it for yourself manually to see how quick it is.
In fact, I was able to automate the entire process using the code below.
1: Private Sub UploadFiles(FilePath As String, WorksheetTabName As String)
2: On Error Resume Next
4: Dim CurrentWorksheet As Excel.Worksheet
5: Set CurrentWorksheet = Worksheets(WorksheetTabName)
7: With CurrentWorksheet.QueryTables.Add(Connection:= _
8: "TEXT;" & FilePath _
9: , Destination:=CurrentWorksheet.Range("$A$1"))
10: .CommandType = 0
11: .Name = "DeleteMe"
12: .FieldNames = True
13: .RefreshOnFileOpen = False
14: .RefreshStyle = xlOverwriteCells
15: .AdjustColumnWidth = True
16: .RefreshPeriod = 0
17: .TextFilePlatform = 437
18: .TextFileStartRow = 1
19: .TextFileParseType = xlDelimited
20: .TextFileTextQualifier = xlTextQualifierDoubleQuote
21: .TextFileConsecutiveDelimiter = False
22: .TextFileCommaDelimiter = True
23: .TextFileTrailingMinusNumbers = True
24: .Refresh BackgroundQuery:=False
25: End With
28: ThisWorkbook.Names(WorksheetTabName & "!DeleteMe_1").Delete
29: End Sub
So let’s go through how this works.
First, the way my program works is that the user supplies a folder containing the CSV files. Then, when they hit a button that says “Run,” I have Excel iterate through the contents of the folder. When I’ve verified a file in the folder is a correct file that will work with my application, I call the UploadFiles method just shown. The FilePath parameter contains to the direct path to the CSV file whose contents I’d like to upload; and the WorksheetTabName parameter refers to the worksheet where’d I’d like the contents to be dumped.
As you can see, on line 8, FilePath is supplied to the connection string when the query table is created. CurrentWorksheet is then used to tell Excel where the data will be placed (line 9).
On line 11, you see I named this query “DeleteMe.” Whenever Excel creates a new Query Table, it also creates a new named range in the background. There’s a default name Excel uses, but you can overwrite it with your own name. In this case, I use “DeleteMe” in place of the default as a reminder that the range shouldn’t exist (if my code errors out, I will see a DeleteMe named range in the name manager). You can see this happening below. It really helps with debugging.
The reason there are so many repeats is that each name created by the code above is scoped locally to the sheet in which it was created. Excel also adds that “_1” to the end. I imagine if I had multiple connections on the same sheet it would increment that last number. But maybe someone with more experience could confirm that for me?
In any event, we want to ensure there are no traces of the connection settings. So the BackgroundQuery setting to FALSE on line 24 ensures there are no direct updates from the backend data. Line 27 deletes the QueryTable we just created. Don’t worry this just deletes the connection behind the scenes, but it keeps the data that’s been dumped. Finally, line 28 removes the range that was just created. Since it’s local to the worksheet, we can refer to it as “WorksheetName!DeleteMe_1” and then delete it.
So let’s recap. I’ve created a mechanism to quickly import the data from CSV files. I used the functionality incorporated into Query Tables to bypass other solutions that either were not as good or weren’t feasible. In particular, I was able to get around two solutions I particularly hate: (1) the iterative algorithm; and (2) copying and pasting from other files.
All told, this solution works great! Right now, Excel will iterate through a folder of about eight CSV files and place the data from each CSV file rather quickly. In fact, it happens so quickly, I had to add a completion msgbox just to ensure the code was actually being run! Neither the iterative or copying/pasting solution can boast these results.
Remember, whenever possible, do more with less.