Solved

Linking an Excel page to a database

Posted on 2011-09-07
12
208 Views
Last Modified: 2012-05-12
How do I link a sheet in the workbook to a database, so that a few columns of data are taken from the DB?

Then, when the user has chosen to work offline, the old data will still be there and referencable by other pages in the Excel file?

I assume we could make a toggle field in the spreadsheet with two value:

"Work Online"
"Work Offline"

which would govern whether the link becomes active or not.

How do I handle this?

Thanks,
newbieweb

0
Comment
Question by:newbieweb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 7

Accepted Solution

by:
Jacobfw earned 300 total points
ID: 36495729
Excel provides ways to talk directly to your DB (either OEM Data connection or ODBC).
What version of Excel are you using?

In Excel 2010, use the DATA -> Connections to setup the link to your database and configure a new connection to your database.  Or Data -? From Other Sources and use the connection wizard.

You can choice to download your data into the spreadsheet and refresh it using the tool bar, or create refresh button in the spreadsheet to facilitate your users.

Jacob

0
 

Author Comment

by:newbieweb
ID: 36495900
Excel 2007
0
 

Author Comment

by:newbieweb
ID: 36495911
I'd like to refresh the data only when the user has selected "Work Online". Otherwise, I'd like to use stale data from the last time they were Online.
0
Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

Author Comment

by:newbieweb
ID: 36495948
Rather than using ODBC, I would probably get a direct database connection to an Oracle database on our server. Otherwise, each machine would need to have ODBC configured.

0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 300 total points
ID: 36496127
0
 

Author Comment

by:newbieweb
ID: 36496157
I can't watch video's at work. Bummer.
0
 
LVL 6

Assisted Solution

by:akajohn
akajohn earned 200 total points
ID: 36496169
This is possible. LinK your "Work online" button to a macro that refreshed that connection.
I use Oracle Drivers. Either these drivers or ODBC.

I used: http://www.experts-exchange.com/Database/Oracle/Q_27036375.html

You are given the option to refresh the data. If the connection is not available ,like for example you send the data to another company, the data remains in the sheet as well as the connection details. (So be careful not to store usernames and passwords).

Each time you refresh Excel will prompt you for the connection details.

A>
0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 300 total points
ID: 36496172
That is ok, it was a product demo.

You will still need to install some type of connector for Oracle.
Do you Micosoft ODBC for Oracle installed?

0
 

Author Comment

by:newbieweb
ID: 36496247
Interesting point...

Is there a way to keep the username and password out of the Excel VBA? I do not see how, but I am very new to VBA.

> Do you Micosoft ODBC for Oracle installed?

Not yet, I have nothing installed. I am trying to grapple with what's the best direction to follow.
0
 
LVL 6

Assisted Solution

by:akajohn
akajohn earned 200 total points
ID: 36496353
This : http://www.experts-exchange.com/Database/Oracle/Q_27036375.html
does not use VBA. There is a dialog box whixh asks you to remember the password, just uncheck it.
0
 

Author Comment

by:newbieweb
ID: 36496558
I think the choice here is to try and encrypt the password. We would not want to require users of Excel to ever know the password, yet we'd want the addition of a file (say a text file) to give than person full access.

A simple encryption algorithm would do.

I will post a different question to learn more about how to do that in VBA.

0
 

Author Closing Comment

by:newbieweb
ID: 36496592
Thanks.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question