Hide Object Activation

[oldSarge98]

Working in Excel 2010 with an embedded Word Document. I want to export(save) the Word Document Object as a .docx file in the file system folder. The following code does the job but when the object is activated it briefly flashes on the screen. I want to avoid that. Here's a sample of the code I've developed:

Sub SaveEmbeddedFile()
    
    Dim xlsWst                  As Worksheet
    Dim strArchivePath          As String
    Dim objOle                  As OLEObject
    Dim appWord                 As Word.Application
    Dim wrdDoc                  As Word.Document
    
    strArchivePath = Me.Path & "\attachments"
 
    Set xlsWst = ThisWorkbook.Worksheets("attachments")
    Set objOle = xlsWst.OLEObjects("HelpDocument")
    
    objOle.Visible = False [highlight #FCE94F]' does not prevent the flash[/highlight]
[highlight #FCE94F]    ' this is where the word OLE object flashes on the screen[/highlight]
    objOle.Activate

    Set wrdDoc = objOle.Object

    wrdDoc.Application.Visible = False
    wrdDoc.SaveAs2 Filename:=strArchivePath & "\help.docx"
    wrdDoc.Close

    Set wrdDoc = Nothing

[highlight #FCE94F]    ' if the following two lines are omitted we are left with
    ' an instance of WINWORD.EXE in the system processes
    ' but, I'm concerned that doing this will interfere with any
    ' Word instances that the user might have had opened before
    ' having this module executed
[/highlight]    Set appWord = GetObject(Class:="Word.Application")

    appWord.Quit

    Set appWord = Nothing

End Sub 'SaveEmbeddedFile

Any recommendations?

 

[SkipVought]

HI,

In what application does this code reside?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[oldSarge98]

Microsoft Excel 2010, "ThisWorkbook" module.

 

[SkipVought]

So why do you Activate objOLE?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[oldSarge98]

So why do you Activate objOLE?

Failing to activate the OLE object makes it unavailable to the Word document object and the procedure fails.

If you know a better way to save an embedded Word object from an Excel workbook I'm all ears(eyes).

BTW, just tested that latter piece about closing out the WINWORD.EXE process and it definately does interfere with any previously opened Word documents. I hate to leave a process dangling.

 

[SkipVought]

Have you COMPILED your VBA Project without error?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[oldSarge98]

A little bit of explanation in case you're interested.

A couple of years ago I dveloped an Excel workbook that calculated a set of financial values that drove five data chart sheets in the same workbook. When the user clicked a command button it opened a PowerPoint template and placed the chart on separate pages in the presentation then saved it to the desktop. This automated workbook has passed through five users in the ensuing time and it seems that regardless of the users technical abilities (we're talking financial wizards and senior project managers) they cannot comprehend the idea that the PowerPoint template has to reside in the same file folder as the Excel workbook, the name of the template cannot be changed, and the design of the templante cannot be changed unless you know how to save it as a template vs a presentation. So . . . my idea is to embed the PowerPoint template and my latest bright idea, a MS Word help document into the workbook that will be droped into the appropriate location every time either is needed. That means they can screw up the template all they want but the one I need will be there unmolested at the time of need.

This has worked well with my Access applications but Excel seems to be a stuborn animal when it comes to embedding and exporting Office file objects.

 

[SkipVought]

...and the COMPILE?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[oldSarge98]

Have you COMPILED your VBA Project without error?

Yep, do that every time.

 

[oldSarge98]

Well, ran out of work hours now. Heading home. Resume pissing into the wind in the morning . . .

 

[SkipVought]

And you have a reference set to the Microsoft Word Object Library?

And what other Libraries?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[oldSarge98]

References:
[ul]
[li]Visual Basic For Applications[/li]
[li]Microsoft Excel 14.0 Object Library[/li]
[li]OLE Automation[/li]
[li]Microsoft Office 14.0 Object Library[/li]
[li]Microsoft PowerPoint 14.0 Object Library[/li]
[li]Microsoft Forms 2.0 Object Library[/li]
[li]Microsoft Word 14.0 Library[/li]
[/ul]

I am assuming had I not had the Word referece I would have encountered an error already. I have no errors, just unsatisfactory operation.

Have you ever had success exporting an embedded Word document object from and Excel workbook to a file in a folder?

 

[SkipVought]

I was attempting to run your code and need to run the wickets. Still have not been successful.

No, I rarely use OLE. I will, on occasion embed a chart in PowerPoint. But if a user community is to use such an application, the source application, usually Excel in this case, needs to be on a server accessible by all.

Personally I'd rather stick a chart pic in the display application than link: removes an ambiguity.

BTW, I still don't understand why you need to activate the link object. In almost every case Selecting or Activating an object is a needless step. I can thing of only one exception. Maybe this is number two.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[oldSarge98]

Having searched extensively yesterday and this morning I have come to the conclusion that the Excel OLE object provides no viable method to extract the object to the file system without activating then manually saving the file from its native application. In my situation this is a non starter and I'll just have to take another approach.

Thank you for your time.

 

[strongm]

>I still don't understand why you need to activate the link object

It's a limitation when Linking (i.e Create from File)

No such limitation with embedded files (i.e Create New)


So we have a solution ...

If Linking, then objOle's SourceName property contains the Link source, which includes the filename, so we can just copy that file to the new location.
And, if we embed, then we don't have the Activate problem

 

[oldSarge98]

Linking to the file would not be a solution to my goal to provide a single file package. It is however similar to what I have resorted to which would be providing all three files (Excel, Word, and Presentation template). I'll just have to trust the user to leave them alone. In my Excel spreadsheet I have a command button that has a hyperlink to the Word file so if they need to review it all they have to do is click the button.

All in all it'll probably be a workable solution. I've left the embedded files in the workbook so they'll always be handy if I shoul have to pay a visit to "reset" the installation.

Thanks again . . .

 

[strongm]

>Linking to the file would not be a solution

The requirement to use Activate and the consequent flicker that you describe in the OP suggest that this is exactly what you are actually doing, though.

 

[combo]

Do not activate OLE object, no flashing even without hiding:

Sub SaveEmbeddedFile2()
    
    Dim xlsWst                  As Worksheet
    Dim strArchivePath          As String
    Dim objOle                  As OLEObject
    Dim wrdDoc                  As Word.Document
    
    strArchivePath = ThisWorkbook.Path & "\attachments"
    Set xlsWst = ThisWorkbook.Worksheets("attachments")
    Set objOle = xlsWst.OLEObjects("HelpDocument")
    Set wrdDoc = objOle.Object
    wrdDoc.SaveAs2 Filename:=strArchivePath & "\help.docx"
    Set wrdDoc = Nothing
   
End Sub 'SaveEmbeddedFile2

combo

 

[strongm]

Yes, quite, as I said above, if the OLEObject represents an embedded file then activation is not required. But if it represents a Linked file, then you have to activate (and the activation causes the reported flicker).

 

[combo]

One can read OLEType, if it's xlOLELink (=0), SourceName contains file path (needs some processing), the suurce can just be copied.

combo