Oh yeah baby! Life on the Edge! There's just something about those hidden

formulae that tends to make your life, how do the Chinese say it? -

"interesting"<G>

JH

-----Original Message-----

From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf

Of David Smith

Sent: Tuesday, August 14, 2007 3:11 PM

To: profox@leafe.com

Subject: Using Excel to handle VPF data

Howdy Fox-folk!

Below is a series of techniques I've ( sort-of ) perfected that allow local

users at my

company to benefit from up to date data we now have available within a VFP

.dbc file.( It

leverages the data extraction portion of Gil Hale's VFP-based DCMS system by

tapping into the

core .dbc file Gil created for us that houses the combined extraction data

from across our

company stores.) The thought is not everyone needs/can have access to/ has

the skills or

interest in learning the DCMS front end, so instead I aimed for MS Office

Excel, which is

comfortable and non-threatening to non-PC folk. The final product kicks out

VFP .dbc data

that updates automatically when opened and just works great. It's probably

old news, but I don't remember reading anything like this on these lists

before, so hopefully this will come in handy some time.

______________________________________

How to create a Excel-friendly database connection using the DCMS CDBM

translationengine.dbc.

Assumptions:

1.Excel 2007

2.VFP OLEDB provider drivers on client PC.

3.Paste-able SQL query that uses data from the translation engine.dbc or

good enough SQL

skills to be able to write the statemnt from scratch. I cut and paste mine

from Visual Studio

connection templates I created, but whatever works for you is fine.

______________________________________

One-time setup of dataconnection to the VFP database.

______________________________________

1. Open Excel, click on Data tab, then From Other Sources, then Data

Connection Wizard

2. Click other/advanced, then select 'MS OLEDB Provider for Visual Fox Pro'

and click the

next button. In next window choose the VFP databases option, then click

browse. Navigate to

the translationengine.dbc and select it. Click the 'ok' button and then

click the 'test

connection' button to test it using the machine collating sequence option.

3. click the 'okay' button, and uncheck the 'select a specific table' option

in the new

window. Click Next, then type in a new name for the connection file, and for

the freindly

name as well. click Finish, then Cancel.

4. Still on the Excel data tab, click connections, then Add.choose the file

you just created

from above. when the table list appears, select any table you wish, then

click Properties,

then click definitions in the new window. delete all contents of the command

text window and

paste in your SQL query code or type it in manually if you're skilled in

SQL. Once done,

click Export Connection File and save it somewhere handy. I saved mine to a

shared folder on

the DCMS server so I could use it from different client PC's without having

to rewrite it

every time. click OK, click close. whew! All done!

_______________________________________

data connection use

_______________________________________

1. From a new workbook, click the Data tab, then connections, click Add,

then click the

browse button. Navigate to the .odc file you just saved and select it. In

the window that

opens up, draw out a size appropriate to the ooutput of your query, or just

select the whole

sheet and then click okay. the query processess, then taa daa! your data

appears :) Now, save

the workbook somewhere on the local client PC. If you have multiple users of

the same query,

just copy the workbook to whoever needs it, just be sure they have the same

drive

mappings.Once created, you can use the sheets within Office 2003 as well

with only some minor

cosmetic loss.

I hope this was helpful :)

David Smith

Systems Administrator

Doan Family of Dealerships

(585) 352-6600 ext.1730

dsmith@doandelivers.com

No virus found in this outgoing message.

Checked by AVG Free Edition.

Version: 7.5.476 / Virus Database: 269.11.17/951 - Release Date: 8/13/2007

10:15 AM

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

[excessive quoting removed by server]