Open an excel file, it without running code in it, copy its contents into another file

Cross posted at:
https://www.mrexcel.com/board/thread...sheet.1177007/
Reason:
No solution after a few days.

Hi,

I want a code that will browse for a file using a dialogue box.

After that I want to open the file. I do not want macros in this file to run (only if that's possible).

I run some codes when the workbook opens and I want to avoid that when I am opening it through this approach - that's triggering the open event from another workbook or using a code to open it. I was thinking of setting some global variables for that but I can't get the right way atm.

If I am able to open the file or workbook without running the scripts as described above, there are Worksheets that I want to match their names in both workbooks.

If they match, then I want to match or compare the contents or headers to see if they match as well.

If they match, then I want to copy from the file that I just opened into the workbook that I ran the code from.

Now let me describe my sheet names:

My sheets have the naming convention like:

PLAN 1
PLAN 2
PLAN 3
CAT 1
CAT 2
OBJ

so if both workbooks have PLAN 1, then I want to check if the contents on B6:M6 are the same on both sheets.

If they match then I want to copy the data from the just opened workbook ( B7:N & last used row) and paste into the workbook that I ran the code from.

And I repeat the same logic for all the other sheets;
PLAN 2
PLAN 3
CAT 1
CAT 2
OBJ

Afterwards, I close the workbook that I copied from.

And that was what I posted at:
https://www.mrexcel.com/board/thread...sheet.1177007/

And after a careful reading and research about my problem, I have come to realized that I need to be a bit clearer about what I wanna achieve.

So in simple terms, what I want to achieve is to get the contents of one workbook copied into another workbook.
But in order not to get the wrong contents into the destination workbook, that’s why I want to use the various criteria as described above.


That is, I want to check if the workbook that I am opening (source) contains these sheets:
PLAN 1
PLAN 2
PLAN 3
CAT 1
CAT 2
OBJ

Then do all those sheets have same values in these ranges

B6:M6

That is to say, is B6 on PLAN 1 sheet (source workbook) having the same header as B6 on PLAN 1 sheet (destination workbook)?

Then same analysis run for, C6 to M6 (PLAN 1 sheet), and same for the other sheets.


Once they pass that check, we proceed to copy as stated above.
And with the copying, I want to only copy data from the source if the range B7:M & last used row is not empty. If empty, we skip the copy for that particular sheet.

Now that I have expressed myself a bit clearer, I am confident that someone can pull me out.

I am ready for any solution that works. Thanks.

Demo workbook added

Originally Posted by cubangt

For the portion of preventing the code from running..

Try this in the main file that will be opening the other workbooks with the code you dont want to run.

Please Login or Register  to view this content.

okay thanks.

Gradually, I will get this fixed - I believe

So gradually, I have been able to open the source file without running the scripts inside it. That is a great progress for me. LOL.

Please Login or Register  to view this content.

I am stacked at the copying stage:

Please Login or Register  to view this content.

I am having the feeling, using For loops could get the job done - the issue here is, I can't figure out how to do the checks as I explained before.

But I am sure someone here can figure that out for me.

We still have to choose Application.AutomationSecurity, which can turn off the macros in opened files, without affecting the macros and events in the file from which the code has been started.

Artik

Originally Posted by Artik

We still have to choose Application.AutomationSecurity, which can turn off the macros in opened files, without affecting the macros and events in the file from which the code has been started.

Artik

Oh okay.
I came across that last time and I was confused, deciding which one to pick (application.displayalerts vs application.automationSecurity)

So from the link, if I did understand properly, then it means that with this code:

Please Login or Register  to view this content.

All macros in the OpenBook file will be disabled and turned back on after opening the OpenBook file?

In my case, I have scripts that run when the workbook opens and those that run when it closes as well.

So with the displayalerts as used before, I did two toggles - one during the workbook open event and the other during the close event.

When using the AutomationSecurity, do I have to do same or once I turn the macros off, I wait till I close the OpenBook file before enabling it again?

The procedure from post # 5 should look like this

Please Login or Register  to view this content.

And likewise, when you decide to use AutomationSecurity:

Please Login or Register  to view this content.

It doesn't make sense for Application properties to be turned on and off in every turn of the loop. This is only done once. Before and after the loop.

And since you only copy values from workbooks you are opening, this method is better (faster):

Please Login or Register  to view this content.

You can even more quickly, without opening the source files, using ADO.

Artik

Ok.

About the ADO, how is it done?

In this thread, you have an ADOGetValue function that you copy to your project. Below the rest of the code.

Please Login or Register  to view this content.

Artik

Wow, that's amazing.
I am loving this already.

Is there a simple way to make this range "B7:M60" dynamically expand like "B7:M" & last used row?

It would be quite difficult to calculate the last used row in a closed file. But you can specify, for example, the redundant range "B7: M60000", and the ADOGetValue function will only return as much data as there is in the closed workbook. But be careful! ADO can sense cells that are now empty, but once had a value. They will enter the returned data range as Empty. For now, I only know one way to get rid of such rows from the source worksheet. Entire rows must be deleted (Delete row command). Clearing (Clear command) does not help.

Artik

But be careful! ADO can sense cells that are now empty, but once had a value. They will enter the returned data range as Empty.

I don't understand this part.

Do you mean that if those cells were once empty, they will always be treated as empty cells?

Please explain further.

No, a little different. Remember that I don't know English, so sometimes the translator may not translate properly.

Assumption. At the end of the data in the source worksheet, there are cells that used to contain a value but that were cleared over time with Clear command. Let's call them "dirty". When you operate on a defined range in Excel, "dirty" cells are "invisible". They are treated like any other empty cells (never used).
The ADOGetValue function returns an array of values from a defined range. Since you are giving a redundant range, if there are "dirty" cells in it, they will be added to the array. If there are no such cells, the array will end at the last row with data.
In the case of your macro, you probably won't see the difference. Empty array items inserted into the target worksheet cells will simply result in blank cells. On the other hand, if you were to consolidate data from several sheets (one below the other), in the case of "dirty" cells in the source sheet, you could get blank rows between the consolidated data.
Hope it gets better explained now. If not, please practice adding and removing values in cells and view (in stepping mode) the contents of the array returned by the ADOGetValue function.

Artik

Hi Artik,

Is it possible for me to loop through this part?

Please Login or Register  to view this content.

I know how to loop worksheets:

Please Login or Register  to view this content.

But since there are two workbooks over here in question, I don't know which one will respond to the above syntax.

The aim is to get all the five sheets transfer their data to the destination file:
PLAN 1
PLAN 2
PLAN 3
CAT 1
CAT 2
OBJ

I think I have been able to resolve what I requested from my post #16

Please Login or Register  to view this content.

Thanks to you Artik for the time and help.