In a previous screen cast, I showed you how you can make a Morse code converter. So we can type in a word here. For example, I've got this is really big name here. It's 45 letters and we're going to convert this to the Morse Code. So in the previous screen cast we did this, and this file enabled the user to save it as a text file. So I'm going to go ahead and save that as a coded-message. And it also converted that to Morse code over here. So we can go ahead and open that, the text file, wherever we saved it. And this then is the coded Morse code for that word. And we see that we have a different letter on each line of this text file. So now what we're going to do in this screen cast, we're going to make a decoder so I can, this is what we're going to be creating. We're going to go to Decode, we navigate to the file that we want to decode, and that was my coded-message and then it goes through and imports. And then it converts the coded-message in a text file to an English word and it outputs that in a message box. So let's go ahead and do this is DBA. So I'm naming this MorseDecoder. The first thing we need to do is obtain a file, so I'm going to Dim a filename, FileName As String. Next we're going to open up the Application.GetOpenFilename. So the user can navigate to where the file is, and that's going to be stored as a string FileName. Then we're going to open that workbook, the file named Workbook. When we open that, it's going to import the text file as a new Excel workbook. So let's go ahead and work through this, just use F8, I need to put a break point there and this is going to be stored as the variable in the Locals window down here. That's the string for the FileName variable, so that's how we can obtain the file name. And then when we open that, it actually has opened a new Excel workbook here. And starting in cell A1, it's got all the letters. And in this particular word, there were 45 different letters. It's always going to start in range A1, so we're going to make use of that starting in cell A1. So right now, we've got two workbooks. We've got this workbook, which is the workbook that this code is in. And then we've got the, when we open this workbook, that's going to be the ActiveWorkbook. So I'm going to make a few additions here. So I've added a Dim tWB As Workbook, aWB As Workbook. I've Set tWB = ThisWorkbook, and I like to do this just so I don't get confused between which workbooks I'm working on. After we open the FileName, whatever workbook we navigate to. So that's going to be a text file imported as a new workbook. Then I'm going to set aWB = ActiveWorkbook. So the ActiveWorkbook then is going to represent the file that we import, the text file that we import. Moving along, we're just going to verify that Range A1 is selected on the ActiveWorkbook. But the next thing we are going to do is count the number of rows in column A. We also need to Dim nr and we're also going to Dim i as an integer and it's going to be accounting on iterating index. Now, the approach that we're going to take is we're going to import each row of the text file. So, if I open up our coded-message Excel file here, this is what has been created from out text file. Each of these rows or cells A1, A2 and so on is going to be imported into a coded vector. So I'm just going to call it coded and it's going to be size, number of rows. So I've Dim coded here unknown size at the beginning as a vector of strings. I'm also going to Dim a Letters as another vector, and unknown size. And then what we are going to do after we count the number of rows, we're going to ReDim coded and letters. Letters are just going to represent then the converted each item of coded. We're going to convert using the Excel Spreadsheet data back to the letters that correspond to those different cells of the imported text file. So, I've ReDim coded, I've ReDim letters, I'm also going to add option base one which I like to use when I'm working with vectors and arrays. I've added this for-loop here for i = 1 to number of rows, each element of the coded vector is going to correspond to, if we go back to the coded message. So we are going to iterate through range A1 to A number of rows. So in this case, the nr is going to be 45, and we're just going through and importing each of these elements. So, A1, A2, A3 and so on and those are going to be the elements of our coded vector. After we have imported all of the coded letters into our coded vector, we are going to go along and close the ActiveWorkbook. That will be done without saving changes. We are then going to start looking through each of our elements of the coded vector in our column B over here on the spreadsheet. And we're going to look for the matching elements in the A column. So we've entered into this For i = 1 to number of rows. I've got a second index here. So for each of the rows of our coded vector, we're going to search through all 36 elements of our B column on the spreadsheet to look for the corresponding element in the A column. I also need to Dim j as an integer. I've added in this if statement, if-then statement. If coded, so we're searching through, in this case, the 45 different elements of our coded vector for that really long word. So as we iterate through the B column, if we get a match, then the first element of our letters vector is going to correspond to the corresponding element of the A column. Once we've found a match, we're going to Exit For And then we're going to keep going. At the end of each iteration through i, so as we're looking through the various letters, at the end of each of those iterations, I'm going to add onto a word. So we're building our word, so the new word = the old word + Letters(i), so the individual letters. And I need to Dim Word as a string. And then at the very end, I'm going to MsgBox the wrd. So this should be all set up to go. I'm going to go ahead and go through this using F8. So we run through here, we obtain this workbook, we import. Then the text file that user select is going to be ActiveWorkbook, then we are going to select A1. So if I go into the ActiveWorkbook you'll see that the set A1 which is guaranteeing that as I step through in this thing. We're, of course, going to count the number of rows, which down here in the Locals window is 45. We're going to ReDim those two vectors, coded, and letters, and now we're going to iterate through and we're importing. So I'm just going to go through a few of these, and then we can look down here in the Locals window at the coded vector. So you see that we have imported, we're starting to import the various Morse coded letters. And we keep going, and I'm just going to Run to Cursor here to step out of the for-loop. We're going to close the ActiveWorkbook without making changes and then we're iterating through, and let me just go through until we find the first match here. I can open up down here the letters vector and you see that we have already converted the first three letters, P, N, E. And then I'm just going to put my cursor down here. We're going to Run to Cursor and finally we're going to MsgBox the wrd. So this is how you can import text files and in this case, I'm just kind of doing a Morse decoding example. But you can use this for a lot of different types of scenarios and different problems that you might have.
