LAST HOPE....Time is Ticking!!

[Guest]

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!!!

 

[Bob Phillips]

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)

 

[random1970]

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.

 

[Guest]

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

 

[Guest]

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