Inserting files into an open workbook

gagnons

New member
Joined
May 2, 2013
Messages
3
Reaction score
0
Points
0
I have many single worksheet files derived from csv files. I would like to bring them to a common workbook and keep the worksheet name. Is that possible with some VBA code:

I know how to copy and paste but I have about 70 files to be combined into a workbook.

Also, is it possible to insert an entire workbook into a workbook as long as the worksheet names are different?
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
gagnons, some clarification please

1) your many single worksheet files derived from cvs files, are they now saved as .xls files?

2) are all these files residing in the same directory?

3) all the single worksheets are distinctively named?

Also, is it possible to insert an entire workbook into a workbook as long as the worksheet names are different?

It is possible to copy an entire workbooks sheets into another workbook, but if the workbook being copied contains VBA modules and UserForms.....??? don't know what would be involved.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
This works for me, I manually enter the file path.


Code:
Option Explicit

Sub ImportFirstSheetFromFilesInDirectory()

Dim Path As String               'string variable to hold path to look through
Dim FileName As String           'temporary filename variable
Dim WBk1 As Workbook             'this workbook
Dim WBk2 As Workbook             'temporary workbook (each workbook in the directory)


'***** MANUALLY SET FOLDER TO CYCLE THROUGH *************

Path = "V:\test_MonthEnd"   '<-- put your directory here

'********************************************************

Application.EnableEvents = False    'turn off events
Application.ScreenUpdating = False  'turn off screen updating

If Right(Path, 1) <> Application.PathSeparator Then     'if path doesn't end in "\"
    Path = Path & Application.PathSeparator             'add "\"
End If

Set WBk1 = ActiveWorkbook                   'this workbook

FileName = Dir(Path & "*.xls", vbNormal)    'set first file's name to filename variable

Do Until FileName = ""  'loop until all files have been done

    If Path <> ThisWorkbook.Path Or FileName <> ThisWorkbook.Name Then
        Set WBk2 = Workbooks.Open(FileName:=Path & FileName)        'open file, set to WBk2 variable
        WBk2.Sheets(1).Copy After:=WBk1.Sheets(WBk1.Sheets.Count)   'the newly opened workbook will be active
        WBk2.Close                                                  'the original workbook will be active
    End If
    
    FileName = Dir()        'set next file name to filename variable
Loop

Application.EnableEvents = True
Application.ScreenUpdating = True

Set WBk2 = Nothing

End Sub

Post back and let us know how this goes for you.
NoS
 

gagnons

New member
Joined
May 2, 2013
Messages
3
Reaction score
0
Points
0
This worked well after I fixed my fat finger mistakes. Thanks
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
You're welcome, and thank you for reporting back.

NoS
 
Top