Excel (general questions)

[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?

[Geir1983]

First param of _Excel_Open is $visible

Check out _Excel_RangeWrite to write arrays-

[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.

[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.

[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.

[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.

[water]

But Excel still not closing.

Was Excel already running when you started the script?

[JohnOne]

No, I tried again made sure no instances are running.

It's from office 2013.

[water]

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

[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

[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 February 21, 2015 by JohnOne

[water]