Sign in to follow this  
Followers 0
shornw

Opening Excel book with variable

11 posts in this topic

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

[font='Comic Sans MS']Eagles may soar high but weasels dont get sucked into jet engines[/font]

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Why do you use variable $HV2 with statement

$wb2 = _ExcelBookOpen($HV2, 1)
Pass the full path without quotes.
$wb2 = _ExcelBookOpen($wb, 1)

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

[font='Comic Sans MS']Eagles may soar high but weasels dont get sucked into jet engines[/font]

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

But after this:

$wb = _ExcelBookOpen($wb, 1)

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

Share this post


Link to post
Share on other sites

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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by Spiff59

Share this post


Link to post
Share on other sites

Maybe the UDF needs some brush up?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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. ;)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0