QUESTION
I’m wondering what the best way is to implement a central excel database into my workflow. Currently when I start a spreadsheet, I will copy common data (rebar sizes and areas, locations and climate loads, or steel shapes and properties) to a separate sheet in the workbook. Then I will define the table and use VLOOKUP functions to pull the data. It’s not a difficult task, but I feel like it is a bit clumsy and makes my files a lot bigger.
What I would like to accomplish is to have the “common data” resting in a single workbook in a central location. Then use add-ins or VBA to pull the data into current lightweight sheets? I’m just wondering if an add-in is the best way to accomplish this.
REPLIES
@SkipVought
Excel does quite nicely accessing databases of all kinds using standard SQL, including,
- Other SHEETS in the same workbook
- Other WORKBOOKS’ sheets
- MS Access
- Oracle
- SQL Server
- DB2
- A variety of text files
I have used this basic technique to acquire data for the past 25 years. It can be done using MS Query via Data > Get External Data…
If you’re grabbing data from Excel, use
Data > Get External Data > From Other Sources > From Microsoft Query > Excel Files* (and check the Use the Query Wizard)
… and drill down to whatever workbook you need.
Each Sheet containing accessible data in a Tabular Format, must adhere to the following requirements:
- Headings in Row 1
- Contiguous data in Table (no empty columns or rows)
- No other data on the Sheet
Set up your query in the grid (very much like Access) and when you are satisfied with the results return the results to the Sheet:
File > Return data to Microsoft Excel
The result will reside on the Active Sheet. A QueryTable is inserted as an Object in on your Sheet. The QueryTable can be Refreshed on demand to display current data.
SOURCE
https://www.eng-tips.com/viewthread.cfm?qid=456273
Above is a snippet.