JohnOne

Excel (general questions)

12 posts in this topic

I have never had Excel on my computer and never used it at all, and I have a few questions.

Can I manipulate excel files without opening visually an excel window?

What my goal is, is to convert a plain txt .csv file to excel .xls.

I seen a few examples with what looks like a depreciated function _ExcelWriteArray (or something similar to that name), is there a newer function similar to it?


AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites



First param of _Excel_Open is $visible :)

Check out _Excel_RangeWrite to write arrays-

1 person likes this

Share this post


Link to post
Share on other sites

All functions of the latest Excel UDF start with _Excel_ (note the second underscore).

User _Excel_BookOpen to open and import the CSV file to Excel.

1 person likes this

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

Thanks, will do. but I found I can just open the csv file.

I have this code...

$oExcel1 = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel1, "output.csv")

Sleep(10000)

_Excel_BookClose($oWorkbook, True)
_Excel_Close($oExcel1)

So looking to widen a row, as it only shows "########" and save it as .xls.

Also _Excel_Close($oExcel1) does not close the instance of excel.


AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites

$oExcel1 = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel1, "output.csv")

Sleep(1000)
_Excel_BookSaveAs($oWorkbook, "output.xls", $xlExcel8)
_Excel_BookClose($oWorkbook, True)
_Excel_Close($oExcel1)

Think that's about it, the width of the column is okay when it's opened as .xls.

But Excel still not closing.


AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites

You need to widen the column when "####" is being shown.

Use:

 

$oExcel1.ActiveSheet.UsedRange.Columns.AutoFit = True

so widen all columns in the used range.

1 person likes this

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 Excel still not closing.

Was Excel already running when you started the script?

1 person likes this

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

What are the return codes and values of @error and @extended after calling _Excel_BookClose and _Excel_Close?

1 person likes this

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

$oExcel1 = _Excel_Open()
ConsoleWrite("_Excel_Open " & @error & " " & @extended & @LF)
$oWorkbook = _Excel_BookOpen($oExcel1, "output.csv")
ConsoleWrite("_Excel_BookOpen " & @error & " " & @extended & @LF)
;$oExcel1.ActiveSheet.UsedRange.Columns.AutoFit = True
Sleep(10000)
_Excel_BookSaveAs($oWorkbook, @ScriptDir & "\output.xls", $xlExcel8)
ConsoleWrite("_Excel_BookSaveAs " & @error & " " & @extended & @LF)
_Excel_BookClose($oWorkbook, True)
ConsoleWrite("_Excel_BookClose " & @error & " " & @extended & @LF)
_Excel_Close($oExcel1)
ConsoleWrite("_Excel_Close " & @error & " " & @extended & @LF)
ShellExecute("output.xls")
_Excel_Open 0 1
_Excel_BookOpen 3 -2147352567
_Excel_BookSaveAs 1 0
_Excel_BookClose 1 0
_Excel_Close 0 0


AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

Sorry, once I put full path "$oWorkbook = _Excel_BookOpen($oExcel1, @ScriptDir & "output.csv")" it began to work.

I swear it worked first couple of times without, perhaps working dir got changed somehow.

EDIT:

Also Excel is closing now too.

Thank you muchly for help.

Edited by JohnOne

AutoIt Absolute Beginners    Require a serial    Pause Script    Video Tutorials by Morthawt   ipify 

Monkey's are, like, natures humans.

Share this post


Link to post
Share on other sites

:)


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