Sign in to follow this  
Followers 0
Sorak99

# of .xls into a single .xls

3 posts in this topic

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.

#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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0