Solved

Linking an Excel page to a database

Posted on 2011-09-07
12
203 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
  • 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
 

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now