Jump to content
Sign in to follow this  

Excel Workbook Merge

Recommended Posts


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


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 _


x = x + 1



Application.ScreenUpdating = True

Exit Sub


MsgBox Err.Description

Resume ExitHandler

End Sub

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