Excel Import and Tabs Question

[Chimaera]

I tested again with the 0-99 files removed and it runs for a while then stops with one blank excel sheet

The original i used before we got complicated ran alll the files prob because the limit isnt within _Excel_SheetAdd which is what i used

Here was what i ran

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>

; Create application object
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $sWorkbook = @ScriptDir & "\Catalogue.xls"
Global $aCsvFilesToConvert = _FileListToArrayRec(@ScriptDir, "*.csv", 1, Default, Default, 2)
_ArrayDisplay($aCsvFilesToConvert, "Csv Files To Convert")
If @error Or Not IsArray($aCsvFilesToConvert) Then
    MsgBox($MB_ICONINFORMATION, "Array Error", "No Files Available To Process", 2)
    Exit
EndIf

Example1($oAppl)

Exit

Func Example1($oAppl)
    Local $oWorkbook1, $iArray, $iSheet, $iSheets
    Local $sDrive = "", $sDir = "", $sFilename = "", $sExtension = ""
    ; Get the filename of the last file = highest file number = number of sheets in the target workbook
    _PathSplit( $aCsvFilesToConvert[$aCsvFilesToConvert[0]], $sDrive, $sDir, $sFilename, $sExtension)
    $iSheets = Int($sFilename)
    ; Create a new workbook with the number of sheets calculated above
    Local $oWorkbookNew = _Excel_BookNew($oAppl, 1) ;Local $oWorkbookNew = _Excel_BookNew($oAppl, $iSheets)
    If $iSheets > 1 Then _Excel_SheetAdd($oWorkbookNew, 1, False, $iSheets - 1)
    For $iArray = 1 To $aCsvFilesToConvert[0]
        ; Open the CSV file in a new workbook
        $oWorkbook1 = _Excel_BookOpen($oAppl, $aCsvFilesToConvert[$iArray], True)
        ; 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"))
        ; Close the source workbook (CSV file)
        _Excel_BookClose($oWorkbook1, False)
    Next
    ; Set the name of all sheets
    For $i = 1 To $iSheets
        $oWorkbookNew.Sheets($i).Name = "Page " & $i
    Next
;~  _Excel_BookSaveAs($oWorkbookNew, $sWorkbook, Default, False)
EndFunc   ;==>Example1

Heres the original if it helps

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#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>


; Create application object
Global $oAppl = _Excel_Open()
Global $sWorkbook = @ScriptDir & "\Catalogue.xls"
Global $aCsvFilesToConvert = _FileListToArrayRec(@ScriptDir, "*.csv|951.csv", 1, Default, Default, 0)
;~ Global $aCsvFilesToConvert = _FileListToArray( @ScriptDir,"*.csv", 1)
        If @error <> 0 Or @extended <> 0 Then ConsoleWrite( "FLTArray " & @error & " | " & @extended & @CRLF)
    If Not IsArray($aCsvFilesToConvert) Then
        MsgBox($MB_ICONINFORMATION, "Array Error", "      No Files Available To Process", 2)
        Exit
    EndIf

Example1($oAppl)

Exit

Func Example1($oAppl)
    _ArrayDisplay($aCsvFilesToConvert, "Csv Files To Convert")
    Local $oWorkbookNew = _Excel_BookNew($oAppl, 1)

    If IsArray($aCsvFilesToConvert) Then
        For $i = 1 To $aCsvFilesToConvert[0]
            Local $oWorkbook1 = _Excel_BookOpen($oAppl, @ScriptDir & "\" & $aCsvFilesToConvert[$i], Default, Default, True) ; Opens the book csv file ready to copy
            If @error <> 0 Or @extended <> 0 Then ConsoleWrite( "BookOpen " & @error & " | " & @extended & @CRLF)
            Local $AddSheet = _Excel_SheetAdd($oWorkbookNew, Default, False, Default, "Page " & StringTrimRight($aCsvFilesToConvert[$i],4)) ; Adds a blank sheet to the second workbook ready for the copy
            If @error <> 0 Or @extended <> 0 Then ConsoleWrite( "SheetAdd " & @error & " | " & @extended & @CRLF)
            Local $oWorkbook2 = _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oWorkbook1.ActiveSheet.UsedRange, $oWorkbookNew.ActiveSheet.Range("A1")) ; Copy data from Book1 to BookNew
            If @error <> 0 Or @extended <> 0 Then ConsoleWrite( "RangeCopyPaste " & @error & " | " & @extended & @CRLF)
            Local $CloseTempBook = _Excel_BookClose($oWorkbook1, False) ; Close the csv book ready for the next cycle
        Next
    EndIf
    _Excel_SheetDelete($oWorkbookNew, 1) ; Removes empty 1st worksheet
     _Excel_BookSaveAs($oWorkbookNew, $sWorkbook, Default, False)
EndFunc   ;==>Example1

[water]

I assume your files are named like this: "4.csv" not this "004.csv"?

[Chimaera]

yes they are

i ran the test both ways and it failed equally

second test i removed all lower numbers (below 100) and ran it again and it still failed with a blank workbook.

You could see sheet numbers flashing at the bottom whilst it was working and it seemed to be doing something but just stopped after a while

[water]

I will do some tests as soon as I find some spare time ...

[water]

This version should solve all problems I tested with files 4.csv and 104.csv.

expandcollapse popup

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

#include "H:\tools\autoit3\Excelex\Excel Rewrite.au3"
#include <Constants.au3>
#include <Array.au3>
#include <File.au3>
;~ #include <Debug.au3> ; <== For debugging of COM errors only

;~ _DebugSetup()
;~ _DebugCOMError()

Global $sDrive = "", $sDir = "", $sFilename = "", $sExtension = ""
Global $iSheets = 0
; Create application object
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "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_ICONINFORMATION, "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

Example1($oAppl)

Exit

Func Example1($oAppl)
    Local $oWorkbook1, $iArray, $iSheet
    ; Create a new workbook with the number of sheets calculated above
    Local $oWorkbookNew = _Excel_BookNew($oAppl, 1)
    If $iSheets > 1 Then _Excel_SheetAdd($oWorkbookNew, 1, False, $iSheets - 1)
    For $iArray = 1 To $aCsvFilesToConvert[0]
        ; Open the CSV file in a new workbook
        $oWorkbook1 = _Excel_BookOpen($oAppl, $aCsvFilesToConvert[$iArray], True)
        ; 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"))
        ; Close the source workbook (CSV file)
        _Excel_BookClose($oWorkbook1, False)
    Next
    ; Set the name of all sheets
    For $i = 1 To $iSheets
        $oWorkbookNew.Sheets($i).Name = "Page " & $i
    Next
EndFunc   ;==>Example1

[Chimaera]

Just tested twice to be sure and you can see the csv import data flashing on screen in between blank pages

takes about 100 secs which is really good but finishes with a book1-Excel workbook with a page 1 tab which is blank and no data

Dunno if it helps but the ones being opened from the csv are read only and it seems to process the 1-99 last of all, from running another test with the windows full screen it doesn't seem to be building the tabs into the main sheet, unfortunately its behind the other one so difficult to see.

Managed to pause it with both screens available and the imported data looks fine and the tab for it was named 223 and i checked 223.csv and they matched but there was no tabs on the main sheet other than Sheet1

Not sure what is happening

ps i had to alter your excel Rewrite line back to #include <Excel Rewrite.au3>

Do you have a different Excel Rewrite.au3 than me maybe? i downloaded mine about 2 days ago

Thanks for your time

Edited January 20, 2014 by Chimaera

[water]

I've added some error checking so we know what happens:

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>

;~ #include <Debug.au3> ; <== For debugging of COM errors only

;~ _DebugSetup()
;~ _DebugCOMError()

Global $sDrive = "", $sDir = "", $sFilename = "", $sExtension = ""
Global $iSheets = 0
; Create application object
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox(16, "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

Example1($oAppl)

Exit

Func Example1($oAppl)
    Local $oWorkbook1, $iArray, $iSheet
    ; Create a new workbook with the number of sheets calculated above
    Local $oWorkbookNew = _Excel_BookNew($oAppl, 1)
    If @error Then Exit MsgBox($MB_ICONERROR, "Error", "BookNew: Error " & @error)
    If $iSheets > 1 Then
        _Excel_SheetAdd($oWorkbookNew, 1, False, $iSheets - 1)
        If @error Then Exit MsgBox($MB_ICONERROR, "Error", "SheetAdd: Error " & @error)
    EndIf
    For $iArray = 1 To $aCsvFilesToConvert[0]
        ; Open the CSV file in a new workbook
        $oWorkbook1 = _Excel_BookOpen($oAppl, $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)
        ; 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
    ; Set the name of all sheets
    For $i = 1 To $iSheets
        $oWorkbookNew.Sheets($i).Name = "Page " & $i
    Next
EndFunc   ;==>Example1

[Chimaera]

The error is SheetAdd: Error 4 within a couple of seconds of starting

With a blank excel sheet open

Edited January 20, 2014 by Chimaera

[water]

Can you please remove the comments from this lines? We will get better debugging info in a debug window.

;~ #include <Debug.au3> ; <== For debugging of COM errors only

;~ _DebugSetup()
;~ _DebugCOMError()

[Chimaera]

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

Number         = 0x80020006 (-2147352570)

WinDescription = Unknown name.

Description   = 

Source         = 

HelpFile       = 

HelpContext   = 

LastDllError   = 0

Retcode       = 0x00000000

@@ DEBUG COM Error encountered in Excel Test.au3 (1606) :

Number         = 0x80020009 (-2147352567)

WinDescription = Exception occurred.

Description   = Add method of Sheets class failed

Source         = Microsoft Excel

HelpFile       = xlmain11.chm

HelpContext   = 0

LastDllError   = 0

Retcode       = 0x800A03EC

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

[water]

WinDescription = Unknown name. is expected and can be ignored.

I will investigate the other error ...

[water]

After the script ends, can you manually add a new worksheet?

[Chimaera]

Yes i can on the blank sheet

I was doing a little reading on that error and id best mention the csv's are maybe 18 colums wide and possibly 100 rows down some only have 1 row

Also second thought my first real file is No 4 1,2,&3 would be blank

EDIT just tried with 1,2,3 files added by hand and still same error

Edited January 20, 2014 by Chimaera

[water]

Can you please add line

MsgBox(0, "", $iSheets)

before line

Example1($oAppl)

and post the result?

[Chimaera]

611

then

SheetAdd error

then

@@ DEBUG COM Error encountered in Excel Test.au3 (1606) :

Number         = 0x80020009 (-2147352567)

WinDescription = Exception occurred.

Description   = Add method of Sheets class failed

Source         = Microsoft Excel

HelpFile       = xlmain11.chm

HelpContext   = 0

LastDllError   = 0

Retcode       = 0x800A03EC

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

Edited January 20, 2014 by Chimaera

[water]

I will test tomorrow as soon as I'm at a Windows PC again.

BTW: Should be possible to name the sheets as you requested.

[Chimaera]

If you want to do a closed test just send it by pm and ill send the results back straightaway

[water]

First test would be:

#include <Excel Rewrite.au3>

Global $oAppl = _Excel_Open()
Local $oWorkbookNew = _Excel_BookNew($oAppl, 1)
_Excel_SheetAdd($oWorkbookNew, 1, False, 512)

If it crashes replace 512 with 255.

[Chimaera]

Nope no crash on 512

But just one blank window with 1 tab

repeated with 255

one excel book with this

Edited January 20, 2014 by Chimaera

[water]

I forgot that we no longer get a crash with AutoIt > 3.3.8.1. So let's check @error.

#include <Excel Rewrite.au3> 
Global $oAppl = _Excel_Open()
Global $oWorkbookNew = _Excel_BookNew($oAppl, 1) 
_Excel_SheetAdd($oWorkbookNew, 1, False, 512)
MsgBox(0, "", @error)

Can you please test with 512 and 255? What does MsgBox show?