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

---

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]

I do it the other way and use vfp to 'push' data to Excel

If anyone's interested here is how I do it:

My main form has a dataObj with properties for the database connection and

the name of a view or table of de-normalised data (I'm using a backend

database but the same techniques would work for fox data).

An option on the main form (menu, button, click event, w.h.y.) calls a

modal form with a vfp field picker object on it, the user selects the

fields they want and click a button to create a spreadsheet from the data.

* **** de.beforeopentables() of fieldpicker form

* ** It took me a while to work out how to do this - the 'trick' is

* ** to realise that the calling form is still in scope here

(_screen.activeform).

LOCAL cSQL2 as String, cSt as string, nOK as Integer, nHndl as Integer,

parm as String

nHndl = _screen.ActiveForm.oData.nHndl && the connection handle

parm = _screen.ActiveForm.parm2send && the table / view name

cSQL2 = [select * from ]+ALLTRIM(parm)+[ order by 1]

nOK = SQLEXEC(nHndl, cSQL2, 'mycur')

SELECT mycur

**************************

* **** fieldpicker form button.click() code:

LOCAL cStr,cFile,i,aSelections

LOCAL oXL as Excel.Application

LOCAL oRange as Excel.Range

LOCAL oColumn as excel.Column

cStr = ""

DIMENSION aSelections[1]

aSelections=""

THIS.PARENT._FIELDMOVER1.getselections(@aSelections)

for i = 1 to ALEN(aSelections)

cStr = cStr+aSelections[m.i]+","

endfor

if len(cStr) > 0

cStr = left(cStr, len(cStr)-1) && remove last comma

ENDIF

THIS.PARENT.xrtnvar = cStr && xrtnvar is a property of my modal form class

for returning results

SELECT &cStr. FROM mycur INTO CURSOR my4xls

SELECT my4xls

IF RECCOUNT() > 0 THEN

cFile = PUTFILE('What name?',ALIAS(),'XLS')

COPY TO (cFile) TYPE XL5

* Open the Spreadsheet with nice column widths

WAIT WINDOW 'Creating Spreadsheet' TIMEOUT .5

oXL = CREATEOBJECT("excel.application")

DO WHILE NOT FILE(cFile)

INKEY(.5)

FLUSH

ENDDO

* oXL.displayalerts = .F.

oXL.workbooks.open (cFile)

oRange = oXL.Range([A1], oXL.ActiveCell.SpecialCells(11))

FOR EACH oColumn IN oRange.Columns

IF ISNULL(oColumn.NumberFormat) && future enhancement -

actually use afields values on source cursor for proper formatting

oColumn.NumberFormat = "Dd-Mm-yyyy"

ENDIF

oColumn.EntireColumn.AutoFit

ENDFOR

oXL.Visible = .t.

oRange = .NULL.

oXL = .NULL.

ELSE

WAIT WINDOW 'Unable to execute Query'

ENDIF

thisform.release

Andrew Davies  MBCS CITP

  - AndyD        8-)#

**********************************************************************

Manchester City Council supports Smokefree England - 1 July 2007

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.

This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses.

Please contact internet.administrators@manchester.gov.uk with any queries.

**********************************************************************

Andy Davies wrote:

> I do it the other way and use vfp to 'push' data to Excel

>

> If anyone's interested here is how I do it:

>

> My main form has a dataObj with properties for the database connection and

> the name of a view or table of de-normalised data (I'm using a backend

> database but the same techniques would work for fox data).

> An option on the main form (menu, button, click event, w.h.y.) calls a

> modal form with a vfp field picker object on it, the user selects the

> fields they want and click a button to create a spreadsheet from the data.

>

...

..

.

> IF RECCOUNT() > 0 THEN

> cFile = PUTFILE('What name?',ALIAS(),'XLS')

> COPY TO (cFile) TYPE XL5

>

(snipped)

I was rereading old mails of interest and I begun wondering.

The COPY TO ... TYPE XL5 will only copy about 16000 records (VFP6). Is

there a way to override this and copy more (or up to 64000)?

Hi Ricardo,

if you keep on reading the old emails in the same thread I think you find

the answes (iirc one would be upgrade to Excel 2007)

Andrew Davies  MBCS CITP

  - AndyD        8-)#

Ricardo Aráoz

<ricaraoz@gmail.c To: profoxtech@leafe.com

om> cc:

Sent by: Subject: Re: Using Excel to handle VPF data

profoxtech-bounce

s@leafe.com

26/08/2007 13:42

Please respond to

profox

Andy Davies wrote:

> I do it the other way and use vfp to 'push' data to Excel

>

> If anyone's interested here is how I do it:

>

> My main form has a dataObj with properties for the database connection

and

> the name of a view or table of de-normalised data (I'm using a backend

> database but the same techniques would work for fox data).

> An option on the main form (menu, button, click event, w.h.y.) calls a

> modal form with a vfp field picker object on it, the user selects the

> fields they want and click a button to create a spreadsheet from the

data.

>

...

..

.

> IF RECCOUNT() > 0 THEN

> cFile = PUTFILE('What name?',ALIAS(),'XLS')

> COPY TO (cFile) TYPE XL5

>

(snipped)

I was rereading old mails of interest and I begun wondering.

The COPY TO ... TYPE XL5 will only copy about 16000 records (VFP6). Is

there a way to override this and copy more (or up to 64000)?

[excessive quoting removed by server]

Andy Davies wrote:

> Hi Ricardo,

> if you keep on reading the old emails in the same thread I think you find

> the answes (iirc one would be upgrade to Excel 2007)

>

> Andrew Davies MBCS CITP

> - AndyD 8-)#

>

>

Pity it's not my company.

But weren't some blokes saying they had issues with office 2007? About

conversion from older versions and stuff? Or are my arteries blocking again?

Andy Davies wrote:

> Hi Ricardo,

> if you keep on reading the old emails in the same thread I think you find

> the answes (iirc one would be upgrade to Excel 2007)

>

> Andrew Davies MBCS CITP

> - AndyD 8-)#

>

>

Just one more thing.

I think it's not excel the problem but VFP6. Excel can get up to 65000

lines per page, it's VFP that's not copying more than 16000.

Earlier versions of Excel only supported 16K rows in a worksheet. 95 &

97 come to mind. I'm not sure when they bumped it up to 64K (2000, 2002,

2003).

Ricardo Aráoz wrote:

> Just one more thing.

> I think it's not excel the problem but VFP6. Excel can get up to 65000

> lines per page, it's VFP that's not copying more than 16000

--

Richard Kaye

Vice President

Artfact/RFC Systems

Voice: 617.219.1038

Fax: 617.219.1001

For the fastest response time, please send your support

queries to:

Technical Support - techsupport@rfcsystems.com

Australian Support - ausupport@rfcsystems.com

Internet Support - websupport@rfcsystems.com

All Other Requests - request@rfcsystems.com

---------------------------------------------------------

---------------------------------------------------------

On 8/28/07, Richard Kaye <rkaye@artfact.com> wrote:

> Earlier versions of Excel only supported 16K rows in a worksheet. 95 &

> 97 come to mind. I'm not sure when they bumped it up to 64K (2000, 2002,

> 2003).

Speaking of which, I noticed yesterday that Office 2007 no longer has

'.dbf' available under the Save As dialog.

--

Paul

Paul Hill wrote:

> On 8/28/07, Richard Kaye <rkaye@artfact.com> wrote:

>

>> Earlier versions of Excel only supported 16K rows in a worksheet. 95 &

>> 97 come to mind. I'm not sure when they bumped it up to 64K (2000, 2002,

>> 2003).

>>

>

> Speaking of which, I noticed yesterday that Office 2007 no longer has

> '.dbf' available under the Save As dialog.

>

>

I'm not surprised.

--

Michael J. Babcock, MCP

MB Software Solutions, LLC

http://mbsoftwaresolutions.com

http://fabmate.com

"Work smarter, not harder, with MBSS custom software solutions!"