Excel book.SaveAs

[TheVig]

Here's the basic code:

Code:

Copy      Help

function str'sA str&sB str&sC str&sD

str sfile="$desktop$\JVF\Template.xls"
str asheet="Template A"
str bsheet="Template B"

Excel.Application app._create

Excel.Workbook book=app.Workbooks.Open(_s.expandpath(sfile))
ExcelSheet es1.ws=book.Worksheets.Item(asheet)
ExcelSheet es2.ws=book.Worksheets.Item(bsheet)


es1.SetCell(sA 1 1)
es1.SetCell(sB 1 2)
es2.SetCell(sC 1 1)
es2.SetCell(sD 1 2)
book.Save

What I want it to do is a Save As that creates a new spreadsheet that uses the variable sA sB sC sD as the file name.
How do I do this?

Thanks,
TheVig

[Gintaras]

es1.Save(sA)

internally it calls book.SaveAs(sA ...)

[TheVig]

I'm working with some data where I need to write a bunch of rows for an excel file.

Using SetCell takes too long...So I thought I'd write to a tab separated text file, then open and saveas in Excel:
Function OpenSaveExcel

Code:

Copy      Help

str filename="$temp$\data.txt"
filename.expandpath
ExcelSheet es.Init("" 8 filename)
filename.timeformat("$desktop$\{yyyy}-{MM}.xls")
filename.expandpath
es.Save(filename)

Essentially it is using Excel to convert a tab separated text file to convert it to an .xls.

This works, except for that any cell with a formula is saved as its value only, which I need the formulas to remain in tact.

Is there any way to work around this? A better SaveAs excel function? A better way to convert tab separated data to an .xls?

Thanks,
jimmy Vig

[Gintaras]

CSV does not include formulas.

This function is fast.

Member function ExcelSheet.SetCellsFast

Code:

Copy      Help

function ARRAY(str)&a $range ;;range example: F"A1:C{a.len}", press F1 to see more.

;Populates part of worksheet with data from two-dimensional array.

;range examples: "" (used range), "sel" (selection in active sheet), "A1:C3" (range), "A:C" (columns A, B and C), "3:3" (row 3), "A1" (cell), "Named" (named range)

;EXAMPLE
;;/exe 1
;ARRAY(str) a.create(2 100)
;for(_i 0 a.len) a[0 _i]=_i; a[1 _i]=10*_i
;
;ExcelSheet es.Init
;es.SetCellsFast(a F"A1:B{a.len}")


if(!ws) Init

int i j nr nc
Excel.Range r

GetRange(range r nr nc)

ARRAY(VARIANT) av.create(a.len a.len(1))
for i 0 a.len
,for j 0 a.len(1)
,,av[i j]=a[j i]
r.Value=av

err+ E

[TheVig]

When trying to post cells with formulas:
0x80020009, Exception occurred. 0x3EC:

[Gintaras]

On my PC works.

Macro Macro1476

Code:

Copy      Help

for(_i 0 a.len) a[0 _i]=_i; a[1 _i]=F"=A{_i+1}+1"

[TheVig]

Your right, it was my mistake. I had errors in my formulas because I copied and pasted some QM code that had some {formatting} in it and I didn't apply the formatting. So, excel won't let you post incorrect formulas. It works fine now that I made the corrections.

Thanks,
Jimmy Vig

[Gintaras]

With CSV file:

Macro Macro1482

Code:

Copy      Help

str filename="$temp$\data.csv"
filename.expandpath

str s=
;1,=SUM(A1+1)
;2,=A2+1
;note: must not be spaces between separator and =
s.setfile(filename)
;run "excel" F"''{filename}''"; ret

ExcelSheet es.Init("" 8 filename)
filename.timeformat("$desktop$\{yyyy}-{MM}.xls")
filename.expandpath
del- filename; err
;es.Save(filename) ;;does not change file format
es.ws.Application.ActiveWorkbook.SaveAs(filename Excel.xlNormal @ @ @ @ 1)

es.ws.Application.Quit
run filename