Excel Import and Tabs Question

[Chimaera]

512 test - Msg = 4

255 test - Msg = 0 and same result as the pic above

[water]

So it seems _Excel_SheetAdd has the same limitation as _Excel_BookNew.

I need to change the script to add new worksheets in chunks of 255.

Will post tomorrow. Now it's time for bed ...

[Chimaera]

Well we learnt something new

[water]

Think I've got it Plus I enhanced performance by about 50% (when processing 2 files).

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=N
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include <Excel Rewrite.au3>
#include <Constants.au3>
#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>

Global $sDrive = "", $sDir = "", $sFilename = "", $sExtension = ""
Global $iSheets = 0, $oWorkbookNew, $oWorkbook1, $iArray, $iSheet
; Create application object
Global $oExcel = _Excel_Open(Default, Default, False)
If @error <> 0 Then Exit MsgBox($MB_ICONERROR, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $aCsvFilesToConvert = _FileListToArrayRec(@ScriptDir, "*.csv", 1, Default, Default, 2)
Global $aNumbersToConvert[$aCsvFilesToConvert[0] + 1] = [$aCsvFilesToConvert[0]]
If @error Or Not IsArray($aCsvFilesToConvert) Then
    MsgBox($MB_ICONERROR, "Array Error", "No Files Available To Process", 2)
    Exit
EndIf
; Get the highest filenumber. This is needed because the last file in the array doesn't have the highest number. 100.csv and 4.csv => 4.csv as the last
For $i = 1 To $aCsvFilesToConvert[0]
    _PathSplit($aCsvFilesToConvert[$i], $sDrive, $sDir, $sFilename, $sExtension)
    $aNumbersToConvert[$i] = Int($sFilename)
    If $aNumbersToConvert[$i] > $iSheets Then $iSheets = $aNumbersToConvert[$i]
Next
; Create a new workbook with the number of sheets calculated above
$oWorkbookNew = _Excel_BookNew($oExcel, 1)
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "BookNew: Error " & @error)
; Create the number of worksheets in chunks of 255 (Excel limitation)
$i = $iSheets - 1
While $i > 255
    _Excel_SheetAdd($oWorkbookNew, 1, False, 255)
    $i = $i - 255
WEnd
If $i > 0 Then _Excel_SheetAdd($oWorkbookNew, 1, False, $i)
; Set the name of all sheets to "Blank 00n"
For $i = 1 To $iSheets
    $oWorkbookNew.Sheets($i).Name = "Blank " & StringRight("00" & $i, 3)
Next
For $iArray = 1 To $aCsvFilesToConvert[0]
    ; Open the CSV file in a new workbook
    $oWorkbook1 = _Excel_BookOpen($oExcel, $aCsvFilesToConvert[$iArray], True)
    If @error Then Exit MsgBox($MB_ICONERROR, "Error", "File: " & $aCsvFilesToConvert[$iArray] & ", BookOpen: Error " & @error)
    ; Get the filename of the CSV file = number of the worksheet to write the data
    _PathSplit($aCsvFilesToConvert[$iArray], $sDrive, $sDir, $sFilename, $sExtension)
    $iSheet = Int($sFilename)
    ; Copy the sheet from the source workbook to the target workbook
    _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.Sheets($iSheet).Range("A1"))
    If @error Then Exit MsgBox($MB_ICONERROR, "Error", "File: " & $aCsvFilesToConvert[$iArray] & ", CopyPaste: Error " & @error)
    ; Set the name of the worksheet
    $oWorkbookNew.Sheets($iSheet).Name = "Page " & StringRight("00" & $iSheet, 3)
    ; Close the source workbook (CSV file)
    _Excel_BookClose($oWorkbook1, False)
    If @error Then Exit MsgBox($MB_ICONERROR, "Error", "File: " & $aCsvFilesToConvert[$iArray] & ", BookClose: Error " & @error)
Next
$oExcel.ScreenUpdating = True
Exit

[Chimaera]

Ok that was a bit strange

That version makes an excel window with no tabs it flashes between 2 windows whilst showing weird graphics in the box that normally displays the cursor box address

see image below

It completes the 611 pages but takes about twice the time the last one did

So it works just a bit weird

Edited January 21, 2014 by Chimaera

[water]

Change

Global $oExcel = _Excel_Open(Default, Default, False)

to

Global $oExcel = _Excel_Open(Default, Default, True)

and remove line

$oExcel.ScreenUpdating = True

The display will flicker again but I hope the weird display is gone.

[Chimaera]

Thats a bit less weird lol

Yep that appears to work fine ill add some gui and progress and other bits to it and do a load of testing

Many thanks

[water]

Great! So all issues are solved now?

[Chimaera]

Im curious about one thing

when using excelbooksave as this part

$iType                - Optional: Excel writable filetype. Can be any value of the XlFileFormat enumeration (default = $xlWorkbookNormal)

after some googling "xlExcel12" would appear to make it save as xlsx but it doesnt work or am i doing it wrong

_Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx", "xlExcel12", False)

Edited January 21, 2014 by Chimaera

[water]

You have to specify one of the XlFileFormat enumeration (taken from ExcelConstants.au3 - $xlExcel12 in this case).

But if you let this parameter default then Excel saves the file in it's default format - which is xlsx for Excel 2007 or later.

[Chimaera]

Ok i tried this

_Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx", "50", False)

as 50 seems to be the number for the excel 12 version

but the only way the file saves is as an xls like this

_Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xls", Default, False)

never mind ill go back to reading

[water]

Excel 12 format is xlsx. Set the filename to xxxx.xlsx and specify $xlExcel12 as $IType.

If you want to save the file as xls then set filename to xxx.xls and $iType to one of the older Excel versions e.g. $xlExcel8.

[Chimaera]

yeah i get that 

thats what i did in this example

_Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx", "xlExcel12", False)

it just doesnt save it, thats why i mentioned it

aaah wait i see the mistake brb

hmm didnt work

_Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx", $xlExcel12, False)

Edited January 21, 2014 by Chimaera

[water]

What's the value of @error after calling the function?

[Chimaera]

It gives 

_ExcelBookSaveAs @error = 5 | @extended = -2147352567

Edited January 21, 2014 by Chimaera

[water]

Please add this lines at the top of your script so we get better error information:

#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

[Chimaera]

@@ DEBUG COM Error encountered in Excel Test2.au3 (89) :

Number         = 0x80020006 (-2147352570)

WinDescription = Unknown name.

Description   = 

Source         = 

HelpFile       = 

HelpContext   = 

LastDllError   = 0

Retcode       = 0x00000000

@@ DEBUG COM Error encountered in Excel Test2.au3 (468) :

Number         = 0x80020009 (-2147352567)

WinDescription = Exception occurred.

Description   = This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type.

Source         = Microsoft Excel

HelpFile       = xlmain11.chm

HelpContext   = 0

LastDllError   = 0

Retcode       = 0x800A03EC

>>>>>> Please close the "Report Log Window" to exit <<<<<<<

[water]

Strange. Need to test tomorrow ...

[water]

If you try

_Excel_BookSaveAs($oWorkbookNew, @ScriptDir & "\Catalogue.xlsx")

does this work?

[Chimaera]

It creates the file but.

Edited January 21, 2014 by Chimaera