Sign in to follow this  
Followers 0

Multple .xls import into single .xls

1 post in this topic

I put this in the wrong section :D

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 <array.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")

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"
    $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              
        _XLCopyTo($FilePath,$z,1,1,$XLCopyRange, "Visable")

Then Randallc (who is super awesome) helped :


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


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


_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.



;~ _XLexit($cFilePath,1)

;~ _XLexit($FilePath,1)



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?


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