How to Export to Excel

HMG Samples and Enhancements

Moderator: Rathinagiri

User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

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: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

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: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

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: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

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: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

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: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: How to Export to Excel

Post by sudip »

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: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

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: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: How to Export to Excel

Post by sudip »

Hello Marek,

I found some following error during running my changed software :-
ExcelError.jpg
ExcelError.jpg (48.97 KiB) Viewed 10099 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 738 times
With best regards.

Sudip
With best regards,
Sudip
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: How to Export to Excel

Post by mol »

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: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: How to Export to Excel

Post by sudip »

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