In my last post about notepad and VBA, I talked about a little macro I put together (and how I did it).
At work today I had an opportunity to share it with my colleagues, and to be honest I wasn’t feeling as confident about its usefulness after all. I discussed it with one colleague over lunch and they brought up the very real challenge of having disparate sources of data and how my solution could be another part of the problem. This threw me off a little because I had thought of that myself and dismissed it, but when someone else has the same idea, you have to entertain the thought a little bit more at least.
Later in the day after a meeting, I shared my solution with another colleague along with a brief demonstration. His eyes practically lit up as we discussed the opportunities it could offer, I was even trying to talk it down a little so that we could look at the potential downfalls, including it being another source of data lying around. He wasn’t having any of it, the only challenge he could see was a more positive one that I had also seen, how to quickly populate all the files, ideally in an automated way. That’s more my language!
So that’s another challenge for me to deal with, and possibly a simple one as I laid a fair bit of groundwork today just before and after that second colleague conversation. As I didn’t want to carry on with the same work that I feel I know how to do, I decided tonight to look at tackling another idea; running through all the text files in a folder and putting their contents into a spreadsheet.
It’s almost the opposite of my automated entry project that I’ve laid the foundations for, and it offers another set of challenges, but I’ve tackled quite a few of them tonight.
The first thing I needed to do was ensure I could read every line in the text file, to be used later as a single row entry in Excel. I adapted a solution I found here:
Open myFile For Input As #1 lineNum = 1 Do Until lineNum = 9 Line Input #1, textline text = text & textline lineNum = lineNum + 1 Loop Close #1
This would loop through each line in my text file and concatenate them all into a string named ‘text’.
The next part I needed was to parse the strings, breaking them up into single entries. I found that InStr was what I needed and followed the tips here:
ActiveCell.Value = Left(text, InStr(text, " ") - 1)
As I would need a loop to extract all the data, and a short string would be easier to parse than a long one, I decided to split the strings as I went through each line instead, which also required a second counter ‘i’ to move across columns.
Open myFile For Input As #1 i = 1 lineNum = 1 Do Until lineNum = 9 Line Input #1, textline Cells(ActiveCell.Row, i).Value = Left(textline, InStr(textline, " ") - 1) i = i + 1 Cells(ActiveCell.Row, i).Value = Right(textline, Len(textline) - InStr(textline, " ")) i = i + 1 lineNum = lineNum + 1 Loop Close #1
The final part of the puzzle was how to split each line’s string by different criteria. I decided to use the lineNum (line number) and a Select Case statement. Select Case isn’t something I’ve used a lot and it’s still pretty tricky, but I found some good guidance on it here:
Select Case lineNum Case Is = 1 Cells(ActiveCell.Row, i).Value = Left(textline, InStr(textline, " ") - 1) i = i + 1 Cells(ActiveCell.Row, i).Value = Right(textline, Len(textline) - InStr(textline, " ")) i = i + 1 Case Is = 2 Cells(ActiveCell.Row, i).Value = Left(textline, InStr(textline, " - ") - 1) i = i + 1 ...
The end result is that for each text file I pass in, this macro will extract the first 8 lines and output them into my spreadsheet based on the criteria in my case statements above. There is still some work to do around managing for files that aren’t formatted in the same way, and I haven’t automated passing in the text files so that part is manual, but I’m pretty happy with how this has turned out so far and can see it being useful already.
I’m not a VBA expert, but this does work, and that’s what matters in the beginning, especially when it could be adding so much value.
Later on I’ll look at using arrays to minimise the number of times I write to the sheet (which slows things down) and trying to figure out a better way of managing that i counter, it just looks so messy to me!