question on Excel and SQL

[SteveDB1]

Morning all.
I'm now crossing in to completely new territory here so please bear with me,
and if this should be posted elsewhere, please let me know. Or if I've
misstated something, please let me know......

I've been finding instances of our data being incomplete due to laziness on
my part because we have so many different sources of data in our offices and
how involved checking all those sources becomes. Yea, I know..... shoot me
later.

How would I go about having VBA code look on our SQL database to check the
data and do a comparison to match everything up. Then, if data is missing in
our excel files, either import it or set a flagging routine to let me know
that there's data missing?

As I said, this is going much farther than I've done in programming before,
so please be patient with me.
I know that the programmers who do our updates on SQL server are using VB
when updates, and modifications are performed. My HOPE is that I can use VBA
to accomplish my goal, and not cross into another language-- our IT dept
won't let me "play" with and language that makes changes to anything other
than Office Products. Which I'm sure you can all appreciate.

Thank you.
Best.

 

[SteveDB1]

I need to make an addendum to this post.
We haveth following books-- please advise which one would better describe
where to start.

VBA for Dummies 5th Edition by Mueller

Excel 2007 VBA- Programmer's reference by Green, Bullen, Boven and Alexander

And Walkenbach's VBA 2007 book.

Again, thank you.

 

[Tim Zych]

Well, there are so many variables at play it is difficult to give you one
suggestion.

You probably can use my Workbook Comparison utility. Select the data from
the SQL table into an Excel worksheet. Compare that to your Excel data.
Workbook Compare has extensive festures to assist you with mapping columns,
and allows you to save projects for future comparisons. It lets you assign
unique keys to match your primary keys in SQL so that sort order does not
matter. The comparison process generates a detailed difference report.

 

[Joel]

With these type problems, I use an Access VBA reference book not excel. Once
you use set db = GetObject("Access Application") and set the reference to
Microsoft Access object you can use all the macro code from Access. You may
also need to set the reference for ADO objects depending on the statements
you are using.