Using a central excel database 3

[skeletron]

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.

 

[IRstuff]

Like other Office products Excel allows for a template, so you can add them to your installation.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[SkipVought]

Hi,

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!}

 

[SkipVought]

Thanx! Post back if you need help configuring your ODBC divers, using parameters, writing more complex SQL or needing other data acquisition options. I'll be glad to help.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[skeletron]

Thanks @SkipVought! I'll check this feature out. I've never used it.

 

[rotw]

SkipVought,

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.

 

[SkipVought]

Yes. I've been out if the loop for several years of retirement, so this is the best of my recollection.

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!}

 

[rotw]

Thank you.

Its an SQL database. I meant *.mdf database.
My apologies for the mistake. It was misleading.

 

[SkipVought]

I'm guessing that your remote access was sucessful.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}