Importing sheet from another WB, without triggering wb_open msgbox

[davedave24]

Hello

I'm trying to import a sheet from another workbook (this part is fine). Let's say import Sheet1 from workbook B into workbook A.

The issue is when workbook B is opened, it runs a subroutine that results in a MsgBox.

Is there:

a) a way to surpess this MsgBox only when importing the sheet, from B into A (so it functions as normal if the user is just using B;

or

b) a method of extracting the sheet without actually opening the other workbook?

For a) I've been looking at this post, but I don't really understand what the fella is saying in this part:

Modify the line where you call the original sub by throwing in False as an extra parameter. This results in OriginalSubNameyour set of parameters, False. This way you don't suppress the dialog box by default, but you do when you use it in your sub.

 

[SkipVought]

Hi,

Please describe your shhet that you are importing. Is it simply a table, where the data alone could be "imported" rather than the entire sheet?

 

[SkipVought]

Alternatively, you could add a delay, say 15 seconds, and a sheet with a switch value. If the switch value is set, the macro is not executed. The 15 seconds is enough time to set the value. Don't forget to reset the switch before the macro ends.

 

[davedave24]

Hi Skip

The sheet is essientially just text data, but I need to retain the source formatting (specifically the cell colour). The problem is that the source workbook is a little large and takes several seconds to open over the network, and secondly that it runs that macro that displays that messagebox.

I'm playing around with a few ideas:

1) import the data without opening the workbook. I can do this but it loses all formatting.

2) open the workbook, copy the sheet, paste it into the new workbook. This works, retains the formatting, but is slow to open and we get the messagebox.

3) created a dummy sheet and simply link the cells to the source, and updating the cell colours of the linked cells using the macro below in workbook_activate():

Sub LinkedColours()
    Dim Cel     As Range
    Dim RefCel  As Range
    On Error Resume Next
    For Each Cel In ActiveSheet.UsedRange
       If Cel.HasFormula Then
          Set RefCel = Evaluate(Mid(Cel.Formula, 2))
          Cel.Interior.Color = RefCel.Interior.Color
       End If
    Next Cel
End Sub

This seems to be the best hope but I'm experimenting with the problem of it opening source as Read Only mode if someone has it open already.

Any ideas on how best to do this?

 

[SkipVought]

Is the formatting associated with the logic of the data values? Normally, this is what Conditional Formatting accomplishes.