Jump to content
JohnOne

Excel (general questions)

Recommended Posts

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?

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

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.

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.

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

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

×
×
  • Create New...