Creating Summary Workbook from Multiple Workbooks

[kklaus]

I need help created a summary workbook form multiple workbooks located in one directory.

The multiple workbooks are password protected and have a hidden worksheet. This hidden worksheet contains one row of data that I need copied as a new row into a Sumarry workbook.

For Example:

I have 20 worksbooks with a hidden worksheet called "LinkedWS" that has row 3 completed with data.

I need 1 workbook with 1 worksheet with 20 rows of data from these workbooks.

Any help would be great!

Thanks,
Kathy

[mark007]

What have you got so far?

To open workbooks you can use:


dim wb as workbook
set wb=workbooks.open("path to file.xls")

You can copy and paste the row using:



wb.sheets("LinkedWS").rows(3).copy
thisworkbook.sheets("summary").range("a65536").end(xlup).offset(1,0).pastespecial xlPasteAll

Have a look into the filesystemobject for lopping through all the files in the directory.

I have to dash so can't type anymore at the moment.

:)

[kklaus]

Here's what I have so far... I've pieced it together from other code in this forum to try to make it fit my needs.


Sub Retrieve_Worksheets()
FilePth = "C:\Documents and Settings\KCKLAU\My Documents\Environmental\January 2005"
Fname = Dir(FilePth & "*.xls")
Do While Fname <> ""
Workbooks.Open FPath & Fname
Sheets(LinkedWS).Copy After:=Workbooks("Environmental Summary.xls").Sheets(Workbooks("Environmental Summary.xls").Sheets.Count)
Workbooks(Fname).Close SaveChanges:=False
Fname = Dir
Loop
End Sub

Sub Sort()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name = "LinkedWS" Then
Sheets(ws.Name).Rows(3).Copy
Sheets("Environmental Summary").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
xlPasteAll
End If
Next ws
Application.CutCopyMode = False
End Sub


Thanks,
Kathy

[mark007]

Basically there. This should do it:



Sub Retrieve_Worksheets()
dim wb as Workbook
FilePth = "C:\Documents and Settings\KCKLAU\My Documents\Environmental\January 2005"
Fname = Dir(FilePth & "*.xls")
Do While Fname <> ""
set wb=Workbooks.Open(FPath & Fname)
wb.Sheets(LinkedWS).Rows(3).Copy
Thisworkbook.Sheets("Environmental Summary").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
Workbooks(Fname).Close SaveChanges:=False
Fname = Dir
Loop
End Sub


:)

[kklaus]

I must be doing something wrong. I've pasted the following code the (General) section.


Sub Retrieve_Worksheets()
Dim wb As Workbook
FilePth = "C:\Documents and Settings\KCKLAU\My Documents\Environmental\January 2005"
Fname = Dir(FilePth & "*.xls")
Do While Fname <> ""
Set wb = Workbooks.Open(FPath & Fname)
wb.Sheets(LinkedWS).Rows(3).Copy
ThisWorkbook.Sheets("Environmental Summary").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
' xlPasteAll
Workbooks(Fname).Close SaveChanges:=False
Fname = Dir
Loop
End Sub

When I run it I get the following error:

Compile Error: Invalid use of Property

and it highlights the xlPasteAll line.

I commented that line out and reran the code and nothing happens.

Also, just to make sure I have this code in the Targert workbook
(Environmental Summary.xls). I open this sheet and go to Tools -> Macros and run the Retrieve Worksheets macro.

I expected to see records appear in the Environmental Summary.xls but nothing happens. Could it be because the Source workbooks are password protected?

Thanks,
Kathy

[Zack Barresse]

Hi Kathy,

Put your xlPasteAll in parenthasis.




Sub Retrieve_Worksheets()
Dim wb As Workbook
FilePth = "C:\Documents and Settings\KCKLAU\My Documents\Environmental\January 2005"
Fname = Dir(FilePth & "*.xls")
Do While Fname <> ""
Set wb = Workbooks.Open(FPath & Fname)
wb.Sheets(LinkedWS).Rows(3).Copy
ThisWorkbook.Sheets("Environmental Summary").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Workbooks(Fname).Close SaveChanges:=False
Fname = Dir
Loop
End Sub


Also, check out this thread (http://www.vbaexpress.com/vbatags.htm) to see how the VBA tags work. :) (I edited your first post so you could see the difference.)

[kklaus]

Thanks for letting me know. As you can tell I'm a newbie.

I no longer get the error since adding the parens (thanks!). However, when I run the code nothing happens.

I stepped into the code and it appears that the problem may be with this line:

Fname = Dir(FilePth & "*.xls")

It says Fname=Empty after I've run it.

I do have 2 xls files in that January 2005 directory so I'm not sure why it's not opening those.

I've attached the Summary and one Source sheet if that helps.

Thanks!
Kathy

[kklaus]

I've continued to work on this and I think I've made some progress, but I'm still running into a problem. It opens the first source worksheet now - Yeah!

However, now I get the following error:
Subscript out of range

When I click on Debug, it highlights the following line of code:

wb.Sheets(LinkedWS).Rows(3).Copy

Here's my most recent code...



Sub Retrieve_Worksheets()
Dim FilePth As String
Dim FName As String
Dim wb As Workbook
FilePth = "C:\Documents and Settings\KCKLAU\My Documents\Environmental\January 2005"
FName = Dir(FilePth & "\*.xls")
Do While FName <> ""
Set wb = Workbooks.Open(FilePth & "\" & FName)
wb.Sheets(LinkedWS).Rows(3).Copy
ThisWorkbook.Sheets("Environmental Summary").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Workbooks(FName).Close SaveChanges:=False
FName = Dir
Loop
End Sub

Any help would be great!!

Thanks,
Kathy

[Ken Puls]

Hi there,

Try changing the line to:


wb.Sheets("LinkedWS").Rows(3).Copy

HTH,

[Killian]

Hi there
I'm having a bit of a dull morning instaaling software so I thought I'd knock up some code with comments on this on (attached in a workbook)
You might want to add some error checking and maybe some extra features on the form

Enjoy K :-)

[mark007]

Just as a BTW, the reason it wasn't working was because you had the xlPasteAll on the worng line - it should have followed the previous line with a spce in between. As you are not assigning a result to anything the parenthesis aren't needed. Adding them will actually evaluate xlPasteAll and then pass it to the function. There is an article on my site about it that might be of use:

http://www.thecodenet.com/articles.php?id=5

:)

[kklaus]

kpuls,
Thanks for that - I should have seen that! I think this is 95% done. It's copying each row from the source files and pasting it in the summary sheet!

The only issue I'm having now is with the data that is pasted in. That row of data in the source files references other cells in various worksheets. I thought it would be easier to put all the data I needed in one row on one sheet, then just copy that one row into the Summary workbook.

But what's happening is on the Summary sheet the references are get pasted in instead of the data.
Such as:
='C:\Documents and Settings\KCKLAU\My Documents\Environmental\January 2005\[EnvironmentalTest.xls]Policy Assessment'!B6
or the formula.

Any suggestions on how to just copy the data in that cell and not the formula or reference?

Thanks,
Kathy

[mark007]

Replace:


.PasteSpecial xlPasteAll

With:


.PasteSpecial xlPasteValues

Assuming he uses that within his code.

:)

[kklaus]

Killian,

That is so cool and obviously way beyond my knowledge. I love this because it will allow the user who's going to be using it the chance to change the directory each month without having to go into the code.

Step 1 works great - it finds and displays the worksheets in the directory I specified.

Step 2 creates a new workbook but the data is not pasted in it. I was going to look at the code and see if I could figure it out on my own, but I don't know how to see that code. I've never worked with a form like that before and don't know how to find the code behind the Create Summary button.

Thanks,

Kathy

[kklaus]

Thanks Mark!! That worked!

Any suggestions on keeping the formatting for the date, currency, etc fields? I formatted the cells in the Source workbook but it doesn't seem to work.

Thanks,

Kathy

[kklaus]

One more thing...

When a source sheet closes I get a popup saying "There is a large amount of data on the clipboard. Do you want to be able to paste this information into another program later?"

So if I have 50 source workbooks I have to click the No button 50 times.

Is there any way to turn this off in the code?

[Killian]

Hi again,
I don't think you would have too much of a problem programming with forms - I actually think it's easier to follow because rather than running one huge stream of code from beginning to end, you're using events (like a button click or a textbox change) to trigger seperate routines. To see the form code, find the UserForm1 in the VBE project explorer and select 'View code'. There's not actually that much code and i think I commented most of it

But to answer your last question, you can use

Application.DisplayAlerts = False
to switch those messages off (don't forget to switch it back to True at the end since it applies to the Application object, not just your workbook)

[kklaus]

Thanks to everyone!! I got this working and the users are going to love it.


Thanks for all the help. You guys are great!!!

:kiss