Jump to content
JohnOne

Excel (general questions)

Recommended Posts

JohnOne

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
Geir1983

First param of _Excel_Open is $visible :)

Check out _Excel_RangeWrite to write arrays-

  • Like 1

Share this post


Link to post
Share on other sites
water

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.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
JohnOne

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
JohnOne

$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
water

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.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
water
But Excel still not closing.

Was Excel already running when you started the script?

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
water

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

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
JohnOne

$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
JohnOne

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
water

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

×