# of .xls into a single .xls

[Sorak99]

k, I'm getting better but I still get stuck alot. I'm attempting to take numerous .xls 's and put them all into a single workbook. I was attempting to use both ExcelCom and ExcelUDF but they wont like to work together.

Problems I'm having is that some of the sheets are asking for updates to their data, and i would just like the values not the formulas. I would like to change the sheet name, but not sure how. Thats about it. The current code i have seems to skip a sheet or two when it imports.

expandcollapse popup

#include <File.au3>
#include<ExcelCom.au3>
#include <array.au3>
#include<string.au3>


Dim $array

$z = 0

$search = FileFindFirstFile("C:\Documents and Settings\Administrator\My Documents\*.xls")  

; Check if the search was successful
If $search = -1 Then
    MsgBox(0, "Error", "No files/directories matched the search pattern")
    Exit
EndIf

While 1
    $file = FileFindNextFile($search) 
    If @error Then ExitLoop
    
    $shortfile = StringReplace($file, ".xls", "");want to use to label the sheets
    
    $cFilePath= "C:\Documents and Settings\Administrator\My Documents\" & $file;"\book4xls.csv"
    $FilePath= "C:\book3.xls"
    
    _XLexit($cFilePath,1)  
    _XLexit($FilePath,1)
    
    $Numsheets = _XLSheetProps( $cFilePath, 1);gets number of sheets +1 so i can copy paste all the time. 

    $realnumsheets = $Numsheets[0] - 1

    $y = $realnumsheets 
    
    For $x = 1 to $y step 1
        
        ToolTip($x & " " & $z, 0,0)
        $z = $z + 1         ;used to paste to the different sheets on book3             
        
        $XLCopyRange=_XLCopy($cFilePath,$x,"UsedRange")
        _XLCopyTo($FilePath,$z,1,1,$XLCopyRange, "Visable")
    
    Next
    
    
WEnd

George

[randallc]

Hi,

There are probably a lot of better ways, but to correct your script;

_XLSheetAdd( $FilePath,$z,1,"NotVisible")

$XLCopyRange=_XLCopy($cFilePath,$x,"UsedRange",1)

_XLCopyTo($FilePath,$z,1,1,$XLCopyRange, "Visible")

you need to add a new sheet each time if that's what you are trying to do.

PS

;~ _XLexit($cFilePath,1)

;~ _XLexit($FilePath,1)

_XLClose($cFilePath,0)

_XLClose($FilePath,0)

I am not sure XLexit is working at present?

Best, randall

[Sorak99]

Hi,

There are probably a lot of better ways, but to correct your script;

you need to add a new sheet each time if that's what you are trying to do.

PS

I am not sure XLexit is working at present?

Best, randall

Thanks Randallc,

Btw sorry all for putting in the wrong forum section, should have been in support, my bad

George