INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

excel automation in vfp

excel automation in vfp

(OP)
Hi expert,


Any one have a idea how can we write multiple table in single excel file's different sheets. Using VFP

RE: excel automation in vfp

You start with something like this:

CODE

OEXCEL = CREATEOBJECT("Excel.Application")
				* make excel visible during development
				OEXCEL.VISIBLE = .T.
				OEXCEL.WORKBOOKS.ADD
				OEXCEL.DISPLAYALERTS = .F.
				OEXCEL.CELLS(1,1).SELECT 

You use something like this to add worksheets (typically you get three, with the defaults for Excel, when creating a work book)

CODE

OEXCEL.ActiveWorkBook.Sheets.Add 

Lastly you need to save it and release the object

CODE

OEXCEL.ACTIVEWORKBOOK.SAVEAS("c:\myfolderxmyfile.xls", -4143)
				OEXCEL.QUIT
				RELEASE OEXCEL 

I forget what the -4143 is - I think it might be the file type.

Best way to learn the techniques is to record macros in Excel and then convert them to VFP.

Main snag is that Excel supports NAMED parameters (no specific order) and VFP does them in a specific order without names...

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: excel automation in vfp

Thank you
xlWorkbookNormal = -4143

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: excel automation in vfp

(OP)
very very thanks to GriffMG and mgagnon


but my problem is different let I am showing bellow


master.xlsx is already present in machine and it has sheets a,b,c,d and I want write data from cursors a,b,c,d

and also set format a column (e.g. custom format dd-mmm-yyyy). Please mention that excel should by installed or not on machine if not so how can we use excel automation using VFP.



regards



RE: excel automation in vfp

Hi

You do have to have Excel on the target machine, no Excel, no automation - Office 365 will only work if you have a locally installed copy of Excel.

Are you trying to export from a number of cursors straight into the excel sheets within a workbook? I don't think there is a native VFP command that can do that.

If you want to do so, you will have to traverse the cursor, and the appropriate worksheet at the same time (in sync - so to speak).

CODE

PRIVATE MyRow
OEXCEL = CREATEOBJECT("Excel.Application")
* make excel visible during development
OEXCEL.VISIBLE = .T.
OEXCEL.WORKBOOKS.ADD
OEXCEL.DISPLAYALERTS = .F.

SELECT myTableA
GO top

OEXCEL.SHEETS(1).SELECT
MyRow = 1

DO WHILE .not. EOF()
	OEXCEL.CELLS(MyRow,1).Value = myTableA.Field1
	OEXCEL.CELLS(MyRow,2).Value = myTableA.Field2
	OEXCEL.CELLS(MyRow,3).Value = myTableA.Field3
	OEXCEL.CELLS(MyRow,4).Value = myTableA.Field4
	OEXCEL.CELLS(MyRow,5).Value = myTableA.Field5
	OEXCEL.CELLS(MyRow,6).Value = myTableA.Field6
	MyRow = MyRow +1
	SKIP
ENDDO


OEXCEL.ActiveWorkBook.Sheets.Add  && adds new sheet 
OEXCEL.SHEETS(2).SELECT
MyRow = 1

SELECT myTableB
GO top

DO WHILE .not. EOF()
	OEXCEL.CELLS(MyRow,1).Value = myTableB.Field1
	OEXCEL.CELLS(MyRow,2).Value = myTableB.Field2
	OEXCEL.CELLS(MyRow,3).Value = myTableB.Field3
	OEXCEL.CELLS(MyRow,4).Value = myTableB.Field4
	OEXCEL.CELLS(MyRow,5).Value = myTableB.Field5
	OEXCEL.CELLS(MyRow,6).Value = myTableB.Field6
	MyRow = MyRow +1
	SKIP
ENDDO

OEXCEL.ActiveWorkBook.Sheets.Add  && adds new sheet 
OEXCEL.SHEETS(3).SELECT
MyRow = 1

SELECT myTableC
GO top

DO WHILE .not. EOF()
	OEXCEL.CELLS(MyRow,1).Value = myTableC.Field1
	OEXCEL.CELLS(MyRow,2).Value = myTableC.Field2
	OEXCEL.CELLS(MyRow,3).Value = myTableC.Field3
	OEXCEL.CELLS(MyRow,4).Value = myTableC.Field4
	OEXCEL.CELLS(MyRow,5).Value = myTableC.Field5
	OEXCEL.CELLS(MyRow,6).Value = myTableC.Field6
	MyRow = MyRow +1
	SKIP
ENDDO 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: excel automation in vfp

The formatting you can do as you traverse a column like this:

CODE

OEXCEL.CELLS(MyRow,6).SELECT
OEXCEL.Selection.NumberFormat = "d-mmm-yy" 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: excel automation in vfp

Griff, a WITH/ENDWITH block around all those Excel object references will make a DRAMATIC performance difference.

Even better? If you're plopping in a whole table use _VFP.DataToClip() and then excel.Paste(). The clipboard is a lot faster at moving a block of tabular data than row-by-row and column-by-column.

RE: excel automation in vfp

With/EndWith - never knew that would make a performance difference, I'll have to test that.

It's never going to be quick anyway - Excel automation is slow - and the examples were just to show the OP the way it
could be done, not really as working source code.

Off to test

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: excel automation in vfp

I can find no firm evidence that a with/endwith improves performance - perhaps readability, MSDN says it does help, Craig Bernston says it doesn't.

I imagine the variable resolution for a COM reference might be slightly better, but who knows.

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: excel automation in vfp

(OP)
thanks for every one for replying me.

Please now please tell me that where I collect All Formatting commands like "OEXCEL.Selection.NumberFormat = "d-mmm-yy" "

RE: excel automation in vfp

Start a macro in Excel, format a cell and select the custom option you want, then you should see the various strings you might use there.

Stop the macro recording when you have what you want and then edit the macro to see what Excel did.

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: excel automation in vfp

Quote:

please tell me that where I collect All Formatting commands like "OEXCEL.Selection.NumberFormat = "d-mmm-yy" "

1. Start Excel

2. Launch the Visual Basic Editor (ALT+F11 in some versions)

3. Launch the Object Browser (View / Object Browser or F2 in some versions)

4. In the <<All Libraries>> combo, select Excel

5. In the Classes list, select the class of interest.

6. In the Members list, select the property, event or method of interest.

7. Read the small Help panel at the bottom of the window, or press F1 to get more detailed help.

So, using your example, you would select the Application class (which corresponds to VFP's oExcel). Then select Selection. You will see that that is an object in its own right. The Help tells you that it is an object of type Range. So repeat the above steps for the Range class, and select NumberFormat. The Help will will tell you that NumberFormat uses the same codes as the Format Codes option in the Format Cells dialogue. You know what those are, so you now have the information you need.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: excel automation in vfp

I tested the speed issue back when writing the Automation book:

"In our tests, a fairly simple example that queried about a dozen properties at four levels below the Application object ran roughly twice as fast using nested WITHs as it did addressing each property directly."

Tamar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close