Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LAST HOPE....Time is Ticking!!
Hi All!
This is what I need to do: On a daily basis, I have to go into windows explorer onto a shared network drive and open up a folder. Lets call that folder "Monday" Inside the Monday folder there are as many as 80 excel files in that folder all consisting of contact names and information. Ex: Name, address, phone, email. Each file contains different contacts BUT they are all on formatted the SAME. What I have to do is put all these files together on ONE MASTER excel spreadsheet! What I don't understand is how can I select all of the files and get them on to that master??? What is the process to get the files on to the master file? I can't do a simple cut and paste because like I said it is going to be over 80 excel files. I know that I can highlight all files and right click to select open. But that opens up ALL of them. Is there a way that I can transfer the data from all those files? Please help me. I do appreciate the links to rondebruin.com but that is kinda confusing considering I am fairly new to macros and all. I just need someone that can suggest a better way of getting the files into one. I REALLY NEED HELP AND FAST!.. THANKS to you all!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LAST HOPE....Time is Ticking!!
Dim oFSO
Sub LoopFolders() Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object Dim file As Object Dim fldr Set Folder = oFSO.GetFolder(sPath) For Each fldr In Folder.Subfolders selectFiles fldr.Path Next fldr For Each file In Folder.Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path ' 'add code here to process the newly opened book Activeworkbook.Close SaveChanges:=False End If Next file End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Miss Atlanta" wrote in message ... Hi All! This is what I need to do: On a daily basis, I have to go into windows explorer onto a shared network drive and open up a folder. Lets call that folder "Monday" Inside the Monday folder there are as many as 80 excel files in that folder all consisting of contact names and information. Ex: Name, address, phone, email. Each file contains different contacts BUT they are all on formatted the SAME. What I have to do is put all these files together on ONE MASTER excel spreadsheet! What I don't understand is how can I select all of the files and get them on to that master??? What is the process to get the files on to the master file? I can't do a simple cut and paste because like I said it is going to be over 80 excel files. I know that I can highlight all files and right click to select open. But that opens up ALL of them. Is there a way that I can transfer the data from all those files? Please help me. I do appreciate the links to rondebruin.com but that is kinda confusing considering I am fairly new to macros and all. I just need someone that can suggest a better way of getting the files into one. I REALLY NEED HELP AND FAST!.. THANKS to you all!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LAST HOPE....Time is Ticking!!
If you don't understand VB code, you're only left with one (kinda ugly) option. Create a new file e.g. "All Data Combined" Assuming there's only one worksheet in each file that you want, open each workbook. Go into the required worksheet in each workbook, go to the Edit menu, choose Move or Copy Sheet, check the Make a Copy checkbox, and select your file "All Data Combined" in the "To book" section. Click OK. Repeat for the other 79 worksheets (yuk!). Probably take a bit over an hour. Like I said, kinda ugly. I also hope is doesn't go past Microsoft's "depends on system resources" limit to the number of worksheets in a workbook - though that's very unlikely. Bob's option's better, but if you've no other choice... Have fun. -- random1970 ------------------------------------------------------------------------ random1970's Profile: http://www.excelforum.com/member.php...o&userid=32112 View this thread: http://www.excelforum.com/showthread...hreadid=519241 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LAST HOPE....Time is Ticking!!
If you don't need the "entire" sheets brought in, but only specific cells,
then this formula, placed in your new workbook, will draw in the value in cell A1 of Sheet 1 of the file MondayOne.xls =[MondayOne.xls]Sheet1!A1 This one will bring in the same cell A1, but from the file MondayTwo.xls =[MondayTwo.xls]Sheet1!A1 The Filename, Sheet, and cell can be modified to suit your need. Vaya con Dios, Chuck, CABGx3 "Miss Atlanta" wrote: Hi All! This is what I need to do: On a daily basis, I have to go into windows explorer onto a shared network drive and open up a folder. Lets call that folder "Monday" Inside the Monday folder there are as many as 80 excel files in that folder all consisting of contact names and information. Ex: Name, address, phone, email. Each file contains different contacts BUT they are all on formatted the SAME. What I have to do is put all these files together on ONE MASTER excel spreadsheet! What I don't understand is how can I select all of the files and get them on to that master??? What is the process to get the files on to the master file? I can't do a simple cut and paste because like I said it is going to be over 80 excel files. I know that I can highlight all files and right click to select open. But that opens up ALL of them. Is there a way that I can transfer the data from all those files? Please help me. I do appreciate the links to rondebruin.com but that is kinda confusing considering I am fairly new to macros and all. I just need someone that can suggest a better way of getting the files into one. I REALLY NEED HELP AND FAST!.. THANKS to you all!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
LAST HOPE....Time is Ticking!!
hello,
Go to my web site and download this utility. It allows you to select a Target folder where the workbooks are held then Open those selected workbooks. It also allows you to deselect workbooks if they are not the ones you want. you will have to scroll down near the bottom of the page to find it ... http://www.geocities.com/excelmarksway SelectWorkbooks.xls I have actually written a script to copy data from all books to a single book but I haven't put it on my site yet and I don't have it with me. You might have specific information to inform me so I can make apprpriate changes. Send me your email and I will find it and send it to you. - -Mark "Miss Atlanta" wrote: Hi All! This is what I need to do: On a daily basis, I have to go into windows explorer onto a shared network drive and open up a folder. Lets call that folder "Monday" Inside the Monday folder there are as many as 80 excel files in that folder all consisting of contact names and information. Ex: Name, address, phone, email. Each file contains different contacts BUT they are all on formatted the SAME. What I have to do is put all these files together on ONE MASTER excel spreadsheet! What I don't understand is how can I select all of the files and get them on to that master??? What is the process to get the files on to the master file? I can't do a simple cut and paste because like I said it is going to be over 80 excel files. I know that I can highlight all files and right click to select open. But that opens up ALL of them. Is there a way that I can transfer the data from all those files? Please help me. I do appreciate the links to rondebruin.com but that is kinda confusing considering I am fairly new to macros and all. I just need someone that can suggest a better way of getting the files into one. I REALLY NEED HELP AND FAST!.. THANKS to you all!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time entry with am/pm and no colons | Excel Discussion (Misc queries) | |||
Time sheets | Excel Worksheet Functions | |||
Formula for a time card | Excel Worksheet Functions | |||
time sheet to calculate 2 different columns | Excel Worksheet Functions | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions |