Multple .xls import into single .xls

[Sorak99]

I put this in the wrong section

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

Then Randallc (who is super awesome) helped :

Hi,

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

QUOTE

_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

QUOTE

;~ _XLexit($cFilePath,1)

;~ _XLexit($FilePath,1)

_XLClose($cFilePath,0)

_XLClose($FilePath,0)

I am not sure XLexit is working at present?

Best, randall

So he mentioned a "better way" , by reading the forums i have seen different codes do the exact same thing. I dont like the way mine runs currently so I'm sure that there is simplier and better code for it.

Autoit will be the first programming i have learned and used. So anything not in the help file is completely alien to me (Byref, threw me off, still kinda does) So my question is of course help with my code to make it better with some functions that i could learn from, but also how to think like a programmer/coder?

Is the best code the shortest, or to take in factors/mistakes that i may make later (putting files in the wrong spot), or allow a dynamic code that isnt strict in the sense that if one part moves, missing, or is off then the whole thing has to be redone or keep it broken into parts so that as it develops i can change key parts?

George