Excel SaveAs

[Ugo Garombo]

Dear friends,
I have a problem with a saveas excel function:
This is the test:

#include "FiveWin.ch"

PROCEDURE Main

LOCAL nError

IF ( nError := SetExcel() ) > 0
DO CASE
CASE nError == 1
MsgStop( "Plese install excel ;-)", "Error" )
CASE nError == 2
MsgStop( "Errore durante la creazione del foglio", "Terminata
elaborazione" )
CASE nError == 3
MsgStop( "Errore durante il riempimento del foglio",
"Terminata elaborazione" )
CASE nError == 4
MsgStop( "Errore durante l'impostazione di stampa",
"Terminata elaborazione" )
ENDCASE
ELSE
MsgInfo( "TUTTO OK" )
ENDIF

RETURN

//------------------------------------------------------------------------------

STATIC FUNCTION SetExcel()

LOCAL oExcel, oSheet := {}, acName := {}, aTitle := {}, nPos := 1,
anRiga := {}, i, u, nTitle := 5, nX

FOR i := 1 TO nTitle
AAdd( aTitle, "Title" + PadL( i, 3, " " ) )
NEXT
// Connessione Excel
TRY
oExcel := CREATEOBJECT( "Excel.Application" )
CATCH
RETURN 1
END
// Impostazione Fogli
TRY
FOR nPos := 1 TO 3
AAdd( oSheet, oExcel:WorkBooks:Add():WorkSheets(1) )
AAdd( acName, "TestExcel" )
oSheet[nPos]:Name := "TestExcel"
// Aggiungo le intestazioni
FOR i := 1 TO nTitle
oSheet[nPos]:Cells( 1, i ):Value := aTitle[ i ]
NEXT
NEXT
CATCH
// In caso di errore...
// oExcel:QUIT()
oExcel:Set( "Visible", .T.)
RETURN 2
END

// Caricamento dati
TRY
FOR nPos := 1 TO 3
// Imposto la riga di lavoro
AAdd( anRiga, 1 )
FOR u = 1 TO 60
anRiga[nPos] ++
FOR i := 1 TO nTitle
oSheet[ nPos ]:Cells( anRiga[ nPos ], I ):Value := "["
+Chr( 64 + HB_RandomInt( 8 ) ) + ":" + LTrim( Str( u, 0 ) ) + "]"
NEXT
NEXT
NEXT
CATCH
// In caso di errore...
// oExcel:QUIT()
oExcel:Set( "Visible", .T. )
RETURN 3
END

/*
How to save?
*/
IF lSave
// oSheet:SaveAs( oObj:cFileXls )
// oExcel:SaveAs( oObj:cFileXls )
AEval( oSheet, {| a | a:SaveAs( oObj:cFileXls ) } ) // *1 This is
the line 456 of my EsportaExcel() Function.
// MsgInfo( oObj:cFileXls, "salvato" )
ENDIF

/*
*1 The oObj:cFileXls is the full path and name of the file.
the line 456 work ok in Excel 2007 but in Excel 2003 create an error:

Descrizione: Error Excel.Application:WORKBOOKS:ADD:WORKSHEETS/3
DISP_E_MEMBERNOTFOUND: SAVEAS
Args:
[ 1] = C C:\WORK\SRC-WIN\TIMBRA2\Esporta\0005_20080908.xls

Stack Calls
===========
Called from: win32ole.prg => TOLEAUTO:SAVEAS(0)
Called from: Timbra13.prg => (b)ESPORTAEXCEL(456)
*/

RETURN 0

Thanks in advance for your help.
Best regards,
Ugo

[Enrico Maria Giordano]

"Ugo Garombo" <gar...@libero.it> ha scritto nel messaggio
news:FpWBk.67378$Ca.1...@twister2.libero.it...

> /*
> How to save?
> */
> IF lSave
> // oSheet:SaveAs( oObj:cFileXls )
> // oExcel:SaveAs( oObj:cFileXls )

Try

oExcel:ActiveWorkBook:SaveAs( oObj:cFileXls )

EMG

--
EMAG Software Homepage: http://www.emagsoftware.it
The EMG's ZX-Spectrum Page: http://www.emagsoftware.it/spectrum
The Best of Spectrum Games: http://www.emagsoftware.it/tbosg
The EMG Music page: http://www.emagsoftware.it/emgmusic

[reina...@gmail.com]

Try this code:

I corrected it, and it worked fine for me.

procedure main()
? setexcel()
/* returned 0, and genereted 3 files called "testExcel_1.xls",
"testExcel_2.xls" and "testExcel_3.xls", inside the specified
folder */
// note: the specified folder in my case was "c:\xharbour\tests"
// please change the path if you want.

return
static function setexcel()
local lsave := .t.
local oexcel, osheet := {}, acname := {}, ;
atitle := {}, npos := 1, anriga := {}, ;
i, u, ntitle := 5, nx
for i := 1 to ntitle
aadd( atitle, "title" + padl( i, 3, " " ) )
next
// connessione excel
try
oexcel := createobject( "excel.application" )
catch
return 1
end
// impostazione fogli
try
for npos := 1 to 3
aadd( osheet, oexcel:workbooks:add():worksheets(npos) )
aadd( acname, "testexcel" )
osheet[npos]:name := "testexcel_"+alltrim(str(npos))
// aggiungo le intestazioni
for i := 1 to ntitle
osheet[npos]:cells( 1, i ):value := atitle[ i ]
next
next
catch
// in caso di errore...
// oexcel:quit()
oexcel:set( "visible", .t.)
return 2
end
// caricamento dati
try
for npos := 1 to 3
// imposto la riga di lavoro
aadd( anriga, 1 )
for u = 1 to 60
anriga[npos] ++
for i := 1 to ntitle
osheet[ npos ]:cells( anriga[ npos ], i ):value := "["
+chr( 64 + b_randomint( 8 ) ) + ":" + ltrim( str( u, 0 ) ) + "]"
next
next
next
catch
// in caso di errore...
// oexcel:quit()
oexcel:set( "visible", .t. )
return 3
end
/*
how to save?
*/
if lsave
oexcel:displayalerts := .f.
aeval( osheet, {| a | a:saveas( "c:\xharbour\tests
\"+a:name ) } )
endif
oExcel:Quit()
return 0

Reinaldo Henrique

[reina...@gmail.com]

Please, try the code bellow, that I corrected.
Worked ok, and genereted 3 files called
"testExcel_1.xls","testExcel_2.xls"
and "testExcel_3.xls" inside the specified folder.

procedure main()
setexcel()

+chr( 64 + hb_randomint( 8 ) ) + ":" + ltrim( str( u, 0 ) ) + "]"

next
next
next
catch
// in caso di errore...
// oexcel:quit()
oexcel:set( "visible", .t. )
return 3
end
/*
how to save?
*/
if lsave
oexcel:displayalerts := .f.
aeval( osheet, {| a | a:saveas( "c:\xharbour\tests
\"+a:name ) } )
endif
oExcel:Quit()
return 0

Reinaldo Henrique

On 23 set, 07:41, "Enrico Maria Giordano"
<e.m.giord...@emagsoftware.it> wrote:
> "Ugo Garombo" <garo...@libero.it> ha scritto nel messaggionews:FpWBk.67378$Ca.1...@twister2.libero.it...

[Ugo Garombo]

Enrico,

> oExcel:ActiveWorkBook:SaveAs( oObj:cFileXls )

For me work well,
thank you.

Regards,
Ugo

[Ugo Garombo]

Dear Reinaldo Henrique,

> Please, try the code bellow, that I corrected.
> Worked ok, and genereted 3 files called
> "testExcel_1.xls","testExcel_2.xls"
> and "testExcel_3.xls" inside the specified folder.

I necessity of one file with all sheet inside.

Regards,
Ugo