combine multiple excel file in to one excel file and multiple worksheet

I am wanting to use the following code to combine worksheets from
multiple files.  However I would like to be able to select folder which

contains files in a more automated way that having to change the code
every time, and also copy all worksheets with links and formulas
removed.  Any help on this is greatly appreciated as I have limited
code knowledge.


Sub Copy_them()

>     Dim TargetWkbk As Workbook
>     Dim mrgWkbk As Workbook


>     Dim i As Long
>     Dim Wks As Worksheet
>     Dim fName As String


>     Application.ScreenUpdating = False
>     Set TargetWkbk = Workbooks.Add(1)
>     ActiveSheet.Name = "dummy"


>     With Application.FileSearch
>         .NewSearch
>         .LookIn = "c:\Temp"          'folder to use
>         .SearchSubFolders = False
>         .Filename = "*.xls"
>         .FileType = msoFileTypeExcelWorkbooks
>         If .Execute() > 0 Then
> '            MsgBox "There were " & .FoundFiles.Count & " file(s) found."
>             For i = 1 To .FoundFiles.Count
>                 Set mrgWkbk = Workbooks.Open(.FoundFiles(i))
>                 For Each Wks In ActiveWorkbook.Worksheets
>                     With TargetWkbk
>                         Wks.Copy after:=.Worksheets(.Worksheets.Count)
>                     End With
>                 Next Wks
>                 mrgWkbk.Close False
>             Next i


>             Application.DisplayAlerts = False
>             TargetWkbk.Worksheets("dummy").Delete
>             Application.DisplayAlerts = True


>             fName = Application.GetSaveAsFilename _
>                     (fileFilter:="MS Excel Workbook (*.Xls), *.Xls")


>             TargetWkbk.SaveAs Filename:=fName, FileFormat:=xlNormal, _
>                 Password:="", WriteResPassword:="", _
>                 ReadOnlyRecommended:=False, CreateBackup:=False
>         Else
>             MsgBox "There were no files found."
>             TargetWkbk.Close savechanges:=False
>         End If
>     End With


>     Application.ScreenUpdating = True
>     Application.EnableEvents = False 


> End Sub

0
bstojke (4)
12/30/2005 9:21:45 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
358 Views

Similar Articles

[PageSpeed] 43

You may do a paste special, which will allow you paste only the information 
you want.  Choose paste values if all you want is the text to be pasted.

"jbhoop" wrote:

> I am wanting to use the following code to combine worksheets from
> multiple files.  However I would like to be able to select folder which
> 
> contains files in a more automated way that having to change the code
> every time, and also copy all worksheets with links and formulas
> removed.  Any help on this is greatly appreciated as I have limited
> code knowledge.
> 
> 
> Sub Copy_them()
> 
> >     Dim TargetWkbk As Workbook
> >     Dim mrgWkbk As Workbook
> 
> 
> >     Dim i As Long
> >     Dim Wks As Worksheet
> >     Dim fName As String
> 
> 
> >     Application.ScreenUpdating = False
> >     Set TargetWkbk = Workbooks.Add(1)
> >     ActiveSheet.Name = "dummy"
> 
> 
> >     With Application.FileSearch
> >         .NewSearch
> >         .LookIn = "c:\Temp"          'folder to use
> >         .SearchSubFolders = False
> >         .Filename = "*.xls"
> >         .FileType = msoFileTypeExcelWorkbooks
> >         If .Execute() > 0 Then
> > '            MsgBox "There were " & .FoundFiles.Count & " file(s) found."
> >             For i = 1 To .FoundFiles.Count
> >                 Set mrgWkbk = Workbooks.Open(.FoundFiles(i))
> >                 For Each Wks In ActiveWorkbook.Worksheets
> >                     With TargetWkbk
> >                         Wks.Copy after:=.Worksheets(.Worksheets.Count)
> >                     End With
> >                 Next Wks
> >                 mrgWkbk.Close False
> >             Next i
> 
> 
> >             Application.DisplayAlerts = False
> >             TargetWkbk.Worksheets("dummy").Delete
> >             Application.DisplayAlerts = True
> 
> 
> >             fName = Application.GetSaveAsFilename _
> >                     (fileFilter:="MS Excel Workbook (*.Xls), *.Xls")
> 
> 
> >             TargetWkbk.SaveAs Filename:=fName, FileFormat:=xlNormal, _
> >                 Password:="", WriteResPassword:="", _
> >                 ReadOnlyRecommended:=False, CreateBackup:=False
> >         Else
> >             MsgBox "There were no files found."
> >             TargetWkbk.Close savechanges:=False
> >         End If
> >     End With
> 
> 
> >     Application.ScreenUpdating = True
> >     Application.EnableEvents = False 
> 
> 
> > End Sub
> 
> 
0
NUMBnut (22)
12/30/2005 9:31:01 PM
Reply:

Similar Artilces:

Naming a worksheet after a cell valu
I have a several worsheets in a workbook. I would like to be able t run a macro that would rename many of the sheets based on the values i a range of cells. If the values change then I would like to be able t change the worksheet names with a manual macro. Any help would be greatly appreciated Jerr -- Message posted from http://www.ExcelForum.com You can run a simple macro like this: Sub ChangeName() Dim ws As Worksheet Dim i As Integer i = 1 For Each ws In ThisWorkbook.Worksheets With ws If .Name <> "Sheet1" Then .Name = Sheets("Sheet1&...

Help, Pls
C Drive crashed, installed O2002; have a months old .wab address backup, but O2002 won't import or allow additional address book. Is there any way to recover the addys in the backup file into O2002? TIA - wlc ...

Excel 2007 charts resizing unexpectedly
On opening an Excel 2007 sheet with charts, I often find that the charts have resized themselves: they are either too big to fit on the screen, or have shrunk. A work-around is to use the 'move chart' menu option to move the chart to an object in another sheet and then move it back again as a new sheet. However, the font sizes are then messed up. ...

excel freezes and goes into a "not responding" state after opening file
When I open an excel file that is located on the file server it will freeze excel and "Not Responding" will show up on the title bar. (Note this only happens with files saved on the file server) Then after about 10 minutes the program will operate normally. I am running a brand new core 2 duo maxed out with memory and the excel sheet is very simple with only basic calculations. There are no hangups in the network between my pc and the server that i can see, and nobody else has the same problem at their workstation. The sheet is not in use by anyone else and the load on the server i...

Discrepancy in size of pst files.
When I check the size of my pst files from within 0utlook, they are all around 700mb. However, when I check them in the actual folder where they are stored, then the main Personal pst file is over 2GB. I intentionally created 3 different pst files, so that I could keep their size down. But why is there this discrepancy in file sizes, and how can I split the 2GB one to make it smaller? There is always some overhead from meta-information in the pst-file and white space for performance reasons. This shouldn't be more than a 20% difference though. Most likely you've delete...

Publisher files saved to a server 2003 open slowly if at all
I have xp computers running office 2007 all clients save files to a server 2003 equiped dell sc4400 server. When office 2003 was opertaing all clients opened fast from the server. With Office 2007 installed all server files open very slowly. I do not have Office 2007 installed on my server. Could this be the problem? All clients run slowly since the change to office 07 I cannot find anything I should have done to inform my server of the change to Office 07. Please Help. ...

how do i link 2 excel files?
I need to link 2 excel files. Let's call them file 1 and file 2 I need to insert rows in file 1 so that it inserts the same amount of rows in the same position in file 2. Ben, Whenever I see a question like this, I have to ask: Why? Why not just use one file with one sheet? Any other approach will have potential problems, and require a lot of work to design. HTH, Bernie MS Excel MVP "Ben" <Ben@discussions.microsoft.com> wrote in message news:2FCEF568-0D45-48CB-B956-98A883EE81CD@microsoft.com... >I need to link 2 excel files. Let's call them file 1 and file 2 ...

Multiple Exchange Servers
Is it possible to install ms-crm email router on more then one Exchange Server in the same domain that will work with a single crm server? Also, is it possible to install the email-router on 2 exchange servers that one of them is in a different domain and both of them will work with a single crm server that resides in one of these domains? Is there a change in this cases in verion 3.0 (vNext)? There is definately a change for v3. The support for multiple servers is much better in v3, but not sure how it will handle the multiple Domain question. For v1.x, it could work, but depends a lot ...

Multiple Payees
I am using Money 2002 v10.0 My banking institution sends its transactions inclusive of transaction fees. At the moment I am 'Split'ing categories to show the fees, which makes my budget look OK. However the bank fees are showing against the Payees and not the Banking institute, so my Payee balances are all out. Is there a way I can also 'Split' a transaction into multiple payees as well as multiple categories EG.. Paying the phone bill: Phone company $20.00 Bank Fee $0.50 Total Trans. $20.50 Although I only paid the phone company $20.00 it shows as $20.50. ALter...

advice about converting publisher/word files to pdf
Can someone here advise me about how I can make a pdf file from a Microsoft Publisher (97) file? I'm trying to speed up the work of a printing press who are making a book of some of my poems. Idally I'd have a pdf file that looks like the book, but if it needs to made from a Word/rtf extraction of the Publisher file then that might do. Ideally if you could direct me to some hassle-free and public domain software that's tried and tested, that you'd recommend, that I can download to do this job that'd be great. Many thanks for any input. gerryhandke@hotmail.com <...

Summary sheet for multiple sheets (difficult problem)
Here's the scenario: people owe money to companies. We assign each of these people a number, and create a separate sheet for each of the many companies. We need to compile a summary sheet (within the same workbook). Here's a sample sheet (let's call it Sample Company, which is the name of the sheet): Name ID # Amount John Jones 555 500 Jim Smith 123 175 Mary Long 232 100 Thus far the workbook has about twenty sheets (not counting the summary sheet) each named after a company and additional sheets are added freque...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

Excel 2003- Tabbing to a specific location in a cell
I have created a template and I want to be able to fill in parts of the template by just tabbing and the cursor to move to the next cell...and to a specific area in the cell...how do i set up my template to do this? THanks so much unlock the cells you want to tab to. leave the other cells protected. then protected the sheet. -- Gary Keramidas Excel 2003 "monty the magician" <monty the magician@discussions.microsoft.com> wrote in message news:FD3728D8-E730-407E-B58A-FED2C6D9EA8A@microsoft.com... >I have created a template and I want to be able to f...

How do I import OE .dbx files into outlook?
Old machine crashed but had fortunately backed up data, including important OE .dbx files. How do I import these .dbx files into outlook (no longer have OE) so I can access the content? Simon wrote: > Old machine crashed but had fortunately backed up data, including important > OE .dbx files. > > How do I import these .dbx files into outlook (no longer have OE) so I can > access the content? Why do you no longer have OE on the old machine that crashed that we are to assume where you will reinstall the same version of Windows that before had OE? See http...

How can I create a chart to compare multiple data series?
Using Excel, is there a way to merge multiple charts with different data series into one to demonstrate a direct comparison of the data series within one single chart. I've got 4 charts of the stacked column type. magnoliak77 Tufte calls them small multiples, Cleveland calls them trellis displays. I call them panel charts. Take a look at the examples on this page. http://processtrends.com/toc_panel_charts.htm Another option is to stick with your 4 charts, however, you can size and align them. Here's a link to a simple macro that sizes and aligns all charts on a single sheet....

changing colours of multiple items in Publisher 2000
Does anyone have any idea how I can change the colour of multiple items on a page? It is so time consuming to have to select and change the colour of each item individually - it would be so useful to be able to select all the items I need and change the colour en bloc. Do the newer Publisher packages have this facility (2003, 2007?) What items are you trying to chage? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Fluffbrain" <Fluffbrain@discussions.microsoft.com> wrote in message news:0B67BD6A-B0E8-45A9...

to use workday function in excel vba code
hi all, is there a way to use wrokday function in my worksheet controls.? i have a date time picker in my worksheet and a text box and a button. on click of this button i should get next desired date. like when i select a date from the calendar and click on button ther is onclick function in my macro. this onclick should calculate next desired date(assume if i choos 10/11/2005 and add 2 to this date i should get 12/11/2005) i know how to use workday function using a cell reference but................ i don't know how to put this into a vba code. if i use workday("10/11/2005&qu...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

document for file I/O with VBA
Hi all, Where can I find a complete document about File I/O with VBA? I find it hard to find in help due to the way it is organized. I want to find ways to deal with files especially ".INI" alike text files using Open, Input, etc. (let me know if there are better ways). The functions I need are to seek lables like [General] and delte or replace data in the section. Thank you, muster <muster@gmail.com> wrote: >Where can I find a complete document about File I/O with VBA? I find >it hard to find in help due to the way it is organized. Access Type Writing Data ...

Dynamic formulae
Hello Excel disciples, =A0 =A0 I wonder if I can run this past you? =A0 =A0 Lotus 123 had a facility that you were able to build "Dynamic" formulae ie; =A0to construct using text and ranges to create valid formulae. =A0 I have several worksheets name Team 1, Team 2, Team 3, each identical etc I have a consolidation worksheet that takes numeric values from a cell from= each and sums the total. =A0 In the Totals sheet columns above the values column is the team name in= text. =A0 =A0 =A0Team 13 =09=09Team 14=09=09Team 15 etc =A0Grd1 =A0Grd2 =A0100 =A0=09375 =A0100=09=09375 43.5=09=09...

How do I import a database from excel 2003 to 2007?
The database is not too large, but important. I need to transfer it to Excel 2007 from 2003. Can this be done without losing data, or changing the format? If so, how? Obviously I am not very adept with Excel. Thank you for your help. Theresa, Start by making a backup copy of your 2003 format file, just in case. Open Excel 2007 and then open the 2003 file with it. It will open in "compatibility mode" meaning that it is treated as an Excel 2003 file with 2003's restrictions on rows, columns and other features. Use File | Save As to save the file in one of the native Exce...

Multiple calls for Campaign
Hello I am attempting to set up a telemarketing campaign by using quick campaign in Microsoft CRM 4.0. I have set up the marketing list with multiple contacts and created a quick campaign for this marketing list. While creating the quick campaign, I selected phone call to all of the contacts within the marketing list. However, a phone call task was not created for every contact within the list. Only one phone call task was created for the entire marketing list. How can I create multiple phone calls for each and every contact within the marketing list. Kindly advise. Thank you ...

excel formula #4
I have in column A1:A365 all dates of the year 2005 and in colum B1:B365 the days of that date. In column D1:D365, E1:D365 and F1;F365 I fill in a X for every actio Succeeded. SO if it is 1 January 2005 and the action in D1 and E1 and F1 are al succeeded I fill in those fields an: X I have in a cell a formula what checks how many % (procent) succeede of the fields who are filled. But when it is December he also calculates the X that are filled in A etc. Now I would like to make a formula which calculates how many action went good in the last 30 days is this possible? So that he only chec...

excel 2003 publish to a remote share does not work...
Using Excel 2003. Create a chart and have saved it as a web page and published to a remote share. When I save it locally, it works fine and is interactive,etc.. When I save it remotely and open the htm file in a web browser, it opens like the charting area but the data is not present. Gives error below in the pivot table that appears. Getting error The query could not be processed: o Error opening data file "file://\\remoteserver01\hassan\numbers_files\Numbers_Jan24_12558_cachedata001.xml". Any idea how to fix it ? I want others to access it remotely. Thanks ...

Printing spreadsheets crested in earlier versoins of Excel
Hello - My computer and my wife's computer are networked. I have WinXP and Excel 2000 9.0.3821 SR-1 on my computer. She has Win98 and Excel 2000 9.0.2720. When I print a spreadsheet created in Win98 with an earlier version of Excel (I'm not sure which version), I get "squiggilly" vertical lines and numbers. When I print the same file on the same printer using my wife's computer it looks fine. What is the problem? Is there a way I can convert the file so it will print correctly? Thanks Drew As answered at your second post: Have you tried copying the conte...