In a previous screencast, I showed you how we can export data from excel files into txt files, T-X-T files. In this screencast, I'm going to show you how you can import data from txt files into Excel. The first part, I'm going to create a phrase in a txt file and we're going to import that single string. So, to use to make your own txt files, you can go into the notepad and I just search here, and we open up the notepad, and I'm just going to type in a string here. And then, I can save that string as a.txt file on the desktop. So, I'm going to call that import-file and it's going to be named.txt file. So, that's my import file. Now, let me just show you what we're going to be creating. We're going to be creating this subroutine that asks the user to navigate to the file. So, that was my import file and then it's going to go through and it's going to put that in a message box. In the second example, I'm going to show you we've got in the previous screencast, I showed you how we can export a vector of items in Excel to a txt file. So, we had this example where we had the Seven Dwarfs as separate lines. We're going to use this then as a basis and we're going to import this output example. So, the second part of the screen casts, I'm going to show you how to import txt files that have multiple lines of data. So, let's let's use our output example. So, this is what we created in the previous screencast. We're going to open that and it's going to then take in those items that were in the txt file and import them into our spreadsheet. So, let's go ahead and get started. So, we're going to make a simple Sub called ImportText. I've Dim FileName as String, 'S' as string. So, file name is going to be the name of the file that the user navigates to. 'S' is going to be the string that's contained within that first line of the txt file. Next, we're going to use the GetOpenFilename application as we did in a previous screencast to open up a txt file that the user navigates to, and then, we're going to open up that Workbook. Now, when I'm working through multiple workbooks, I don't think it's necessary in this case but I like to. A lot of times you can get confused between what's known as the active workbook which is the one that you just opened. So, once we open up this file name Workbook, that's going to be the active workbook. This Workbook is always the workbook that is, that this code, the VBA code is in. So what I like to do, is I like to dim two new variables. This work with tWB an AWB for this workbook and active work. So, I've got tWB, this workbook and AWB, that's going to be the active Workbook as workbooks. Those are just another type of data, data-type that you can dim variables as. So, we can use set tWB as this workbook and it'll just set, it will remember this file name that this code is in and actually that is supposed to be an equal sign here. So it's set tWB = ThisWorkbook. After we open up the Workbook that the user navigates to, we're going to set aWB, that's going to be the active cell as the active Workbook. So at this point, let me show you what's going to happen. So, we go through this. We set this Workbook tWB equal to this workbook. So, if you look down here in the locals window, if you look down here into the full name, that's this workbook. So, that's the workbook, the path for this workbook. Then we're going to have the user define where we're going to import the data and I'm going to import the data from the output of the previous screencast, our are Seven Dwarfs. So, we do that and what has happened is we have opened up then a new Excel spreadsheet and this is basically the txt file being converted to an excel spreadsheet. So, if I open this up, you see that we've got, we've imported them into our new file. So, this is a separate file. This is the active workbook now and we've got into in cells A1 through A7. We've got the data that were in the original txt file. So again, aWB is that workbook that I, the txt file, we converted into a workbook and that's what we're working with now. So, that's the active workbook. The next step is to count. I'm going to use the count A function in Excel to just count the number of columns in our active workbook, which again is this file, that will tell us the number of items in there that are not blank. So, we have seven. That's another way to count the number of rows. Instead of dimming 'S' here as a string, I'm going to make this a vector because that's going to contain the components in our txt file, the different lines and now, 'S' is going to be a string of unknown size at the beginning. Once we know the size, the number of rows, I can ReDim S(nr) as a string. So, it's going to be a vector of strings. When I'm working with vectors, I like to use Option Base 1 up here. So, I've added that and now to populate our 'S' vector. So, our 'S' vectors are going to represent in our active workbook here that corresponds to our txt file. 'S' is going to represent all the items of column A here. So, I'm going to implement a for loop. We're going to go from i equals to number of rows. Each of the 'i' element of 'S' is just going to be Range A and i. So, we concatenate A with I. So, the first one is just can be Cell A1, Cell A2, and so on. So, we're going to pop this for loop populates 'S' with the items then, in our active workbook which is this. And now, I'm going to do two things the first one is we're going to close the active workbook. So, that's the workbook that was converted from a txt file to a workbook. We're closing that. We're going to not save any changes. So, you can do SaveChanges;=False. And again, this isn't really necessary but I like to do it because if you have other workbooks open, sometimes you can get confused and you accidentally like paste or output stuff into the wrong workbook. So then, I like to activate that workbook and finally to output. Now, there's multiple ways to do this but the one that I'm just showing here is we're going to do range of A1. So, we're going to output into that range you can choose whatever range you want. There's multiple ways to do this but we're going to go range A1 to a number of rows, that's seven in this case, equals the transpose, the worksheet function transpose of 'S'. Remember 'S' vectors are row vectors so if you want to make a column vectors, you have to transpose. So, let's go ahead and do this. Like I've been saying in a previous screencast, sometimes when you use these different types of applications in debug mode, it just kind of goes to the end. So, I'm going to put a couple of break points in here and I'm going to go ahead and press F8. We need to define nr and i, I guess. I forgot to do that. So I've dimmed those variables and let's see if there's anything else I forgot. So, we're going to set this workbook to this workbook. We open up this to choose our file that we want to import and see how it just skipped to the next break point. I don't know why it does that but anyway, we counted the number of rows. We ReDim 'S' then, as a size 7 and now, we're going to import into our 'S' vector. All of the elements, you notice that here I've got this is our txt file that has been converted to an excel file and we're importing those names down here in the locals window and I can run through the last two of these. And then, we're going to close the workbook, not saving any changes we're going to activate this workbook. And then, what we're going to do is in Range A1 to A7. We're going to output the data so we can go back to the original spreadsheet and we've output that. So, you can change where you want to place that with changing the the Range here. And you can also have the user select that in some more advanced features. But this is how you can then import data from a txt file into Excel.
