Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Excel automation within VFP

1,721 views
Skip to first unread message

Freightliner

unread,
Dec 21, 2009, 4:45:56 PM12/21/09
to
Hi,

I am not sure this is the right group for this.
My VFP 9 application runs Excel automation.
In 29 steps, a sheet is formatted in a certain way.
I noticed that some steps take considerably longer time than others,
and I wonder if there's other faster ways to achieve the same goal.

I post the code below.

The most time consuming steps are 17 (columns resizing) and 21 (add
footer and page numbers).
They take twice longer than steps 3, 4, 5, 23.
They take 10 times longer than steps 1, 6, 7, 8, 19, 24, 25, 26, 27,
28.
They take 90 times longer than steps 10, 12, 13, 15, 20, 29.

Fixing somehow steps 17, 21, 3, 4, 5 and 23 would improve speed.
Thank you for help, and sorry in case this is not the right group.

* 1
oSheet = .Worksheets.Add()
_VFP.DataToClip(,,3)
oSheet.Paste()
oSheet.Name = "#" + TRIM(vstore)

* 2
.Range("A:Z").Font.Size = 8

* 3
.ActiveWorkbook.ActiveSheet.Pagesetup.LeftMargin = 36 && era 18
.ActiveWorkbook.ActiveSheet.Pagesetup.RightMargin = 27 && era 18

* 4
.ActiveWorkbook.ActiveSheet.Pagesetup.TopMargin = 36
.ActiveWorkbook.ActiveSheet.Pagesetup.BottomMargin = 54

* 5
.ActiveWorkbook.ActiveSheet.Pagesetup.HeaderMargin = 18
.ActiveWorkbook.ActiveSheet.Pagesetup.FooterMargin = 18

* 6
.range("A:D").delete

* 7
.range("J:K").delete

* 8
.range("A:A,A1").numberformat = "0"

* 9
.range("D:D,D1").numberformat = "0000"

* 10
.range("G:I").numberformat = "#,##0.00"

* 11
.range("A1").value = "UPC"
.range("B1").value = "Style"
.range("C1").value = "Style Desc"
.range("D1").value = "WP Color"
.range("E1").value = "Size"
.range("F1").value = "Qty"
.range("G1").value = "Cost $"
.range("H1").value = "Amount $"
.range("I1").value = "Retail $"
*.range("J1").value = "Diff $"

* 12
.range("A1:I1").Borders.LineStyle = 1

* 13
* Left: -4131, Center = -4108, Right = -4152
.range("F1:J1").HorizontalAlignment = -4108

* 14
.ActiveWorkbook.ActiveSheet.pagesetup.printtitlerows = "$A1:$J1"

* 15
.range("A:A,B:B,D:D,E:E").horizontalalignment = -4131

* 16
.range("J:J,J1").horizontalalignment = -4152

* 17
.Columns("A:A").ColumnWidth = 12 && UPC was 13
.Columns("B:B").ColumnWidth = 15 && Style was 13
.Columns("C:C").ColumnWidth = 16 && Styledesc
.Columns("D:D").ColumnWidth = 7 && Wp color
.Columns("E:E").ColumnWidth = 5 && Size
.Columns("F:F").ColumnWidth = 4 && Qty
.Columns("G:G").ColumnWidth = 7 && Cost was 8
.Columns("H:H").ColumnWidth = 10 && Amount
.Columns("I:I").ColumnWidth = 8 && Retail
.Columns("J:J").ColumnWidth = 6

* 18
totqtycell = "F"+ALLTRIM(STR(storereccount+2))
.range(totqtycell).Select
.range(totqtycell).FormulaR1C1 = "=SUM(R[-"+ALLTRIM(STR(storereccount))
+"]C:R[-1]C)"
.range(totqtycell).Select
.range(totqtycell).font.bold = .T.
.range(totqtycell).font.size = 9

* 19
totamtcell = "H"+ALLTRIM(STR(storereccount+2))
.range(totamtcell).Select
.range(totamtcell).FormulaR1C1 = "=SUM(R[-"+ALLTRIM(STR(storereccount))
+"]C:R[-1]C)"
.range(totamtcell).Select
.range(totamtcell).font.bold = .T.
.range(totamtcell).font.size = 9

* 20
.Range("A1").Select

* 21
.ActiveWorkbook.ActiveSheet.PageSetup.PrintArea = ""
.ActiveWorkbook.ActiveSheet.PageSetup.RightHeader = ""
.ActiveWorkbook.ActiveSheet.PageSetup.LeftFooter = "FLI" + ' ' + dts
.ActiveWorkbook.ActiveSheet.PageSetup.RightFooter = "&P/&N" && es:
1/3

* 22
.selection.entirerow.insert()
.Range("A1").value = COMPNAME
.Range("A1").font.size = 14
.Range("A1").font.bold = .T.
.Range("A2").select

* 23
.selection.entirerow.insert()
.Range("A2").value = v_tpc + SPACE(22-LEN(tpc)) + 'PO #' + v_po
.Range("E2").value = v_dn + v_btl
.Range("A2:E2").font.size = 11
.Range("A2:E2").font.bold = .T.
.Range("A3").select
.selection.entirerow.insert()
.Range("A3:L3").font.size = 9
.Range("A3:L3").font.bold = .F.

* 24
.range("A4").select
.selection.entirerow.insert()
.range("A4").value = v_pod + v_pot + v_vn

* 25
.range("A5").select
.selection.entirerow.insert()
.range("A5").value = v_sh + v_snbd + v_drd + v_dndbd + v_psd + v_cad +
v_dndad + v_cindbd

* 26
IF NOT (v_pgn == '' AND v_an == '' AND v_smop == '')
.range("A6").select
.selection.entirerow.insert()
.range("A6:L6").font.size = 8
.range("A6").value = v_pgn + v_an + v_smop
ENDIF

* 27
IF NOT(v_tt == '' AND v_tbdc == '' AND v_tdp1 == '' AND v_tdp2 == ''
AND v_tdduda == '' ;
AND v_tddadu == '' AND v_tndd =e= '' AND v_tnd == '' AND v_tda == '')
.range("A7").select
.selection.entirerow.insert()
.range("A7:L7").font.size = 8
.range("A7").value = v_tt + v_desc_a + v_tbdc + v_desc_b ;
+ IIF(v_tdp1 == v_tdp2, v_tdp1, v_tdp1 +
v_tdp2) ;
+ v_tdduda + v_tddadu + v_tndd + v_tnd + v_tda
ENDIF

* 28
.Rows(.selection.Row+1).Insert

* 29
.range("J1").select

Dan Freeman

unread,
Dec 22, 2009, 3:45:22 AM12/22/09
to
Looks like code adapted from a macro recording. That code is seldom
optimal and is usually tightly bound to the visual representation of the
actions.

It's the visual hoohaw (specifically painting the screen) that slows
down Excel so much.

Note that you're calling Select(). There's no reason to do that except
that it's the way the macro recorder did things. It usually gets a Range
object from the current selection, but you can also get a Range from any
Cells(x,y) reference. You DON'T NEED the selection, and that's the
visible thing that slows up the works.

Dan

Jürgen Wondzinski

unread,
Dec 22, 2009, 4:20:20 AM12/22/09
to
Hi Freightliner

Also make sure that Excel isn't .VISIBLE when doing the formatting stuff.


--

wOOdy
Visual FoxPro Evangelist
Microsoft "Most Valuable Professional" 1996 to 2009

"*��)
�.���.�*��) �.�*�)
(�.��. (�.�` *
..�`.Visual FoxPro: It's magic !
(�.�``��*

Freightliner

unread,
Dec 22, 2009, 9:02:40 AM12/22/09
to
On Dec 22, 3:45 am, Dan Freeman <s...@microsoft.com> wrote:
> Looks like code adapted from a macro recording. That code is seldom
> optimal and is usually tightly bound to the visual representation of the
> actions.
>
> It's the visual hoohaw (specifically painting the screen) that slows
> down Excel so much.
>
> Note that you're calling Select(). There's no reason to do that except
> that it's the way the macro recorder did things. It usually gets a Range
> object from the current selection, but you can also get a Range from any
> Cells(x,y) reference. You DON'T NEED the selection, and that's the
> visible thing that slows up the works.
>
> Dan

Select() is not called in the 5 slowest steps (17, 21, 3, 4, 5).
What seems to be the most time consuming is resizing columns (17) ,
adding page footer (21), setting page margins (3, 4, 5).

I run it with .visible = .F. and the speed is more or less the same.


Freightliner

unread,
Dec 22, 2009, 9:03:03 AM12/22/09
to
On Dec 22, 4:20 am, Jürgen Wondzinski <juer...@wondzinski.de> wrote:
> Hi Freightliner
>
> Also make sure that Excel isn't .VISIBLE when doing the formatting stuff.
>
> --
>
> wOOdy


I already run it with .visible = .F., and the speed is more or less
the same.

Juri Shutenko

unread,
Jan 5, 2010, 5:13:30 AM1/5/10
to
Hi!
Just tested your code (with the some corrections inside range() - )
Working without any delays at the step 17.
(Windows 7, Excel 2007, VFP9 SP2)
Check your code - range() parameters too.

BR
Juri

"Freightliner" <freightl...@hotmail.com> сообщил(а) в новостях
следующее:5f5e4935-af0a-45d1...@x15g2000vbr.googlegroups.com...

Freightliner

unread,
Feb 9, 2010, 1:01:50 PM2/9/10
to
On Jan 5, 5:13 am, "Juri Shutenko" <juri_shute...@hotmai.com> wrote:
> Hi!
> Just tested your code (with the some corrections inside range() - )
> Working without any delays at the step 17.
> (Windows 7, Excel 2007, VFP9 SP2)
> Check your code - range() parameters too.


Did you calculate the time using time() as I did, or just looking at
the screen?
All delays are minimal, but make up quite a long wait, all together.

benjie albarracin

unread,
Aug 6, 2010, 1:09:08 AM8/6/10
to
how do i see steps 19 to 29?

Freightliner wrote:

Excel automation within VFP
21-Dec-09

Hi,

I am not sure this is the right group for this.
My VFP 9 application runs Excel automation.
In 29 steps, a sheet is formatted in a certain way.
I noticed that some steps take considerably longer time than others,

and I wonder if there is other faster ways to achieve the same goal.

I post the code below.

The most time consuming steps are 17 (columns resizing) and 21 (add
footer and page numbers).
They take twice longer than steps 3, 4, 5, 23.
They take 10 times longer than steps 1, 6, 7, 8, 19, 24, 25, 26, 27,
28.
They take 90 times longer than steps 10, 12, 13, 15, 20, 29.

Fixing somehow steps 17, 21, 3, 4, 5 and 23 would improve speed.
Thank you for help, and sorry in case this is not the right group.

* 1
oSheet = .Worksheets.Add()
_VFP.DataToClip(,,3)
oSheet.Paste()
oSheet.Name = "#" + TRIM(vstore)

* 2
..Range("A:Z").Font.Size = 8

* 3
..ActiveWorkbook.ActiveSheet.Pagesetup.LeftMargin = 36 && era 18
..ActiveWorkbook.ActiveSheet.Pagesetup.RightMargin = 27 && era 18

* 4
..ActiveWorkbook.ActiveSheet.Pagesetup.TopMargin = 36
..ActiveWorkbook.ActiveSheet.Pagesetup.BottomMargin = 54

* 5
..ActiveWorkbook.ActiveSheet.Pagesetup.HeaderMargin = 18
..ActiveWorkbook.ActiveSheet.Pagesetup.FooterMargin = 18

* 6
..range("A:D").delete

* 7
..range("J:K").delete

* 8
..range("A:A,A1").numberformat = "0"

* 9
..range("D:D,D1").numberformat = "0000"

* 10
..range("G:I").numberformat = "#,##0.00"

* 11
..range("A1").value = "UPC"
..range("B1").value = "Style"
..range("C1").value = "Style Desc"
..range("D1").value = "WP Color"
..range("E1").value = "Size"
..range("F1").value = "Qty"
..range("G1").value = "Cost $"
..range("H1").value = "Amount $"
..range("I1").value = "Retail $"


*.range("J1").value = "Diff $"

* 12
..range("A1:I1").Borders.LineStyle = 1

* 13
* Left: -4131, Center = -4108, Right = -4152

..range("F1:J1").HorizontalAlignment = -4108

* 14
..ActiveWorkbook.ActiveSheet.pagesetup.printtitlerows = "$A1:$J1"

* 15
..range("A:A,B:B,D:D,E:E").horizontalalignment = -4131

* 16
..range("J:J,J1").horizontalalignment = -4152

* 17
..Columns("A:A").ColumnWidth = 12 && UPC was 13
..Columns("B:B").ColumnWidth = 15 && Style was 13
..Columns("C:C").ColumnWidth = 16 && Styledesc
..Columns("D:D").ColumnWidth = 7 && Wp color
..Columns("E:E").ColumnWidth = 5 && Size
..Columns("F:F").ColumnWidth = 4 && Qty
..Columns("G:G").ColumnWidth = 7 && Cost was 8
..Columns("H:H").ColumnWidth = 10 && Amount
..Columns("I:I").ColumnWidth = 8 && Retail
..Columns("J:J").ColumnWidth = 6

* 18
totqtycell = "F"+ALLTRIM(STR(storereccount+2))

..range(totqtycell).Select
..range(totqtycell).FormulaR1C1 = "=SUM(R[-"+ALLTRIM(STR(storereccount))

Previous Posts In This Thread:

On Monday, December 21, 2009 5:36 PM
Freightliner wrote:

Excel automation within VFP
Hi,

I am not sure this is the right group for this.
My VFP 9 application runs Excel automation.
In 29 steps, a sheet is formatted in a certain way.
I noticed that some steps take considerably longer time than others,

and I wonder if there is other faster ways to achieve the same goal.

I post the code below.

The most time consuming steps are 17 (columns resizing) and 21 (add
footer and page numbers).
They take twice longer than steps 3, 4, 5, 23.
They take 10 times longer than steps 1, 6, 7, 8, 19, 24, 25, 26, 27,
28.
They take 90 times longer than steps 10, 12, 13, 15, 20, 29.

Fixing somehow steps 17, 21, 3, 4, 5 and 23 would improve speed.
Thank you for help, and sorry in case this is not the right group.

* 1
oSheet = .Worksheets.Add()
_VFP.DataToClip(,,3)
oSheet.Paste()
oSheet.Name = "#" + TRIM(vstore)

* 2
..Range("A:Z").Font.Size = 8

* 3
..ActiveWorkbook.ActiveSheet.Pagesetup.LeftMargin = 36 && era 18
..ActiveWorkbook.ActiveSheet.Pagesetup.RightMargin = 27 && era 18

* 4
..ActiveWorkbook.ActiveSheet.Pagesetup.TopMargin = 36
..ActiveWorkbook.ActiveSheet.Pagesetup.BottomMargin = 54

* 5
..ActiveWorkbook.ActiveSheet.Pagesetup.HeaderMargin = 18
..ActiveWorkbook.ActiveSheet.Pagesetup.FooterMargin = 18

* 6
..range("A:D").delete

* 7
..range("J:K").delete

* 8
..range("A:A,A1").numberformat = "0"

* 9
..range("D:D,D1").numberformat = "0000"

* 10
..range("G:I").numberformat = "#,##0.00"

* 11
..range("A1").value = "UPC"
..range("B1").value = "Style"
..range("C1").value = "Style Desc"
..range("D1").value = "WP Color"
..range("E1").value = "Size"
..range("F1").value = "Qty"
..range("G1").value = "Cost $"
..range("H1").value = "Amount $"
..range("I1").value = "Retail $"


*.range("J1").value = "Diff $"

* 12
..range("A1:I1").Borders.LineStyle = 1

* 13
* Left: -4131, Center = -4108, Right = -4152

..range("F1:J1").HorizontalAlignment = -4108

* 14
..ActiveWorkbook.ActiveSheet.pagesetup.printtitlerows = "$A1:$J1"

* 15
..range("A:A,B:B,D:D,E:E").horizontalalignment = -4131

* 16
..range("J:J,J1").horizontalalignment = -4152

* 17
..Columns("A:A").ColumnWidth = 12 && UPC was 13
..Columns("B:B").ColumnWidth = 15 && Style was 13
..Columns("C:C").ColumnWidth = 16 && Styledesc
..Columns("D:D").ColumnWidth = 7 && Wp color
..Columns("E:E").ColumnWidth = 5 && Size
..Columns("F:F").ColumnWidth = 4 && Qty
..Columns("G:G").ColumnWidth = 7 && Cost was 8
..Columns("H:H").ColumnWidth = 10 && Amount
..Columns("I:I").ColumnWidth = 8 && Retail
..Columns("J:J").ColumnWidth = 6

* 18
totqtycell = "F"+ALLTRIM(STR(storereccount+2))

..range(totqtycell).Select
..range(totqtycell).FormulaR1C1 = "=SUM(R[-"+ALLTRIM(STR(storereccount))

On Tuesday, December 22, 2009 3:45 AM
Dan Freeman wrote:

Looks like code adapted from a macro recording.
Looks like code adapted from a macro recording. That code is seldom
optimal and is usually tightly bound to the visual representation of the
actions.

it is the visual hoohaw (specifically painting the screen) that slows
down Excel so much.

Note that you are calling Select(). There is no reason to do that except
that it is the way the macro recorder did things. It usually gets a Range


object from the current selection, but you can also get a Range from any

Cells(x,y) reference. You DON'T NEED the selection, and that is the


visible thing that slows up the works.

Dan


Freightliner wrote:

On Tuesday, December 22, 2009 4:20 AM
J?rgen_Wondzinski wrote:

Hi FreightlinerAlso make sure that Excel is not .
Hi Freightliner

Also make sure that Excel is not .VISIBLE when doing the formatting stuff.


--

wOOdy


Visual FoxPro Evangelist
Microsoft "Most Valuable Professional" 1996 to 2009

"*??)
?.???.?*??) ?.?*?)
(?.??. (?.?` *
...?`.Visual FoxPro: it is magic !
(?.?``??*

On Tuesday, December 22, 2009 12:32 PM
Freightliner wrote:

Select() is not called in the 5 slowest steps (17, 21, 3, 4, 5).
Select() is not called in the 5 slowest steps (17, 21, 3, 4, 5).
What seems to be the most time consuming is resizing columns (17) ,
adding page footer (21), setting page margins (3, 4, 5).

I run it with .visible =3D .F. and the speed is more or less the same.

On Tuesday, December 22, 2009 12:32 PM
Freightliner wrote:

I already run it with .visible =3D .F., and the speed is more or lessthe same.
I already run it with .visible =3D .F., and the speed is more or less
the same.

On Tuesday, January 05, 2010 5:13 AM
Juri Shutenko wrote:

Hi!


Hi!
Just tested your code (with the some corrections inside range() - )
Working without any delays at the step 17.
(Windows 7, Excel 2007, VFP9 SP2)
Check your code - range() parameters too.

BR
Juri

??????????????????:5f5e4935-af0a-45d1...@x15g2000vbr.googlegroups.com...

On Tuesday, February 09, 2010 1:01 PM
Freightliner wrote:

Did you calculate the time using time() as I did, or just looking atthe screen?
Did you calculate the time using time() as I did, or just looking at
the screen?
All delays are minimal, but make up quite a long wait, all together.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Scrolling in WPF Toolkit?s Column Chart
http://www.eggheadcafe.com/tutorials/aspnet/0939d60c-8e17-4a27-b898-1fc772d2d6f6/scrolling-in-wpf-toolkits-column-chart.aspx

0 new messages