Using a central excel database
Using a central excel database
(OP)
Now that I have a bit of time on my hands, 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.
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.
RE: Using a central excel database
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Using a central excel database
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 either 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 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
File > Return data to Microsoft Excel
The result is on the active sheet, a refreshable QueryTable is inserted. Can be refreshed on demand.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Using a central excel database
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Using a central excel database
RE: Using a central excel database
Can I use Excel to access a SQL database stored in remote server?
Idea would be to point to the *.mdb database file in the remote server using connection string and access password using Excel > Get External Data feature ? Would you have some sort of basic example or link for this?
Thanks
Life is not about waiting for the storm to pass. It's about learning dance in the rain.
RE: Using a central excel database
Is this an SQL Server database? You mentioned *.mdb which sounds like an Access DB.
So assuming that it is an Access DB, when you go thru Data > Get External Data > Access DB... the next step is to drill down to that DB on the remote server. This step will connect you to the DB and enable you to use the GUI to select one or more Tables in that DB, join tables appropriately, select fields, all very similar to the Access GUI. Once you have composed the query that returns the desired result in the GUI, you File > Return data to Microsoft Excel and the top-left result will return to the selected cell on the active sheet. You now have a QueryTable object on you sheet that you can Refresh to return current query results from you remote Access DB. If you right-click in the QueryTable results, you'll get a context-sensitive drop-down with options for managing your query.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Using a central excel database
Its an SQL database. I meant *.mdf database.
My apologies for the mistake. It was misleading.
RE: Using a central excel database
Skip,
Just traded in my OLD subtlety...
for a NUance!