My files and this folder contains four separate text files that will connect to now rather than my standard movies examples which you may have been expecting based on the fact that it's.
Only two days until christmas i went with a slightly more seasonal example each one of these text files contains a list of the 12 days of christmas and what dates you should be expecting to.
Receive certain gifts so i've got a proper csv file saved as a csv file you can see the comma separated list of values there i've also got a txt file version of that.
Same thing so it's still comma separated but saved as a txt file i've got a tab delimited text file so rather than commas we've got the columns separated by tab spaces and then i've got a mixed.
The visual basic editor i've created a new module and started a simple subroutine and i've also set a reference to the microsoft activex data objects library so with all that in place we can.
Get started with writing the code now if you already know the basics of working with activex data objects you might want to skip ahead to the chapter of the video where we connect to the tab.
Delimited file this section is just in case you need a quick recap or you're not that familiar with activex data objects here's the basic code we'll need to write to.
Connect to a text file i'll need to declare a variable that can hold a reference to an adodb.connection object then i'll need to create a new instance of that class.
So i can say set cn equals new adodb.connection and then i need to set the connection string property so i'll say cn.connectionstring.
Posts Related:
- Cara Halus Menolak Masuk Grup WA dan Cara Sembunyikan Nomor Kontak
- 4 Steps Gear Infographics **Free Download** How to convert picture to shape. Powerpoint Presentation Office
- Sistema de Painéis de EstatÃsticas Financeiras Gráfico de mapa dinâmico do Excel Tutorial 2 Office
Equals and in some double quotes i can - ID Card Make
Write out the connection string actually that's a bit of a lie i'm definitely not going to write out the connection string by hand i'm going to skip over to.Connectionstrings.com and i'll drop this link in the video description so you have it to hand and i'm going to use the microsoft text odbc driver so i'm just going to copy this.
Basic connection string from that website pop back to the visual basic editor and then paste all that in there's a bit of modification i'd like to do here just to make things a little.
Easier to read i'd like to break this onto separate lines i'm going to try to identify where the semi-colon characters are the different individual properties listed in this connection string.
So i'm going to break this up and then concatenate the line onto a new line and then get the extensions part there as well and do the same thing.
Okay the bit that we want to change here of course is the um the path that's listed i don't want to list files in the text files folder.
Placeholder i want to refer to the my files folder saved in the same directory as this workbook so i'm going to take away the c column backslash text files folder backslash.
And then i'm going to break this line up by concatenating this workbook.path and then to the end of that i can concatenate a backslash and my files and.
Another backslash and then that will give me access to that folder at this point i'm going to give it a quick test by opening the connection and then closing it again so i'll say.
Cm.open give myself a few blank lines
And say cn.close and then if i run the subroutine and nothing goes wrong and nothing apparently happens whatsoever that's a.Pretty good sign next i'd like to extract the content of one of the text files in the folder and to do that we'll use a record set object so back to the top of the subroutine.
I'll declare a new variable i'll call this one rs as adodb.record set then once we've opened up the connection we can create a new record set object so.
We'll say set rs equals new adodb dot record set and then we can set a couple of the basic properties of that record set object so we'll say.
Rs.activeconnection equals cn then we're going to say rs.source and this is where we'll write a very basic select statement to select everything from one of our text files so.
In some double quotes we'll write our very basic select star select asterisk which is just select all columns from and then the name of the text file i want to get.
Written in a set of square brackets so i'm going to use the 12 days comma dot csv file as our first example and then i can paste all that in then i want to just quickly check that.
That's going to work i can say rs.open and then a little later on we can say rs.close and then again if i just quickly run this subroutine just to check that.
Nothing happens if nothing happens that's another good sign finally in this little recap section i want to write the contents of the record.
Set out into sheet 1 in the workbook we'll be using sheet 1 for all the demonstrations here so let's make sure we clear it out each time so at the top of the subroutine i'm going to just say.
Sheet1 dot cells dot clear and then once we've opened up the record set we can say something like sheet1 and then refer to a range on that worksheet let's say range a2 and then.
I'm going to say copy from record set and reference the rs object that we've created if we just very quickly run that one to quickly check that it works so currently.
Sheet1 is empty but if we run the subroutine it's now fully populated with the 12 days of christmas we can do a little bit extra here as well we could add some column headings.
In and we can change the column widths as well so just going back to the visual basic editor after i've copied out from the record set i'm going to just quickly.
Copy paste this and say dot current region dot entire column dot autofit to make sure all the columns are the correct width.
And then to get the column headings out i'm going to use a simple integer variable so that i can count through the fields collection of the record set so the topic will say dim i as integer and.
Then after opening the record set we'll say for i equals 0 to rs.fields dot count -1 and then next i and then inside that loop we'll say.
Sheet1 dot cells open some round brackets we'll refer to row number one and then we'll refer to column i plus one and set its value to be equal to.
Rs.fields open some round brackets and then refer to the integer variable dot name and if all that felt a bit quick and you've not seen that before we have.
Plenty of other videos which talk about this basic stuff so i just want to quickly get to this stage now where if we run the subroutine one more time and then have a look back at the.
Worksheet it's a little bit neater and tidier and we've got the column heading sitting in there as well so this code works perfectly when we connect to a csv file the commas in that.
Csv file indicate the beginning of each new field which is generated in the adodb record set and that in turn generates each new column in the excel output.
The same thing is true if we connected to a text file using commas as the delimiters so the 12dayscomma.txt file is just a copy of the csv file with a different extension.
We're still using commas to separate out the various values so if we change our code to refer to the txt file rather than the csv file we can run the same code again and we'll also end up with.
Three separate columns indicated by the commas what if we tried to connect to our tab delimited text file though if i go for 12 days tab you can see that the values.
Are separated by tab spaces rather than by commas and if i change my file that i'm connecting to here to say 12 days tab when i run this subroutine this time.
Although the code runs it doesn't detect the fact that there are separate columns in those files so everything appears in one single column all mashed together.
The tab spaces are still generated you can see if you select a cell in this output the tab spaces are still there they're just not presented properly in the excel cells.
So how can we get the record set to recognize the tab character as a delimiter we actually have several choices but the one we're going to go for in this video is to create something.
