Excel Workbook Merge

[Snowball]

Hi All!

I'm somewhat new to autoit. However I've run into a problem that I can't seem to solve. I run the below macro in excel but now I'm trying to automate my entire process. I run a number of scripts already and create a bunch of individual .xls files. Now I need to merge them and like i said i manually use the below. However, I now want to do this in Autoit and not in a VB macro. Mostly just for simplicity sake of keeping all the code in Autoit. I've looked through the Excel UDF but can't find anything that would do this. I've also searched the web high and low for other macros that would just merge all the files in a directory placing each file on a separate worksheet tab in a new workbook. No luck there. Key is to merge a number of excel files and have each one show up as a separate tab in the new file. I'm open to any help or suggestions!

Thanks Kindly

Snowball

Here's the macro that works in the excel sheet but requires the user to go select all the files they want to merge. I need it to autoselect the files in a directory then save off as a new file of todays date.

Sub CombineWorkbooks()

Dim FilesToOpen

Dim x As Integer

On Error GoTo ErrHandler

Application.ScreenUpdating = False

FilesToOpen = Application.GetOpenFilename _

(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _

MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then

MsgBox "No Files were selected"

GoTo ExitHandler

End If

x = 1

While x <= UBound(FilesToOpen)

Workbooks.Open FileName:=FilesToOpen(x)

Sheets().Move After:=ThisWorkbook.Sheets _

(ThisWorkbook.Sheets.Count)

x = x + 1

Wend

ExitHandler:

Application.ScreenUpdating = True

Exit Sub

ErrHandler:

MsgBox Err.Description

Resume ExitHandler

End Sub