Excel vba to populate more than one sheet from a sql database

I am not experienced in excel vba. I have an excel workbook with more than 1 sheet. Each sheet should refresh on clicking a button within the sheet. Providing here the code used with just 2 sheets as an example and have removed all sensitive data from the code. I need help with the following:

a. if I give this document to someone else they are prompted for a database name and password? Since there is an EXEC function used I am forced to use the Read-Write authentication which I cannot distribute to every user. How can I embed the connection string here so other users are not prompted for database login details.

I am not sure how can I provide an Excel file as an example without revealing any sensitive data. However the whole VBA code is copied here after deleting sensitive data.

Please Login or Register  to view this content.

Thanks dflak. If i decide to go down the path of MS-Query i have the following questions:
1.I have a stored procedure as a query: EXEC TATDataReport 'AUCKLAND',2,2024.
a. I would like to add these inputs in the spreadsheet as a dropdown or user entry and the user can then select/input the options.
b. So EXEC TATDataReport 'AUCKLAND',2,2024 would read: EXEC TATDataReport [sLoc],[mthNum],[yearNum] - How do make this work?

2.My workbook has 4 worksheets - one for each location. So can i add a button to the sheet to retrieve data based on the inputs?

Regards docgee

Hi
I have found a different way to work this out but am stuck at one spot in the vba code. I am not sure how to write Redim Preserved array for the following output.

Please Login or Register  to view this content.

I have checked and the resultsArray is correct. I am getting a "Run-time error '9' Subscript out of range" on the Redim Preserve line below

Please Login or Register  to view this content.