Excel internal database connection

3
votes

I built a data entry UserForm to populate a worksheet that will serve as the raw database. The raw data requires further manipulation and analysis in order to be reported, so I set up a database connection using Get External Data>From Microsoft Query>Excel Files, pointed it to the file I was already working in, selected the fields I wanted and performed basic functions on those I wanted aggregated. This creates an Excel table where I then use formulas that to complete the analysis. It works great for me; I can add entries to the database, Refresh the summary table, the new entries are added and the formulas populate automatically.

The problem is that no one else can refresh the table because it's looking locally for the file. The connection string is: DSN=Excel Files;DBQ=C:\Users\MyName\Desktop\Folder 1\Results.xlsm;DefaultDir=C:\Users\MyName\Desktop\Folder 1;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

I have a very basic understanding of the database connections, but I need this file to be as automated as possible by request of my colleague. Can I fix the connection string so that the file is "flexible" and can be refreshed on any computer? Is this the best solution? If not, what else can I do that does not involve downloading additional plugins or 3rd party add-ins?

1
exceldatabase-connectionconnection-stringvba
Do you have an actual database to connect to? Because having everything on your local machine is not a good idea. – durbnpoisn
The "database" is just a sheet within the same Excel file. – 406LQE
You'll have to separate the raw data into an independent, network accessible workbook and then have the "working" spreadsheet with your user form connect to the common "database". – PeterT
Which, assuming you have a network drive that everyone can get to, is the best solution. Excel couldn't care less if the reference sheet is within the workbook or not. – durbnpoisn

1 Answers

1
votes

If what you need is a file containing the raw data (a Database) AND one or more excel files connected to it that pick up the data from the database and work with this data, you need to split the two things. You can do the database with an access file located on a shared directory with an appropriate table and you can reproduce the user form in this file so the insertion of the data will be made in this file. Then you connect one or more excel files (using connection Mode = Share Deny None, so you can update the data and at the same time work with them from the excel files), the data will be imported in the files in tables and here you do all the proessing you need.

If one file is enough for you (you don't need to have a database with the row data separated and you don't need to use the file from different location simultaneously) and all the problem is that if the file is opened from a different location from the one specifyed in the connection string it does not work...well in this case (that seems the case) i don't know why to use a connection to the same file.
If what you need is a table for work with, just create it selecting the range with the data you already have inserted (Create a table - quick start guide) and then when you add data through the form instead of adding them in a "normal" row, add them to a new row of the table with something like WorkSheets("name").ListObjects("table_name").ListRows.Add and add the data in the new table row.