Opening new file sheets into current sheet

[aditi_lic]

Hello, Thank You for giving me your precious time.
I have following code to open new files through dialog box and add all sheets from them into current workbook. It is working fine as a macro but when I save it as Add-In and select multiple workbooks to open, it opens only sheets from one workbook.

Sub opensheets()
Dim openfiles
Dim crntfile As Workbook
Set crntfile = ActiveWorkbook
Dim x As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
openfiles = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls;*.xlsx),*.xls;*.xlsx", _
MultiSelect:=True, Title:="Select Excel files!")


If TypeName(openfiles) = "Boolean" Then
MsgBox "You need to select atleast one file"
GoTo ExitHandler
End If


x = 1
While x <= UBound(openfiles)
Workbooks.Open Filename:=openfiles(x)
Sheets().Move After:=crntfile.Sheets(crntfile.Sheets.Count)

x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub


ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

 

[DeBeuz]

Hi,

How many files did you try to open at the same time? 2?
I thought X should start at 0, not at 1.

But I might be wrong.

 

[excelbee2013]

If a code is working as a macro that should also work as an Add-In. Anyways try this code:

Dim wb as Workbook
Dim wa as Workbook
While x <= UBound(openfiles)
Set wa = Application.ActiveWorkbook
wb.Worksheets().Copy After:=wa.Sheets(wa.Sheets.Count)

x = x + 1
Wend

Hope it helps!


ExcelBee

 

[aditi_lic]

If a code is working as a macro that should also work as an Add-In. Anyways try this code:

Dim wb as Workbook
Dim wa as Workbook
While x <= UBound(openfiles)
Set wa = Application.ActiveWorkbook
wb.Worksheets().Copy After:=wa.Sheets(wa.Sheets.Count)

x = x + 1
Wend

Hope it helps!


ExcelBee

@ Excelbee sorry its not giving any error but opening a single file only.
@DeBeuz its not working with x=0. Any other idea?

 

[DeBeuz]

Hi,

Sorry, you are correct x should start at 1.
I verified your code and at my place with Excel 2007 it works and opens all files I marked to be openened.
Did you try to debug it and step throug the code?

Do you get any error messages?
Do you see anything happen?
Are the files to be opened protected or normaly accessible?

 

[aditi_lic]

Hi,

Sorry, you are correct x should start at 1.
I verified your code and at my place with Excel 2007 it works and opens all files I marked to be openened.
Did you try to debug it and step throug the code?

Do you get any error messages?
Do you see anything happen?
Are the files to be opened protected or normaly accessible?

Thank you DeBeuz, As I mentioned earlier it works fine as a macro but does not work as an Add-In. It would be great If you can try it as an Add-In in in your system. Me too using 2007.