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:
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:
<tbody>
</tbody>
Workbook3:
<tbody>
</tbody>
Workbook1:
<tbody>
</tbody>
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 SubWorkbook2 = 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:
| Store1 | Store2 | |
|---|---|---|
| Apples | 1 | 3 |
| Oranges | 8 | 6 |
| Pears | 4 | 2 |
<tbody>
</tbody>
Workbook3:
| Store3 | Store4 | |
|---|---|---|
| Apples | 7 | 3 |
| Pears | 9 | 4 |
| Bananas | 2 | 8 |
<tbody>
</tbody>
Workbook1:
| Store1 | Store2 | Store3 | Store4 | |
|---|---|---|---|---|
| Apples | 1 | 3 | 7 | 3 |
| Oranges | 8 | 6 | ||
| Pears | 4 | 2 | 9 | 4 |
| Bananas | 2 | 8 |
<tbody>
</tbody>
Last edited:
