How to Export to Excel

HMG Samples and Enhancements

Moderator: Rathinagiri

User avatar
mol
Posts: 2879
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 136 times
Been thanked: 76 times
Contact:

Re: How to Export to Excel

Post by mol » Thu Dec 11, 2008 6:55 am

I need to export line by line, because my program must interactively rewrite some cells in worksheet, if data in .dbf was changed - for compatibility with actual system of work in company. Time doesn't matter now - export to excel will be done once a day or week, about 200 rows for day.
But your solution about export to excel is excellent :-)

Thanks very much for your engagement in my problem.

Best regards, Marek

User avatar
mol
Posts: 2879
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 136 times
Been thanked: 76 times
Contact:

Post by mol » Mon Jan 26, 2009 11:49 am

I tried to use Alex Gustov' way to write to Excel, but in HMG function CopyToClipboard does not exist....
How to resolve this problem?

User avatar
mol
Posts: 2879
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 136 times
Been thanked: 76 times
Contact:

Post by mol » Mon Jan 26, 2009 12:16 pm

I found and implement CopyToClipboard
function CopyToClipboard
param cTekst
#define HB_GTI_CLIPBOARDDATA 15
hb_gtInfo( HB_GTI_CLIPBOARDDATA, cTekst )
return

User avatar
mol
Posts: 2879
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 136 times
Been thanked: 76 times
Contact:

Post by mol » Mon Apr 27, 2009 9:06 am

It looks that I'm writing to myself....
But maybe someone will read this post and will have an idea, how to switch off automatic recomputing excel sheet by a procedure.
this recomputing terribly slows down the process off exporting...

Best regards, Marek

User avatar
mol
Posts: 2879
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 136 times
Been thanked: 76 times
Contact:

Post by mol » Mon Apr 27, 2009 9:49 am

I found!!!

I put it of forum, maybe it will be useful for someone...
private xlCalcStatus
// xlCalcStatus - for restoring status after export to excel

#define xlManual -4135

xlCalcStatus := oExcel:Calculation
oExcel:Calculation := xlManual
// ^^^^^^^
// put your export code here

// and now restore settings
oExcel:Calulation := xlCalcStatus

// and I think, it's good idea for recalculating sheet after export
oExcel:Calculate()

Best regards, Marek
Last edited by mol on Tue Apr 28, 2009 5:36 am, edited 2 times in total.

User avatar
sudip
Posts: 1444
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 4 times

Post by sudip » Mon Apr 27, 2009 10:05 am

Hi Marek,

Can you share us some code for this so that I can test :)

Regards.

Sudip
With best regards,
Sudip

User avatar
mol
Posts: 2879
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 136 times
Been thanked: 76 times
Contact:

Post by mol » Mon Apr 27, 2009 11:27 am

I'm working with huge project and it's hard to put the whole code.

The example will be similar to yours program to write dbf to excel.
Only at the beginning you must put lines with setting
oEcel:Calculation := xlManual
and at the end of program restore setting of calculation, ant recalcutate sheet

oExcel:Calulation := xlCalcStatus
oExcel:Calculate()

regards, Marek

User avatar
sudip
Posts: 1444
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 4 times

Post by sudip » Tue Apr 28, 2009 5:10 am

Hello Marek,

I found some following error during running my changed software :-
ExcelError.jpg
ExcelError.jpg (48.97 KiB) Viewed 1391 times
I found that it is due to the statement:-

Code: Select all

	oExcel:Calulation := xlManual 	      
My changed code is given here
Demo.zip
(1.5 KiB) Downloaded 335 times
With best regards.

Sudip
With best regards,
Sudip

User avatar
mol
Posts: 2879
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 136 times
Been thanked: 76 times
Contact:

Post by mol » Tue Apr 28, 2009 5:30 am

Hello Sudip!
Have you seen that you (and me) wrote "CALULATION" not "CALCULATION" !!!
This is the reason of error.
I found it later.
It was casued due haste...

Marek

User avatar
sudip
Posts: 1444
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India
Has thanked: 4 times

Post by sudip » Tue Apr 28, 2009 6:22 am

Hi Marek,

Thanks it's now OK :D And exporting large table becomes very quick :D

Thank you very much :D

And later I must be careful before coping ...

Thank you again very much for sharing this code.

Regards.

Sudip
With best regards,
Sudip

Post Reply