Table into separate sheets we'll begin by looking at how to select unique values from a column and use those to populate a record set that we can then loop through.
Inside that loop we're going to be creating dynamic select in two statements we'll do that to create multiple output worksheets in the same file as your.
Source data create a separate single output workbook with the multiple worksheets and finally create separate workbooks for each individual output that we'll.
Create so let's get started the setup for this video is fairly similar to previous parts of this series we have a macro enabled workbook which.
Contains the code we're going to use to manipulate data stored in a separate excel file called movies the aim for this video is to take the list of 1200 films on the film sheet.
Called output which is currently empty but which will fill up with all of our output files eventually i'll drop a link in the video description so that you can download these files yourself.
And follow along with me with writing the code if you'd like to just to show the basics of the code that i've written in the split table into separate sheets workbook.
The main subroutine we're going to be manipulating here is called split table and all i've done in here so far is write the code to establish the connection to the movies workbook.
Using an activex data objects connection object i'm not going to talk much about activex data objects here we do have separate playlists which cover that.
And if you're interested in the background of activex data objects and i'd recommend starting with this video here how do i get data from a closed excel.
File using vba
And i'll drop a link in the video description as well so you can get to this playlist quickly the other two subroutines i've created.So far are designed to tidy up the movies workbook so when we insert new worksheets into that workbook we can quickly get rid of those and a similar subroutine to get rid of all the.
Extra output files we'll create in the output folder but all of our code we're going to write is going to be stored in the split table subroutine.
For the first example i'd like to take the list of films and divide those into different worksheets according to which genre the films belong to the first step in doing that is to.
Create a list of unique genre names to do that i'm going to create a record set object and populate it using a select distinct query so back in the visual.
Basic editor i'm going to declare a new variable called rs as adodb.record set and then after i've opened my connection i'm going to create a new instance of.
The record set class i'll say set rs equals new adodb dot record set there are then a few properties of the record set i want to configure i'm going.
To say rs.active connection equals cn so that's the connection we've established to the movies workbook then i'm going to say rs.source equals and then here's where i'm going to write.
My basic select statement i'm going to say select distinct genre from and then refer to the film worksheet so that's film.
Dollar sign what i can then do is open
Up the record set by saying rs.open and then a little later on once i finish with that record set i'm going to close it down.Just before i close my connection so i'll say rs.close so that's the basics of creating a record set and populating it with the results of a select statement.
Next i'd like to loop through the record set and just to test that i've got the correct values write out the individual genre names into the immediate window.
To loop through a record set we can use a do loop and we can continue looping until we hit the end of file marker on the record set so do.
Until rs.eof i'll give myself a couple of blank lines and then say loop which will return to the do until statement and check that we're.
Not at the end of file marker yet and then inside this loop i'm going to do two things to begin with first of all i'm going to debug.print and then i'm going to refer to my record.
Set fields collection and refer to the genre field which should be the only one in that record set is the only one that we've selected in our select statement.
And i want to write out the value of that field then to make sure we move to the next record in the record set we can add a statement which says.
Rs.move next okay so having done that we can run the subroutine and we should find that we get the list of unique genre names written out into.
The immediate window the main thing that i want my loop to do is select all the films for the particular genre that we're looking at.
And then insert those into a brand new worksheet with the same name as the genre to get that to work we're going to use an adodb command object.
So let's start by heading up to the top of the subroutine and we'll declare a new variable cmd as a db dot command then after we've finished setting up and.
Opening the record set but before we begin looping we can say set cmd equals new adodb.command to create a new instance of that class.
And then just like we did with the record set object we can say cmd.activeconnection equals cn the next property of the command object we're going to set is its command.
Text and because that's going to be different each time we go around this loop let's set that inside the do until loop so we can say cmd dot command.
Text equals and then just to make it easier to write this out i'm going to break this across multiple lines using continuation characters and i'll start just by setting it to be.
An empty string once we've finished setting up the command text we're finally going to execute the command and to do that we can say cmd.execute.
So that's the basic pattern of the code we're going to write we'll go around the loop creating a different command for each different genre.
To set the command text we're going to use a select and do statement so inside these double quotes let's begin by selecting all of the columns from the film worksheet so i'm going to.
Say select asterisk or select sar to select all the columns and then let's have another continuation character and then we're going to say.
From and then the film worksheet so film followed by a dollar sign and then of course we'll want to make sure we only select films where the.
Genre is equal to the value of the genre field in the record set so i'm going to add another continuation character and then a where clause.
And ask where the genre is equal to and then imagine i was going to look for all the action films i'd write the word action in a set of single quotes there like so.
And then close the double quotes now to replace the word action with the value of the genre field let's get rid of the word action there and then i'm going to put in.
Two double quote characters position the mouse cursor back inside those double quotes give myself a couple of spaces position the mouse cursor back again.
A couple of ampersand characters and then the mouse cursor back a couple again and then i can refer to rs.fields genre dot value.
Okay so the entire thing concatenated together would for the first row read select all the rows sorry all the columns from the film worksheet where the genre equals.
Action that will be placed without venture animation awful etc the final thing we need to do to make sure all that data is inserted into a new table.
Is to add the into clause and that goes in between the select and from clauses so let's add a new line there and we'll say into and then in a similar.