Opening Excel book with variable

[shornw]

I have been tryng different things with this for quite a while now, yet still it eludes me. I want to open a spreadsheet, add two worksheets, hten open the same workbook again and utilise the data on the default sheet.

It works absolutely fine if I use a standard path, however when I try and use a variable it reports @error 2 (File does not exist). This is where I am so far:

Local $wb = FileOpenDialog("Open Excel WorkBook", "C:\Error Reports", "(*.xlsx;*.xls)")
$HV2 = '"' & $wb & '"'

$wb = _ExcelBookOpen($wb)
_ExcelSheetAddNew($wb, "Servers")
_ExcelSheetAddNew($wb, "Workstations")
_ExcelSheetActivate($wb, "Workstations")

MsgBox(0, "String", $HV2)

$wb2 = _ExcelBookOpen($HV2, 1)
MsgBox(0, "Error", @error)
;$wb2 = _ExcelBookOpen("C:\Error Reports\9.7errs.xls")

Any ideas anyone please

[water]

Why do you use variable $HV2 with statement

$wb2 = _ExcelBookOpen($HV2, 1)

Pass the full path without quotes.

$wb2 = _ExcelBookOpen($wb, 1)

Edited May 14, 2012 by water

[shornw]

Hi Water.

I had already tried

Local $wb = FileOpenDialog("Open Excel WorkBook", "C:\Error Reports", "(*.xlsx;*.xls)")

$wb = _ExcelBookOpen($wb, 1)
_ExcelSheetAddNew($wb, "Servers")
_ExcelSheetAddNew($wb, "Workstations")
_ExcelSheetActivate($wb, "Workstations")

$wb2 = _ExcelBookOpen($wb, 1)
MsgBox(0, "Error", @error)
;$wb2 = _ExcelBookOpen("C:\Error Reports\9.7errs.xls")

bus still get @error 2 File does not exist

[water]

Before you can open a workbook again you have to save the changes and close the workbook.

[Spiff59]

But after this:

$wb = _ExcelBookOpen($wb, 1)

$wb is no longer the filename, but the handle to the first opened workbook.

[BrewManNH]

Your second example won't work because you changed the value of the $wb variable in your script, so it's not the file name and path any longer. If you do this "$HV2 = $wb" it will work, it does not work when you quote the filepath and name for some reason.

[Spiff59]

I notice that _ExcelSheetAddNew() does not behave very nicely.

If the sheet being added already exists then _ExcelSheetAddNew() barfs, kicks a com error (line 959 of excel.au3), and leaves a generically named ("Sheet 1", "Sheet 2", etc) sheet in your workbook.

One would think that would be an easy error to handle a little more politely.

Edited May 14, 2012 by Spiff59

[water]

Maybe the UDF needs some brush up?

[Reg2Post]

I notice that _ExcelSheetAddNew() does not behave very nicely.

If the sheet being added already exists then _ExcelSheetAddNew() barfs, kicks a com error (line 959 of excel.au3), and leaves a generically named ("Sheet 1", "Sheet 2", etc) sheet in your workbook.

One would think that would be an easy error to handle a little more politely.

Well you can't name a worksheet until it exists. If you look at the Function, line 959 is where it is trying to rename the newly added worksheet. And you can't have two worksheets with the same name. If you don't want the error using that function then you should add your own error check before using it. Personally, I think it would be impolite if there was no error report whatsoever.

[Spiff59]

I think it would be impolite if there was no error report whatsoever.

There isn't an error report, really. Only when run inside SciTE is one able to view any resulting error. I'm pretty sure it's preferred that function calls should not abnormally terminate a script. Doing so silently is even worse. I'm sure there are some exceptions. No one wants to try and handle what can result from being "creative" with some of the DLL functions. I think the general intent is that built-in functions, as well as UDF routines, should not be allowed to cause a program to abend.

I submitted a BugTrack with a correction a little while after my prior post in this thread.

[Reg2Post]

Only when run inside SciTE is one able to view any resulting error.

And that is exactly my point.

The function is doing exactly what it is told to do. What do you suggest any preemptive debugging should do? I created my own error check by bringing the worksheet names into an array because you can't even close Excel when you try and change a worksheet name to one that already exists in the workbook.