Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using a central excel database

Using a central excel database

Using a central excel database

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.

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
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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using a central excel database

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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using a central excel database

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

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?


Life is not about waiting for the storm to pass. It's about learning dance in the rain.

RE: Using a central excel database

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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using a central excel database

Thank you.

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

RE: Using a central excel database

I'm guessing that your remote access was sucessful.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close