Import datasheets from multiple protected workbooks to single workbook for processing using VBA.

RHUL

New Member
Joined
May 10, 2008
Messages
9
This may be more of an ‘approach’ question, but any coding to help with achieving aspects of it would be welcome!

I am frequently provided with a spreadsheet of data (in .xlsm format) that I need to process, however it appears to be protected to prevent people accessing the internal macros that the provider uses to compile the data. The data can be selected, copied and pasted manually. I am writing a spreadsheet using VBA to process the data contained within this spreadsheet, and other spreadsheets that I receive that will be of a similar format. The first step is to combine these datasets into Workbook1. Workbook2 and Workbook3 can be closed once data has been copied. The first stumbling block is to actually open the protected workbook and prevent the ‘open’ macro from failing. Saving the source workbooks as a .xls file first is a possibility if required, but as this will be used by people who frequently save their files as the wrong format and the default settings hide file extensions, a macro that does this for them is preferred. The initial outline is provided below so hopefully more experienced people than me can determine the best approach!

Workbook1 = my spreadsheet, which will be opened by the user and contains the macros. A button opens the browse feature so the user can select the workbook(s) that need to be imported into a new sheet (“ImportedData”).
This 'test' code opens the workbook, but produces a 'Compile error in hidden module: Thisworkbook' error, which I assume is due to the opened workbook being protected:

Code:
Sub Btn_FileOpen_Click()

Dim MyWB As String
MyWB = Application.GetOpenFilename()

Workbooks.Open (MyWB)
    Windows(MyWB).Activate
    Sheets("Data Summary").Select
    Cells.Select
    Range("B4:L30").Activate
    Selection.Copy
    Windows("Workbook1.xlsm").Activate
    Sheets("ImportedData").Select
    ActiveSheet.Paste
End Sub

Workbook2 = protected workbook containing source data, provided by others. This contains a worksheet (“Data Summary”) which is an aggregation of all the data within the other sheets. This is the sheet I wish to copy the usedrange and paste into a new sheet (“ImportedData”) in Workbook1.

Workbook3 = As Workbook2, but with different data. If a row text exists in Column A that is the same as that imported into Workbook1, the column will be appended to the right of the last column containing data in Workbook1 and the data assigned to that row. If the text is not present in any of the rows in Column A, the row text from the imported data will be appended to the last row in Column A and the data will be inserted into the new column formed to the right of the last column of previously imported data.

A simplified example of the desired outcome is provided below:

Workbook2:

Store1Store2
Apples13
Oranges86
Pears42

<tbody>
</tbody>

Workbook3:

Store3Store4
Apples73
Pears94
Bananas28

<tbody>
</tbody>

Workbook1:

Store1Store2Store3Store4
Apples1373
Oranges86
Pears4294
Bananas28

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top