autofaurus

User defined file path won't work with spaces

6 posts in this topic

#1 ·  Posted

I am using an input box to allow myself or another to enter the name of the excel file they would like to use for the program in order to create a workbook object. My issue is that I can't get it to work with spaces. I have found multiple solutions for if the file path is written out, but i have the name of the file assigned to a variable. Below are the relevant lines:

Global $filename = InputBox("Reference Filename", "What is the name of the file where your references are stored?")
Global $excel = _Excel_Open()
Global $oWorkbook = @MyDocumentsDir & "\" & $filename & ".xlsx"
Global $workbook = _Excel_BookOpen($excel, $oWorkbook)

Running this gives me error code 2 if $filename has a space in it. This would mean that the file path doesn't exist, but it definitely does.

Also, please let me know if there is a better way to create a workbook object when the filename can be different each time.

I am very new to AutoIt and not a very experienced programmer in general so any help is appreciated. Thank you.

Share this post


Link to post
Share on other sites



#2 ·  Posted

Will check over the weekend :)


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

#3 ·  Posted (edited)

Works fine for me, try running the following in Scite to make sure the file path is being set correctly:

#include <Excel.au3>
Global $hSelect = InputBox("Reference Filename", "What is the name of the file where your references are stored?")
Global $oExcel = _Excel_Open()
Global $sWorkbook = @ScriptDir & "\" & $hSelect & ".xlsx"
ConsoleWrite($sWorkbook & @CRLF)
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
ConsoleWrite(@error & @CRLF)

 

Edited by Subz

Share this post


Link to post
Share on other sites

#4 ·  Posted

Did you check that there a no leading or trailing spaces in the user input?


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

#5 ·  Posted

The problem could easily be what water described ...

I don't have Excel, so can't test how _Excel_BookOpen($oExcel, $sWorkbook) evaluates the $sWorkbook string ...

But normally, a full file name with spaces needs to be wrapped in quotes to be evaluated as a valid filename.

So you would need:

Global $oWorkbook = '"' & @MyDocumentsDir & "\" & $filename & ".xlsx" & '"'

Share this post


Link to post
Share on other sites

#6 ·  Posted

The Excel UDF uses FileExists to check the file and returns @error = 2 if the file could not be found.
So you could do this check in your script as well to make sure the file exists.


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

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