Consolidation of password protected workbooks with a twist

[Philsargent]

Hi Guys,

This is my first post and was wondering if you could help me out with this massive challenge?

I have to consolidate up to 10 spreadsheets into one spreadsheet every month. All I want to do is create a macro to copy each childs spreadsheet data and paste under each other in the parent sheet but with a few small complications.....

Firstly each child is password protected but they are all the same ("abc") but I don’t want to enter the passwords 10 times to extract the data from each.

I have to do this process for another 8 parent accounts so I was hoping it was possible to create a standard parent workbook that it would have on one tab a list of locations of the spreadsheets with the name of the spreadsheet so i could change them if a new file was created;

Name Location File name
ABC1 C:\Documents and Settings\Files\ ABC1Feb
AFC2 C:\Documents and Settings\Files\ AFC2Feb
ABE4 C:\Documents and Settings\Files\ ABE4Feb

(I don’t know if that was possible so I will accept any other ideas!)

All child accounts are set out exactly the same however some childs may have new codes added each month so it may vary in rows going down in the page but the column titles will always stay the same. I would only want to copy the data from A to U in my examples.

The next challenge is when the data is taken from each spreadsheet and pasted in the master spreadsheet would be to have a new column created in column A to state the name of the file it was taken from (eg "ABC1").

If this is possible then please can someone help me out? Or give any other suggestions?

I have attached my example files.

Many thanks.

Phil

Consolidation file.xlsAFC2Feb12.xlsABC1Feb12.xls

[hensucker]

This would be better to query each workbook in order and have the passwords clearly marked out in the VBA already..
it would contact each childworkbook access with password grab the data and return it to the master, does that sounds like a good idea?

[Philsargent]

HI. Open to any suggestions however not sure how I would do this.

[hensucker]

Ok well i can not upload documents as i am at work currently and it does not allow me to do so.

Open VB in Excel by pressing Alt+F11, then create a module and add the following code.

Please Login or Register  to view this content.

Then once this is done save the workbook and switch back to Excel, from there locate on the command bar: Tools/Macro/Macros and run the macro from there.
As long as all your workbooks are named correctly and in the right directory as you stated this should pull the information from the child workbook for you.

Else another workaround is to record a Macro as you setup a query to each individual workbook you can do this by going to the command bar in excel: Data/Import External Data/Import Data.
From here you locate your workbook, set your parameters and run it, Excel records all that and creates a module for you to tweak.use and study.

Good Luck!

Let me know and dont forget Rep!

[Philsargent]

Thanks for you help mate.

I have copied it in and gave it a go but for some reason I can get it to work.

I can write basic macros and quite advanced with formulas but this level goes right over my head. I am keen to learn so I will keep giving it a go.

[hensucker]

It is hard to adjust from where i am to where you are without having the workbooks, and i cant upload.
Try the suggested way of recording the macro and importing the data.
Can you enlighten me as to what happens when you try the code?

[Philsargent]

as you suggested I can get it working by recording and I am learning as it goes along. However it will only paste in text and now number data. Is something simple I need to change?

No rush as I can understand you are at work - So please feel free to take a quick look when you have a spare min when you are not busy and can access the files.

Thanks again.

[hensucker]

I have used this feature many times before and it generally works with figures, i often found it too be quite volatile in some situations it would miss the odd column or work for say 8 of the workbooks then miss the 9th completely with no reasons as to why, you see troubleshooting something that doesnt error is pretty hard.
I will have a look when i get home phil and see if i can adapt your workbooks then send them back to you.
Hopefully i can complete this project for you.

[hensucker]

I have sent you a PM as an update.