Importing data from numerous closed workbooks

[SGlobal]

Hello,

This is my first time posting so I apologize if its not in the correct sub forum. I am new to excel VBA...very new. Just basic stuff however I am working on improving some of our day to day activities and was wondering if this could be done with excel VBA.

What I am trying to do is open up a workbook, click on a macro button and that macro routes to a folder looks in to each workbook within that folder and pulls in all the data on to the active workbook.

Is this possible? I know I can transfer data from all open workbooks to active work book but can I import from closed workbooks? Also the names of the closed workbooks change everyday. So basically this macro would have to just look in the folder and however many excel files are in that folder, it needs to open them, copy the data and import. If there is no way around the constant name changes ergo the files must have a certain name for mapping reasons then we can work around that over here. I can have that department just name the file something specific every time they download it.

Please let me know if this is possible or if theres a workaround for this.

Thanks,
Saad

 

[nikik]

Sub Combine()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim J As Integer<o></o>
<o> </o>
On Error Resume Next<o></o>
Sheets(1).Select<o></o>
Worksheets.Add ' add a sheet in first place<o></o>
Sheets(1).Name = "Combined"<o></o>
<o> </o>
' copy headings<o></o>
Sheets(2).Activate<o></o>
Range("A1").EntireRow.Select<o></o>
Selection.Copy Destination:=Sheets(1).Range("A1")<o></o>
<o> </o>
' work through sheets<o></o>
For J = 2 To Sheets.Count ' from sheet 2 to last sheet<o></o>
Sheets(J).Activate ' make the sheet active<o></o>
Range("A1").Select<o></o>
Selection.CurrentRegion.Select ' select all cells in this sheets<o></o>
<o> </o>
' select all lines except title<o></o>
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select<o></o>
<o> </o>
' copy cells selected in the new sheet on last line<o></o>
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)<o></o>
Next<o></o>
End Sub<o></o>

 

[nikik]

The first one was to combine worksheets in one spreadsheet to one sheet and this one is to combine workbooks onto seperate sheets in one workbook. Hope that's what you were asking for.

Sub CombineWorkbooks()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim FilesToOpen<o></o>
Dim x As Integer<o></o>
<o> </o>
On Error GoTo ErrHandler<o></o>
Application.ScreenUpdating = False<o></o>
<o> </o>
FilesToOpen = Application.GetOpenFilename _<o></o>
(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _<o></o>
MultiSelect:=True, Title:="Files to Merge")<o></o>
<o> </o>
If TypeName(FilesToOpen) = "Boolean" Then<o></o>
MsgBox "No Files were selected"<o></o>
GoTo ExitHandler<o></o>
End If<o></o>
<o> </o>
x = 1<o></o>
While x <= UBound(FilesToOpen)<o></o>
Workbooks.Open FileName:=FilesToOpen(x)<o></o>
Sheets().Move After:=ThisWorkbook.Sheets _<o></o>
(ThisWorkbook.Sheets.Count)<o></o>
x = x + 1<o></o>
Wend<o></o>
<o> </o>
ExitHandler:<o></o>
Application.ScreenUpdating = True<o></o>
Exit Sub<o></o>
<o> </o>
ErrHandler:<o></o>
MsgBox Err.Description<o></o>
Resume ExitHandler<o></o>
End Sub<o></o>

 

[SGlobal]

Yes that's what I am looking for. Thank you much!!! I can actually manipulate it from here to work with my files. I just didn't know where to even start but this puts on the right track. Thanks a lot!!