excel vba macro recorder to parse text using text to columns vba excel

in this video I discuss why professionals use the VBA macro recorder from time to time and how you can speed up your understanding of the Excel application programming interface that's API by using it to see how Excel handles complicated stuff and then use the macro recorder code in a small program written from scratch it's what going through this video because you pick up some useful tips and tricks along the way now let's get into it I was putting together a tutorial video and it's taking me longer than I thought it would on one of the actual videos I was doing I used the macro recorder again and it made me think about my previous video where I talked about using the the macro recorder and it made me wonder a lot of us me included in the past too cool for school because what I'm getting at is when you learn to program you can sometimes think that oh the macro recorder that's just for programming babies or newbies or whatever but even in the corporate environments if you're a professional programmer sometimes the macro recorder is useful because what I was doing was I was parsing some data so let me I forgot here let me just let me just show you what I'm talking about so I've got a without getting into the big tutorial which would probably come out later what I wanted to do was I went to a particular website and I wanted the list endemic comm I guess of all United States radio stations and their choral science because I want this to be part of a lookup table and as part of my tutorial on how to use Excel VBA functions but not only how to use them why to use them and the importance of good coding technique I want to sort of write a very bad and then talk about how I make it better but that's not the main point of this particular video the main point of this particular video is to show you a couple of tricks and you can watch me pars of this particular data so for example if I copy this stuff off of the web page I'm just going to do edit copy and the point the reason I'm doing this is is the point of this as I said is if you want to figure out how to parse this data so the first thing I'm going to do also is I'm going to show you another little trick and what it is is how to launch a fresh version of excel and you would want to do this for example let me just type excel you would want to do this because excel sometimes just wants to be too smart and so for example if you do something in Excel it then remembers what you did and sometimes if you're doing something that's slightly different what you did beforehand it remembers what you did beforehand and then it messes everything up and the way I've found to get around that is to launch a completely clean excel and so what I'm doing here is I'm pressing the start button and then I'm type and Excel let me just press a stock up again Excel the xce l ik hexane and then /x and when I do that it launches a fresh excel you're probably not seeing it because I got three monitors here but here we go we've got our three shakes of Excel I'm gonna launch a blank workbook so for example this is a fresh session to the session you see here so you see behind it so you got an excel here and you've got an excel here but if I go to view window you say switch windows you've only got this book showing because this is in a set session of excels in the previous one and this is useful for example another time when this is very useful is if you've got a really intense workbook open calculations going etc or maybe there's a long routine in that state me half an hour to run but you wanna do some work in Excel well one thing you could do is launch another workbook within that session but if for any reason you do anything that causes the Excel to crash you've lost everything whereas when you do Excel that X e /x and launch a new excel session if would session crashing does not negatively affect the other session but anyway back to what I was talking about I'm now going to paste the text in so when you copy text from a web sites use paste special and choose text and now we've got the text that we want so just for speed and bottom delete some of the rubbish stuff that I don't want here just going to delete and I'm now going to select everything by doing control as to us now if I want to know how to parse this I could as I said look up the documentation and if I go to the documentation well you've got the text to columns method in VBA so you got to go through all of this and notice that the examples are okay but you know you've got to take 15 to 20 minutes going through this and for example you've got a data type and it's got Excel text parsing type and you click on that this or you've got Excel you've got in enumeration type sorry it mentions somewhere down here which is actually important and then you have to look up your enumeration types here on this page and of course you got a parsing type so if you open that in a new tab there's your parsing types and you got your text qualifier so you gotta follow all these links and sort of figure out what's meant here or you could use macro recorder and then go look at the documentation so let's do that right now so let's put on the macro recorder so I'm going to go to developer record macro macro one in this workbook okay so now I'm going to do data and I'm going to choose text to columns and then the data is going to be delimited and that means there's two kinds of parsing so pop text the columns the old technical term for this parsing where you split a string of text into separate columns based on one of two methods a delimiter means there's something that's marking out different columns so for example if we look at this text here so this box here is showing what's on the spreadsheet you can see you've got masterís Montgomery being the city comma looking through this again you've don't have any other commas but looking through the data it looks like we can decide that you've got a comma as a delimiter and then you've got so get so let's see Galion comma then you have oh hey which would be Ohio and then you have a dash and then the name of the radio station so to a certain extent you could say those two delimiters in this data there is the comma for that particular column and then a dash which two limits the state name from the radio station so this is wfx or the call sign for the radio station and I guess it's handle or it's friendly name as the Fox Rock next Network so anyway that's using delimiting and the other one is fixed with where if I choose fixed with and go next you can put in lines where you're saying you want your columns to appear obviously the fixed width is not going to work here because each column is of a different length so we're going to just get rid of those in fact I'm just going to go back and I'm going to choose delimited now I'm going to click Next and you choose what you are delimiting on so first of all we've decided that we've got a cover so because of the comma there now you can see if it a comma off it's one piece of text but comma on and now it's delimited into various texts and also with this particular data you can see the first element is an asterisk now I don't want this asterisk it's just meaningless for what I need so what I'm gonna do is I want to choose other here and I'm going to put in an asterisk and so now that s to us has become a column as well so let's go to the next stage click Next now for each of these elements I can say well I don't want this asterisk column so I think do not import and well I will say I can leave these as general but for the sake of this particular tutorial I leave the first cut the first column was general and I make a second column text and I leave the third column as I'll make a text as well and so if you click you again you can see do not import skip skip general text text let's click finish it's past and what we can now do is I'm going to double-click just to see what we've got so first of all I'm going to click stop recording and I'm going to go to the visual basic editor and see what we've got in the macro so here's our text to columns parsing code provided by the VBA recorder so let's tidy it up first of all so we can see what we've got we can see there's a couple of elements that are false and the ones that are false just to keep things clean just going to get rid of because we're not using them okay so what we can see from the text to columns when we look at the actual code is you've got your destination range a1 but that's please believe straightforward data type excel delimited text qualify a double quote comma equals true so it's always going to happen it's pretty much always going to have the text qualifiers double prototyping comma trail order other trail and the other character is the asterisk but here's what's interesting is the field info so so if we look at our field info camera I've just launched another copy of Excel and I've just repeated the operation so that we've got the text the columns wizard up here you can see field in force as away one two three and four so what that means is you've got a raid one is this column here which is the one that's been governed by the as Thomas a way to is this column of a3 is this column and a way forward is this particular column now let's look at what these numbers mean so you've got your column data types so if we look at the column data types we've got data type nine so data type nine is Excel skit column so nine column is not passed so that means nine means don't import this column so there you go don't import that column in the second part of the array one means general format so remember I said this column is general so that's say if I just go to the next space and just show what we did so do not import column skip that is the nine which is here skip general is one so you can see general format and then I made this one text so text is too that's fine too here text and then general again which is two so it's a good way of quickly making sense of Microsoft's details but not always hugely clear information so sometimes when you want to figure out how to get something done in Excel maybe it's with a shaft or whatever don't be too cool for school to use the macro recorder to do something and then incorporate that in your code so now having shown you the macro recorder part let's now just quickly light a function based on what we've just done to pass the data so this is another instance of Excel I've got here I'm just going to insert the module and and was named sheet one just data import just for the heck of it data import so I'm just going to create a function called saw pars data okay so I'm going to let's write our comments first guess reference to data pause the data and not sure if I'm going to do this or not but we'll see the data okay so get reference the data so I'm gonna do a dim range data as range and I'm going to make the function bit more useful I'm going to do by Val s said sheet named as string and I'm going to do dim wks as worksheet so get reference the data would be set wks equals this workbook top worksheets said she's name and you know I've got the name right when the intellisense when the case sensitivity jumps to the same name as the argument that I passed then this being the arguments that I passed in okay so given reference to the data well that's going to be set range data equals wks time range anyone that current region okay and so now looking at the macro I recorded earlier I'm going to do range data dot text to columns and the destination is going to be destiny des di n a tion and that's gonna insert thing of command equals and I'm just going to make it the same idea as before so squatty range data does resize would enroll one column which means cell a1 in this instance okay and I'm going to do let's just see comma underscore and go down to the next line and I'm going to give it a data type of exercise with a worse version of Excel delimited comma and space on the store and I will with I put in a text qualifier I probably don't even need a text qualifier because it's going to be assumed to be underscores but on one foot comma command equals true comma underscore and what other piece of information do we need we also need the other being true so other command equals is true because we are putting in an asterisk as an asterisk to also get rid of the first column data so other week was true and we also want it to we need to put information on other cap character sorry so I'm going to just put that here other child command equals quotes and that no for example with this function you could pass that information into an argument in as an argument also we look at that in a moment so now the other thing is we need to put in the field info so field info command equals and it's first of all it's an array of arrays so a of our a Y of may and then within that away ARR a Y and open quotes and I'm just going to make things easier by copying that to the clipboard and so first of all column 1 is going to be 9 because we want to skip column 1 which is going to be the a stresses and then we want oops and then we want another way to deal with column 2 so column 2 and we said column two is going to be generally if I remember correctly and that was one and we put in a comma and copy away again and column three was going to be text so that's text is 2 and we're going to use general again so array and so as column 4 comma 2 for general or 1 for text whichever you prefer and comma and do we want to put in trailing - numbers trailing - numbers I didn't even look up what that is but a you can trailing - numbers and chances are I don't even need this one true okay so I've probably missed something out here so I got this here should be a space and yes that's the text to columns fitted but now I'm going to go one step further to make that to customize this function I'm going to push in optional by Val other ciao I said other child as string what what I want to do is if s other Chow equals an empty string then else this and you know maybe this can be made better but it's just showing you how you can have a useful function so I'm going to copy this to here copy that to there and so basically if we haven't got a another character this function main ones and if we have another character that function oops passing the microphone darlin that function runs so if we have another character we want the delimiter otherwise we don't okay so let us now test the function so I'm going to do all the subroutine in this instance so if I click powers data and will it run first time and this is how a code is written it's messy to start with so sheet name is data import and the other character is going to be an asterisk before I run it I'm also going to say range data not current region columns dot autofit so that the you'll see the columns jump when it's been done here we go named argument not found trailing - numbers so I grasp enough - long let's just face that let's stop the code I should have noticed that because the you guys probably noticed it because the capitalisation wasn't correct let's try one more time okay here goes and there we go it's paused so that's an example of using the macro recorder but then incorporating it into what you're doing these techniques of occasionally using the macro record or to figure out some complicated functionality can be very helpful don't be too cool for school don't be afraid to use the macro recorder now and there and look if you found this video helpful don't forget to Like and subscribe and hey try ringing the bell as well thanks for watching you

Channel: Sean Johnson

Views: 728

Rating: 5 out of 5

Keywords: vba, vba excel, vba tutorial for beginners, vba tutorial advanced, vba tutorial excel, macro, vba macro, vba macro excel, parse text file in excel vba, vba text to columns, text to column, text to columns in excel vba, text to column excel, excel vba, vba tutorial, excel vba tutorial, business programmer

Id: jtnVPPPS5NI

Length: 23min 33sec (1413 seconds)

Published: Wed Apr 15 2020