Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 18th, 2010, 04:54 PM
Registered User
 
Join Date: Nov 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Please Help Consolidate all Workbooks in Folder

Dear VBA for Excel Programmers,

Thank you for your help. Here is a problem that troubles me beyond my present ability to solve. An application that I use exports data in the .xls format. I have about 1000 files in various sub-folders and I need to consolidate all of these into 100 and again down to 10 .xls files so I can analyze the data. Each sub-folder has about (varies) 10 workbook files in it. Each workbook has only one sheet (all named "Sheet1") and the range layout is the same for every file. I want to have one .xlsm file in the root folder and copy the desired range from every .xls file in the sub-folder into a new sheet in the .xlsm that resides in the root folder.

I am able to make a new worksheet named after every file in the sub folder with the below VBA code. I have not figured out how to copy the desired range into the new sheet. I am trying to do it without selecting and activating cells in the source worksheet. How can I copy the cells using variables for workbooks, worksheets and ranges? Your help is greatly appreciated! Thanks in advance.

*******************
Sub test_import()

Dim pth As String
Dim fle As String

' The path to the files
pth = "C:\test folder\"

' Make sheets with same names as files in path
fle = Dir(pth & "*.xls")
Do While Len(fle) > 0

' Add sheet with file name
Sheets.Add.Name = fle

fle = Dir()
Loop

End Sub
*****************

Generate summ rpt,auto send fr various excel files

I found an example that I can modify to do the same as above but I still am not able to get the desired ranges copied into the newly created worksheets.
 
Old November 19th, 2010, 02:14 PM
Registered User
 
Join Date: Nov 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default I figured it out!

I leaned enough in the past few hours to solve this problem myself. The wrox p2p Excel 2007 VBA book helped. Here is the simple solution:

Sub test_import()

Dim pth As String
Dim fle As String
Dim pthfle As String
Dim wkb As Workbook

' The path to the files
pth = "C:\your specific folder\"

' Step through each Workbook in subfolder
fle = Dir(pth & "*.xls")
Do While Len(fle) > 0
pthfle = pth & fle

' Add sheet with file name
ThisWorkbook.Sheets.Add.Name = fle
' Open file and get range
Set wkb = Workbooks.Open(Filename:=pthfle, ReadOnly:=True)
wkb.Worksheets("Sheet1").Range("A2:B6").Copy Destination:=ThisWorkbook.Worksheets(fle).Range("A 1:B5")

' Close file after use
wkb.Close SaveChanges:=False

fle = Dir()
Loop

End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
Data transfer between 2 workbooks jamesfalkener Excel VBA 3 May 4th, 2009 12:52 AM
comparing rows of two workbooks nasirmunir Excel VBA 2 July 9th, 2008 08:10 PM
two workbooks--matching values within kwik10z Excel VBA 0 November 29th, 2007 11:51 AM
Opening Workbooks With Macros RollingWoodFarm Excel VBA 1 February 11th, 2007 05:48 AM
Vba Consolidate Syntax grouth Excel VBA 3 September 29th, 2006 07:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.