I have a workbook with multiple sheets, the first is a reporting and summary sheet, and the others are data lists that it grabs data from.
The data sheets each have a similar structure, a region of rows names “data” (for example), and a set of columns which are named for example (name, id, score, item).
The names of columns will differ on different data sheets.
What I need to create is a button on the front page which invokes a macro for each data page, which runs a SQL query against a (MySql) database and returns the results into the named region on that data sheet. An example for just one data page is adequate. I have my own SQL query and database, so any very simple sample test query on a simple table is fine. I have already setup the DSN for the database, and done an Excel MSoft Query from excel on it, but couldn’t automate this or manage the named regions aspect.
So what is required is a VB macro that when invoked will so a SQL query and insert the result-set data into a named region on a page, expanding the named region if needed.
The columns in the result-set are fixed, but the number of rows can vary, and thus the named region must expand vertically to accommodate the actual query result size.
I also want the macro to be about to take a reference to a cell in the main page and update it with the date of the last update.
I think this is fairly simple, but I don’t know how to do VB programming and manipulate the Excel named ranges.
Including a test /sample/ spreadsheet is good, but not required, as long as it works for me when I copy it into my own project here.