Refresh Document with a VBA Macro in Excel

[sgonthey]

Hi,

I try to do a script to refresh a list of documents( beacause it takes  a long time for each document )

I put the name of the documents in an Excel Sheet and I do a macro but it doesn't work .

If someone can help me...It would be great !

Sub LaunchGeneration()

    Dim oMegaCurrentEnvironment As New MegaCurrentEnv
    Set oMegaRoot = oMegaCurrentEnvironment.GetRoot

    x = 9
    y = 4
      
Application.DisplayAlerts = False

While Sheet1.Cells(x, y).Value <> ""
        oDocument = oMegaRoot.GetSelection("Select [Document]  where nom = """ & Sheet1.Cells(x, y).Value & """).Item(1)
        Set refreshStatus = oDocument.RefreshDocument()
        If refreshStatus Then
            Sheet1.Cells(x, y + 1) = "Document généré"
        End If
        x = x + 1
Wend

 oMegaRoot.MegaCommit
 Set oMegaRoot = Nothing
 
  Application.DisplayAlerts = True
End Sub

[jhorber]

Hello sgonthey

I can confirm there is not MEGA API function that enables to check of a MEGA document is open or not.

I recommend you check if aMS Word API function is available.

[sgonthey]

Hello !

I have another question about  document generation.

I want to test if a document is already open before refresing it.

For the moment, the script works well, there is no error but  the document doesn't refresh if someone open it before!

Does a function  exist to test this ? 

Thanks for your help  !

Sandrine

[mbergamini · Accepted solution]

Hi,

I've tried in the Script editor inside MEGA this script:

Set oMegaRoot = GetRoot

oDocument = oMegaRoot.GetSelection("Select [Document]  where nom = ""Document.doc""").Item(1)

oDocument.InitializeDocument

Set refreshStatus = oDocument.RefreshDocument()
print refreshStatus 
If refreshStatus Then
    Print "Done"
End If

 

 and it worked for me.

So you have first to initialize the document, then refresh it.

Also RefreshDocument() returns an object with value:
• 1 when document update is in progress
• 0 if document update is completed.

So you will never enter that If block.

Hope I was helpful.

[sgonthey]

Hello !

The ddl was correctly  referenced.

I try  the action InitializeDocument

It works only with oDocument.InitializeDocument ( not with status)

But in fact, it doesn't really works ....

When I open  a document, whitout refresh before, it takes about 20 seconds to open

When I do the InitializeAction with my script, it takes   a very long time to open the document after ... like 2 minutes ! So it's not interesting because the issue of my script it's to improve the duration of document generations for the users ...

Is it possible to  open a document in Word application with a script ? It could be a solution

Thanks for  your response  !

[lrobinet]

The reference for your VB projects outside of MEGA should be : mg_mapp.dll (present in the "System" folder of the MEGA installation)

To verify the code syntax , I just performed a test in my MEGA Script editor:

Set RefreshStatus = oDocument.RefreshDocument

Print RefreshStatus

I have no errors, but the RefreshStatus returns a value of "0" and my document is not "refreshed" in the sense that the content doesn't reflect changes in the repository.

What works well for me:

Set RefreshStatus = oDocument.InitializeDocument

Upon the next opening of the document it correctly reflects the repository.

[sgonthey]

Thanks for your response.

The connection is OK but the script stops on the action refresh ... I have to force the stop  with Ctrl+ alt+Suppr

I didn't reference the ddl for the moment so I'm going to do it and I 'll tell you a feedfack

thanks

[lrobinet]

When you say "it doesn't work", your scipt stops at what line?

Connection to the current environment?

Did you reference the DLL for "MEGA Application Automation Components" in your VB project in your Excel sheet?