Integrate Excel, VBA and SQL. Simplified and powerful

Simplified tutorial, how to Integrate Excel, VBA and SQL. Simplified and powerful
Please like, share and comment to improve channel algorithm
👉 Subscribe to receive our latest content czcams.com/users/VBA...
You can now support by buying any of the Projects or Source Code.
📥Downloads: pamaitech.com/...
👍 Support: paypal.me/pama...
✉️ Contact: [e-mail]
📌What to watch next:
Playlist: www.youtube.co...
Videos: www.youtube.co...
#VbaA2z

[@linnick9235]

Sir you’ve no idea how much all these videos have helped me at work. Thanks much.. Stay blessed!

[@VBAA2Z]

So nice of you. Glad to hear!

[@matnz7546]

This is best and most detailed VBA SQL tutorial I have come across. Thank you so much

[@VBAA2Z]

You're very welcome!

[@YazminAbat]

could not agree more, I thought it was necessary to use a combination of power query, sql and python before this :))) Thank you very much!!

[@hoseal8048]

Yours is the best VBA CZcams channel!!!
Keep it up!

[@VBAA2Z]

Wow, thanks!

[@bnancy3699]

Very simplified and easy to follow. thanks as always, appreciate your efforts!

[@VBAA2Z]

Glad it was helpful!

[@azel2239]

Thanks for another cool video.
Never knew about this.

[@VBAA2Z]

Glad to help

[@shellynomis7368]

Best tutorial, processional and practical indeed

[@VBAA2Z]

Thank you Cheers!

[@GiselleBrat]

you are awesome, many thanks

[@VBAA2Z]

Glad it helped

[@scottansir7708]

You're the best mentor. Thanks Pamai.

[@VBAA2Z]

My pleasure!

[@pekidonn5139]

This is brilliant. Thanks for another useful video.

[@VBAA2Z]

Glad it was helpful!

[@melivo853]

Thanks for this useful video. Please show how to compare 2 tables and return the differences and also find the variance between 2 tables

[@VBAA2Z]

Sure I'll share some more videos. Give it a shot from your end.
To return only non matching items try like this: Fruits is the key field name used for comparison. Hope it helps.
Select *
from T1
where Fruits not in (select Fruits from T2)
UNION
Select *
from T2
where Fruits not in (select Fruits from T1);

[@holieh5301]

Thank you for another great tutorial. Keep going

[@VBAA2Z]

My pleasure!

[@BEATWINCONTROL]

Thank you!!!! Just what i needed!!!!

[@VBAA2Z]

You're welcome!

[@LiangInfinite]

Very helpful tutorial thank you

[@VBAA2Z]

Glad it was helpful!

[@perymdiso1006]

Very useful video. Thanks

[@VBAA2Z]

Glad it was helpful!

[@susilsxzen-ff1zn]

Thank you sooo much

[@VBAA2Z]

you're most welcome!

[@chrish9518]

eye opener for me :) thanks a lot!

[@VBAA2Z]

Glad to hear that!

[@shaiknagulmeera1595]

Thank you for sharing your knowledge with all of us and I really appreciate it.
Can you please make one complete video related to Excel to power point automation. Thanks

[@VBAA2Z]

My pleasure. Have you seen below video? Anything specific you are looking to achieve?
czcams.com/video/dIqoXYy_Clg/video.html

[@kenstarr8612]

This is powerful. Thanks!

[@VBAA2Z]

You're so welcome!

[@Jean-hp7gh]

Amazing tutorial. Thanks to Pamai

[@VBAA2Z]

Thanks for watching!

[@Rahul-sn1wn]

Thank you soo much sir. You’re awesome

[@VBAA2Z]

So nice of you

[@VBAA2Z]

Interested to become VBA Pro? Here is 100% free guided e-course with Real-life projects. Sign-up to participate, get assignments and a certificate:- bit.ly/3eX8tzl

[@houstonvanhoy7767]

@VBA A2Z Please pin this to the top of the of comments, or better, put it into your explanations above the comments.

[@user-dp5xg7wf2b]

Thank you so much

[@VBAA2Z]

Any time

[@sktneer]

While using a SQL statement within the rs.Open method, you need to use the argument CommandType as "adCmdText" instead of "adCmdTable".

[@VBAA2Z]

Appreciate the nudge. just skipped my mind.
Adding sample below for reference.
rs.Open "SELECT * FROM [Transactions$];", ConXL, adOpenDynamic, adLockPessimistic, adCmdText
rs.Open "[Transactions$]", ConXL, adOpenDynamic, adLockPessimistic, adCmdTable

[@DAXifiedSatish]

Hi, I am facing an issue hope you can help me out. I have 2 connection and I am getting data from sharepoint list.
I want to make final data output based on inner join of both these list. I tried but unbale to join these tables. In your example there is single connection but as I am retrieving two lists so I have two connections. Can u guide be as I can't go ahead without inner join.

[@imranbhatti8580]

Great video Pamai. Would you please let us know how we can use SQL within a workbook? i.e. the source is not a separate workbook but it a sheet within the workbook itself .

[@VBAA2Z]

Thanks.
You'll use in same way. Simply mention the path of ActiveWorkbook.

[@imranbhatti8580]

@@VBAA2Z thanks for the quick turnaround.

[@VBAA2Z]

no probs.

[@kinga334]

Excellent tutorials!
Can we work with password protected excel file too? Doesn’t work for me when locked.
Please share example of how to delete records too. Thanks.

[@VBAA2Z]

for DELETE SQL try
SQL = "DELETE * FROM tableName WHERE FieldName = 'Sample Value';"
call ConXL.Exe (SQL)
Password protected excel file, in Excel you want to be able to pass the password in the connection string. There is a workaround though you want Create Excel obj, load excels up by passing the password.
All the best!

[@kinga334]

@@VBAA2Z thanks works perfect.

[@shadow_gaming_sk]

Can you please prepare the series basic to advanced

[@VBAA2Z]

thanks for your suggestion

[@KhalilYasser]

Thanks a lot for this masterpiece of the tutorial. Can you please share the VBA codes?

[@VBAA2Z]

Hi Yasser, code/project download are not available for this video. Please let me know if you're stuck / need help.

[@KhalilYasser]

@@VBAA2Z Can you attach the material codes so as to save the time for people who follow you my bro?

[@big1975E]

Is there a way to use ADO to write data from one workbook into another? If not is there a way to write data to a closed workbook using a connection from another Excel workbook?

[@VBAA2Z]

yes you can write data from one workbook into another

[@big1975E]

@@VBAA2Z I know how to query data from a closed workbook my goal is to edit that data in my active workbook once queried and then write it back to the closed workbook editing the data in the closed workbook just by using the connection and not ever opening the closed workbook that the data was queried originally. Do you have a video showing how to do this using VBA?

[@VBAA2Z]

Please follow same approach to pull, append, update data using the connection.
Try this video, it is uses access db however the code will remain the same czcams.com/video/c9jkK6aRWnQ/video.html

[@shadow_gaming_sk]

Hi I want online training training from you

[@VBAA2Z]

Any doubt please comment here

[@jaimec4572]

Why all data are Stored into the sheets like String
the data is store before the value has single quote in the cells like '1 , 'text how can i solve this

[@VBAA2Z]

That's how some users store numbers are string to prevent from losing leading zeros or formats

[@jaimec4572]

@@VBAA2Z can be a solution for this?

[@photogpphotogp7468]

How it's possible to check if a record exists in a access database with SQL and VBA?

[@VBAA2Z]

Yes try something like this
tb.Open "SELECT * FROM mytable WHERE ID = 1", connectionObj, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not SourceTb.EOF Then
' record exist.

[@vanreg08]

Hi, where can I get the file. I want to learn and apply what you show. Thanks

[@VBAA2Z]

Hi, you can download sample data used from below link.
drive.google.com/file/d/1bo7FgZvOwbDYr5ATL4-SahwmmJRnVeuD/view?usp=sharing

[@houstonvanhoy7767]

@@VBAA2Z Please pin this to the top of the of comments, or better, put it into your explanations above the comments.