How to move data from many spreadsheets to one

[davegb]

I have 73 workbooks that I want to copy data from one of the
worksheets and put it all into a single workbook (not one of the 73)
which will then tally the results. Before I can even start to write
code, I have a problem I'm not sure how to deal with. If I write a For
Each loop to open each workbook in the folder in turn, if the tally
workbook is in the same directory, it's going to try to open that one
and, I imagine, will hang the macro because that workbook
(ThisWorkbook) will already be open. So does the tally workbook have
to be in a different directory? Or, can I test each workbook as it is
selected , before it is opened) to see if it's ThisWorkbook and have
it skip ThisWorkbook and go on the the next? Any suggestions as to the
best way to handle this kind of situation?

 

[RocketDude]

DaveGB,

Take a look at Ron de Bruin's merge routine and see if it does what you
want:

http://www.rondebruin.nl/merge.htm

Good luck,

RocketDude

 

[davegb]

DaveGB,

Take a look at Ron de Bruin's merge routine and see if it does what you
want:

http://www.rondebruin.nl/merge.htm

Good luck,

RocketDude






- Show quoted text -

Thanks for your reply. My situation is different and is not a straight
merge. It would take a long time to give the history of how I got to
where I am. Suffice it to say that many mistakes have been made and
now I find myself with 70+ workbooks from which I need to extract the
data from one worksheet in each workbook, paste it into a master
workbook, then run an existing macro to tally the numerical data and
save the text data into the master. Then move on to the next workbook.
It really shouldn't be that difficult, I just need a little help in
figuring out an approach so I don't waste a lot of time going up blind
alleys.

My original question remains: Is it workable to do a For Each on every
workbook in the folder (is this the best, or even a good, way to go
through all the workbooks?), test each in turn to skip opening
ThisWorkboook (the master), copy the data sheet, paste it into the
master data sheet, run the existing macro, close the data workbook and
go on to the next? Can anyone see any obvious reasons why this
wouldn't work?

Thanks in advance!

 

[Peter T]

Adapt to your needs

Sub test()
Dim cnt As Long, i As Long
Dim bIsOpen As Boolean
Dim sFldr As String
Dim colFiles As Collection
Dim wb As Workbook

sFldr = ThisWorkbook.Path & "\"

cnt = FilesToCol(sFldr, colFiles)
If cnt Then
For i = 1 To cnt
Set wb = Nothing
bIsOpen = False
If UCase$(colFiles(i)) <> UCase$(ThisWorkbook.Name) Then

On Error Resume Next
Set wb = Workbooks(colFiles(i))
On Error GoTo 0

bIsOpen = Not wb Is Nothing
If Not bIsOpen Then
Set wb = Workbooks.Open(sFldr & colFiles(i))
End If

' do copy stuff with wb here <<<

If Not bIsOpen Then wb.Close False
End If
Next
Else

MsgBox "no files found"
End If

End Sub

Function FilesToCol(sPath As String, c As Collection) As Long
Dim sFile As String

Set c = New Collection
Call Dir("nul")
sFile = Dir(sPath & "*.xls*") '
Do While Len(sFile)
c.Add sFile
sFile = Dir()
Loop
FilesToCol = c.Count
End Function

Regards,
Peter T

 

[Ron de Bruin]

Hi davegb

There is code on this page if the add-in is not what you want
http://www.rondebruin.nl/copy3.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

DaveGB,

Take a look at Ron de Bruin's merge routine and see if it does what you
want:

http://www.rondebruin.nl/merge.htm

Good luck,

RocketDude






- Show quoted text -

Thanks for your reply. My situation is different and is not a straight
merge. It would take a long time to give the history of how I got to
where I am. Suffice it to say that many mistakes have been made and
now I find myself with 70+ workbooks from which I need to extract the
data from one worksheet in each workbook, paste it into a master
workbook, then run an existing macro to tally the numerical data and
save the text data into the master. Then move on to the next workbook.
It really shouldn't be that difficult, I just need a little help in
figuring out an approach so I don't waste a lot of time going up blind
alleys.

My original question remains: Is it workable to do a For Each on every
workbook in the folder (is this the best, or even a good, way to go
through all the workbooks?), test each in turn to skip opening
ThisWorkboook (the master), copy the data sheet, paste it into the
master data sheet, run the existing macro, close the data workbook and
go on to the next? Can anyone see any obvious reasons why this
wouldn't work?

Thanks in advance!

 

[salgud]

I have 73 workbooks that I want to copy data from one of the
worksheets and put it all into a single workbook (not one of the 73)
which will then tally the results. Before I can even start to write
code, I have a problem I'm not sure how to deal with. If I write a For
Each loop to open each workbook in the folder in turn, if the tally
workbook is in the same directory, it's going to try to open that one
and, I imagine, will hang the macro because that workbook
(ThisWorkbook) will already be open. So does the tally workbook have
to be in a different directory? Or, can I test each workbook as it is
selected , before it is opened) to see if it's ThisWorkbook and have
it skip ThisWorkbook and go on the the next? Any suggestions as to the
best way to handle this kind of situation?

Thanks to everyone who made suggestions. There's a lot of material here, so
when I get to this later today, I'll go through it and figure out what
works best for me.