Excel VBA Introduction Part 58.31 - SQL for Excel Files - Split a Table into Separate Sheets

By Andrew Gould

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can join this channel to get access to perks:

This video explains how to update existing data in an Excel workbook using SQL UPDATE statements. You'll learn how to assign a simple value to a column, how to calculate a new value to produce a different result for each row, and how to update information in one worksheet using information stored in a different worksheet. The final part of the video discusses how to import data from a separate workbook using a SELECT INTO statement and then use the imported data to update existing data.

Chapters
00:00 Topic List
00:40 The Basic Setup
02:18 Selecting Unique Values into a Recordset
03:45 Looping Through a Recordset
04:53 Creating a Command Object
06:19 Building a Dynamic SELECT INTO Statement
09:37 Creating New Sheets in the Same Workbook
10:26 Worksheets and Range Names
12:43 Creating a Single Output Workbook
15:49 Deleting Output Files
16:56 Creating a Separate Workbook for Each Output

[rogeriopalma]

Andrew! Thanks for your time and dedication!

[rubenhuertagranados]

God Bless for so many help ! you have help a lot, also clear and direct, the best of the best, 5 of 5 Stars

[janezklun]

Great tutorial, very interesting, the best vba on YT, thank you Andrew

[VillaOuk]

Thank you, Andrew. This is a great tutorial. Great help really

[daves]

This is another great video. I thought I might suggest another possible video in the SQL for excel files series, which I hope others might benefit from. That would be in this use case: -
a) Excel connecting to a database and there are stored procedures to be triggered (example extract below with passing a parameter into the stored procedure),
b) How would we implement sql looping on an initial sql data list returned then merged with a join to values from another table \ sheet,

example of a) stored procedure:-
Set wb = ThisWorkbook
Set ws = wb.Sheets("M_SKU")

ws.Activate

DBPath = ThisWorkbook.FullName 'Refering the sameworkbook as Data Source
sconnect = ;"
conn.ConnectionTimeout = 30

conn.Open sconnect

Set cmd = New ADODB.Command

cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "PLM_HIERARCHY" 'ETL_PROD

'set the parameters
cmd.Parameters.Refresh
cmd.NamedParameters = True

=
=

Set rs = cmd.Execute

[frikduplessis]

Thank you again Andrew for a brilliant tutorial as always, Awful is a very interesting genre 😀 and I think I know what is in the file 😉

[Mohamed.GadAllah]

I am amazed with quality and quantity of your videos. May I ask if you are planning to do Microsoft Access videos?

[dilipdelwash]

Please Upload a Series of SharePoint From Excel Vba with sql Queris

[Munichandra_Reddy]

This time, I am using this code, existing worksheet I am creating sheets, but by using " in-clause " I am unable to create the files that time I am getting runtime error

[ousmanetall]

Hi teacher andy, i have noticed a small little error in CleanUpMoviesWorkbook Subroutine, in the section of code that closes file when it is open. That section that establishes: "if not FileAlreadyOpen then wb.close", will only make sense when file is closed. If file is already open that section of code will never be executed and never close the file. Then one must add "FileAlreadyOpen = True" bellow "Workbooks.Open(filePath) section before "Resume Next", and change the "if not FileAlreadyOpen" to "if FileAlreadyOpen", in another words changing that section of code From checking if "False" to if "True"

[tejamarneni]

Thank you Andrew for such an amazing series. Can you please let me know what is the next topic? Power BI or SQL?

[pratapbogolu]

Just wondering if any video is available on 'how to connect with ms exchange server from vba to loop mails.'

[Munichandra_Reddy]

whatever code you explained I have written that code, but I am getting only columns header data, I would not get data.

[luizdomingo]

Congratulations Andrew, Thank you for sharing your knowledge with us.
I would like to know your opinion about the Vba, many say here In Brazil that the VBA
It will be replaced by the typescript, and that soon it will die.
What do you have to say, what is your opinion on this subject?

[teodorohturnos]

Hi Andrew. Once again Thank you very much. I today reached the end of Part 31 and I can only hope, that you one day continue this series. Maybe one point you could then touch is: In real project where would you use SQL VBA, where "normal" VBA? Connected to this: If you compare the speed of a) this SQL queries with Array Operations. Is there any benefit is using the one of the other technique? b) Power Query speed vs. SQL and VBA?. Let me finish with a joke I already tried in the past. I am looking forward to the 31 intermediate lessons. Thank you so much!!!

[Munichandra_Reddy]

Hi Sir,
Thank you so much for explained.
Instead of movie file, we can't do inside macro workbook?
We are keeping code in oneworkbook and we are creating worksheets another workbook, actually I don't know the reason.

Why? I hope it's not possible

[munichandrakuppani]

Hi Sir,

By using loop statements ; How would We do? please try to explain .

[corneliuzabolotnic]

Hello once again,
I have a question about a function that I created in Access.
One parameter of the function gets a variant.
I wanted to use that function in a Query in Acces, I use a Subquery to get the values for that parameter, is that posible?

[munichandrakuppani]

Hi Sir,
in Data analyst or Data scientist role, python technology more popular present days,

will you teach on python? coming soon