ExcelSheet question

[koukie]

How do you get cell value from specific file & specific sheet using ExcelSheet. So far I was only able to get cell value from specific sheet, but not specific to a file. Thanks in advance.

[Gintaras]

Create new member function ExcelSheet.Init2 and paste this.

Code:

Copy      Help

function $book [VARIANT'sheet] [activatebook] [activatesheet]

;Same as Init, but also allows you to specify workbook (file name).

;EXAMPLE
;ExcelSheet es.Init2("Book1.xls" "Sheet2")
;

Excel.Application xlApp._getactive
err
,int h=win; act "+Shell_TrayWnd"; act h
,xlApp._getactive

Excel.Workbook wb=xlApp.Workbooks.Item(book)
if(activatebook) wb.Activate

int good
sel sheet.vt
,case [VT_I4,VT_DISPATCH] good=sheet.lVal
,case VT_BSTR good=sheet.bstrVal.len
if(good)
,ws=wb.Worksheets.Item(sheet)
,if(activatesheet) ws.Activate
else ws=wb.ActiveSheet

err+ end _error

[koukie]

WoW....great!! thanks gintaras. btw ExcelSheet is awesome!

[Nithin]

Hi gintaras,

i would like to how to read/write to a cell value from specific file & specific sheet using ExcelSheet when the excel file is closed

iam able to get/set the cell value when the excel file is open...

thanks,
Nithin

[Gintaras]

You can start Excel invisible, and open the file:

Code:

Copy      Help

str sfile="$personal$\book1.xls"
str ssheet="Sheet2"

Excel.Application app._create
;app.Visible=TRUE ;;uncomment to make Excel visible
Excel.Workbook book=app.Workbooks.Open(_s.expandpath(sfile))
ExcelSheet es.ws=book.Worksheets.Item(ssheet)

;now you can get/set cells using es
str s
es.GetCell(s 2 2)
out s
es.SetCell("newvalue" 2 2)

book.Save
app.Quit

The problem is that, if an error occurs, app.Quit is not executed, and Excel does not exit, and you'll have to end EXCEL process in Task Manager. To solve this problem, use this instead:

Code:

Copy      Help

str sfile="$personal$\book1.xls"
str ssheet="Sheet2"

Excel.Application app._create
atend ExcelQuit &app
;app.Visible=TRUE ;;uncomment to make Excel visible
Excel.Workbook book=app.Workbooks.Open(_s.expandpath(sfile))
ExcelSheet es.ws=book.Worksheets.Item(ssheet)

;now you can get/set cells using es
str s
es.GetCell(s 2 2)
out s
es.SetCell("newvalue" 2 2)

book.Save

Function ExcelQuit

Code:

Copy      Help

function Excel.Application*a
a.Quit

[Nithin]

Thats wonderful...!!

thanks Gintaras....