excel automation in vfp

[ramanjain]

Hi expert,


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

 

[GriffMG]

You start with something like this:

                                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)

				OEXCEL.ActiveWorkBook.Sheets.Add

Lastly you need to save it and release the object

				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 [Smile]ing

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. ​

 

[Mike Gagnon]

https://msdn.microsoft.com/en-us/library/office/aa221100(v=office.11).aspx

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[GriffMG]

Thank you
xlWorkbookNormal = -4143

Regards

Griff
Keep [Smile]ing

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. ​

 

[ramanjain]

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

 

[GriffMG]

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).

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 [Smile]ing

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. ​

 

[GriffMG]

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

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

Regards

Griff
Keep [Smile]ing

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. ​

 

[Dan Freeman]

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.

 

[GriffMG]

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 [Smile]ing

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. ​

 

[GriffMG]

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 [Smile]ing

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. ​

 

[ramanjain]

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"

 

[GriffMG]

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 [Smile]ing

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. ​

 

[Mike Lewis]

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

 

[TamarGranor]

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