?
Solved

Linking an Excel page to a database

Posted on 2011-09-07
12
Medium Priority
?
209 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 1200 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
Office 365 Training for IT Pros

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.

 

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 1200 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 800 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 1200 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 800 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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