CHALLENGE: MySQL, SQL, Excel Queries to My Book

Alright,

I have looked for a while and have attempted several things but seem to be falling short in all fronts - Instead of providing some really ugly code and mixed up parts (As I was smashing many together) I am going to pose this as a direct question in hopes that there will be various examples that we can run off of.

I have a book that displays a LOT of user information, it does well in its presentation but the file is rather large. Most of the users do not have PC's that can really handle it so the fluid motion I experience is lost and then the book becomes undesirable for use.

What I need -

I need the ability to Query an excel book that would be saved in a shared location (SharePoint) without having to open it.
Also the ability to Query MySQL and SQL directly via VBA so that I can control the query dynamically allowing me to limit the rows returned based on who has the file open!

The book currently ties into their AD which allows me to identify them. IF I can get the VBA to work so that I first identify the user and then query the databases OR an Excel document... this would limit the size of the file down to what the lowest possible size is as a shell.

Open to suggestions - and hoping someone has a viable solution that is workable.

NOTE: For the Database Queries I need to be able to have a full query or understand how to tie in multiple tables in the database to return the data.
I am keeping one source in Excel as I have done a LOT of work via PowerPivot to tie multiple databases together and normalize some ugly data.


Looking forward to a new start on this 2 week project - hopefully a breath of fresh air to get it going again.

Cheers,

Eddie

I make such Dashboards all the time that are connected to Databases, and based on the User Input selection, I want all my data and report to generate accordingly.

Initially I used to get the data at once, store it in a Worksheet, and use Excel Formulas to get the required information.

Such an approach is good only if you have a small database, and not so frequently updating data.

Over the period I'v realized, its best to query the database using the WHERE Clause, to get data only as required.

I feel the biggest decision is to select the mode of getting Data to the Workbook. I generally use ADO, Data Import Wizard & ADO along with List Objects.

All have their uses and draw backs, and generally selecting the right one, makes the dashboard more fluid I feel..

Also, many a times you can get away without getting any data to the workbook and instead processing the data in memory..

These are just tips, If you want something specific post it here..

Hey Dave,

Thanks for the reply - I have been playing with ADO and cannot seem to get the data to write into a sheet.

Like you stated the idea is that it would not really gather all data, instead VBA would build a where clause based on whom is accessing the document which in turn limits the amount of data. I intend on having this sit as a shell on a SharePoint page and the end user will open it up. Upon open it discovers the user based on AD and then the Query would gather that users info to then be broken up and displayed throughout the document.

One source will need to remain an excel table as again that was a huge undertaking to normalize a cluster of data

All attempts thus far have failed - Do you have a working example of the code in VBA that utilizes a full SQL/MySQL Statement?

The examples I have come across I cannot seem to make work and are limited to a single table pull...
The source they came from are so old I have yet to get a reply days later...

Eddie -

You can do something like this -

http://www.needforexcel.com/#!Call-S...f298ff2bc93245

This Code calls a SQL Stored Proc & gets data to Excel.

Or If your is a normal SQL Query, maybe something like

Please Login or Register  to view this content.

Thanks for that!
I will play with this and come back with questions should I hit them (Im sure I will ).. It has been a LONG weekend -

Thanks again,
Cheers,

ELeGault

Incase you have any issues, take a look at this -

http://www.needforexcel.com/#!Using-...f2de902a798bd8