Visio Guy

I need to open and refresh multiple Visio drawings that reside in Multiple folder locations.

I have an Access database with a table named "VisioFiles" and 150 Visio document names inside the table named FName, and a file directory location named FPath.

Inside this database I have a form where users can scroll through a picklist and select what documents they want to open and refresh. This could be from 1 to 150.

What I need to do is have some sort of loop code that can look at the table in access and go out to the file locations and update the selected documents the users made.

Any help would be greatly appreciated.
Many Thanks
FlowerGirl.

FlowerGirl,
1. Where do you want to put the code - in Access, executable, WSH-script?
2. What is the main difficulty - selection the data from Acces, updating Visio file?

An example of retrieving data from the database showed Yacine in http://visguy.com/vgforum/index.php?topic=1831.msg8253#msg8253
But the code should be adjusted to the correct database, table, field names, etc.
It would be convenient if the update function was implemented as a macro in Visio files. Then the main program will need only to call this macro. Such as in this example
        Set vApp = New Visio.InvisibleApp
        For i = 1 To filesUpd.Count
            Set vDoc = vApp.Documents.Open(filesUpd(i))
            Call vDoc.PDFs
            vDoc.Close
        Next
        vApp.Quit

Here means, that the macro "Sub PDFs()" is placed in ThisDocument module of Visio file.
This arrangement is good that the documents may contain some parameters that affect the updating of the document.
If the Visio files already exist and do not contain the macros, instead of the calling "Call vDoc.PDFs" you have to write more long program for updating the document and save the file.

If your 150 documents do not contain a macro that refreshes the document, then you can temporary add the module with macro, execute it and then remove.
The code below shows how it may be done. This is the WSH script.
Dim vApp
Dim vDoc
Const workPath = "D:\temp\"
'Trust is needed - Tools / Macros / Security / Trust access to Visual Basic Project
Set vApp = WScript.CreateObject("Visio.Application")
Set vDoc = vApp.Documents.Open(workPath & "doc1.vsd")
Set Proj = vApp.Vbe.VBProjects(1)
Set c = Proj.VBComponents.Import (workPath & "m1.bas") 'Import module containing macro
call vDoc.ExecuteLine("m1.ttt") 'Execute macro that refreshes the document
Proj.VBComponents.Remove c 'Remove module
vDoc.Save
vDoc.Close
vApp.Quit

Thanks for the reply Croc.

I need to launch this code from Access.
At this time, all the 150 Visio drawing documents have an internal macro that will fire on save.
That is pretty much all I think I need to do the Visio part of this.

My dilemma is getting Access set up as stated in my first post.

Thanks for your time.
FlowerGirl

Bump.

Any help on this one.
I have included my Access database that has a table called VisioFiles
I hope this helps with this effort.


Thanks for all your time.
FlowerGirl

FlowerGirl,
I made a small example. Look at it.
An example is placed in DropBox
https://www.dropbox.com/sh/r80ahbwouc8117o/AABltpItdQAvMrxIddlbOQz3a?dl=0

Thanks for your response. That site is blocked by my work.
Can I see it as a ZIP ?


Thanks again.
FlowerGirl

zip
----
The most interesting thing there is - it is code in the module "Module1" in .accdb.
Dim VisioFiles As Collection

Public Function RefreshVisioFiles()
    Dim db As Database
    Set db = CurrentDb
    Dim rs1 As Recordset
    strSQL = "Select * from VisioFiles"
    Set rs1 = db.OpenRecordset(strSQL)
    Debug.Print rs1.RecordCount
    Set VisioFiles = New Collection
    Do While Not rs1.EOF
        Debug.Print rs1!FName & ", " & rs1!FPath
        VisioFiles.Add rs1!FPath & rs1!FName
        rs1.MoveNext
    Loop
    rs1.Close
    Set rs1 = Nothing
    Set db = Nothing
    UpdateAll
    MsgBox "Finished"
End Function

Sub UpdateAll()
    Dim vApp As Visio.Application
    Set vApp = New Visio.Application
    'Dim vApp As Visio.InvisibleApp
    'Set vApp = New Visio.InvisibleApp
    For i = 1 To VisioFiles.Count
        Set vDoc = vApp.Documents.Open(VisioFiles(i))
        Call vDoc.UpdateMacro
        vDoc.Close
    Next
    vApp.Quit
End Sub

Thanks so much for the reply.  :-*  :-*

I will bolt this into my program and let you know how it worked.

FlowerGirl

It Works Great. Thanks so MUCH.

Now another situation. My bosses want to save a copy of each .vsd as they are open and saved.
I am enclosing a snapshot of the table view that I modified.
the FCopyPath would be the name of the new location where we need a copy saved.
We will need the FName added to that I would assume.


Thanks for your time with me.
FlowerGirl.

The path to the copy can be transmitted through another collection. For example:
VisioCopy.Add rs1!FCopyPath & rs1!FName
Or make a two-dimensional array.
And to make a copy is better to use the FileSystemObject than Visio.
Like that:
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f2 = fso.GetFile(VisioFiles(i))
   ' Copy the file to \temp.
   f2.Copy (VisioCopy(i))
   Set fso = Nothing

I attempted to make what you call "Another Collection" for creating a copy.
I did not do so well, I guess there are a few things I still don't yet understand.

Would you be able to put this example into the .accdb that you build and zip it to me?

Thanks for all your time and patience.
FlowerGirl  :-*

Done.

Thanks so much for this add on. It worked great.
The only change I made is to make the copy after the save.


Much Thanks.
FlowerGirl  :-* :-* :-*