Automating Export to Database

[skwyer]

I'm trying to automate an export of shape data to an Access database. I can use the run method of addon("export to database") for each of the several pages I have in my document.

But then I don't know how to pass parameters so that the table name is set by the code and the user doesn't have to click "OK" for each of the pages.

I have been searching high and low and I can't find any reference for what the syntax needs to be and what I've tried isn't working.

Here is my code currently

Code Select Expand


    Dim doc As Visio.Document
    Dim Pages As Visio.Pages
    Dim Page As Visio.Page
    Dim Adn As Addon

    Dim PageName As String
    Dim strCommand As String

    Set doc = Application.ActiveDocument
    Set Pages = doc.Pages
    Set Adn = Visio.Application.Addons("Export to Database...")

    For Each Page In Pages
        PageName = Page.Name
        strCommand = "/table=PageName"
        Adn.Run ("/S-RUN" & strCommand)
        'Application.AlertResponse = 1
    Next


Could somebody please help me in getting the syntax correct?

Thank you.

[skwyer]

Okay, I got most of it to work. Now I'm trying to get it to just hit OK for each of the dialog boxes that pop up in the Export to Database event. I used AlertResponse = 1. That got the popups to go away but it doesn't seem to be actually clicking on OK. The code runs but nothing exports.

Is there something other than AlertResponse that should be used with regard to the Export to Database addon? Or is there some other setting I need to use?

Here is my code

Code Select Expand


    Dim myDoc As Visio.Document
    Dim myPages As Visio.Pages
    Dim myPage As Visio.Page
    Dim myAddon As Addon
    Dim myCell As Visio.Cell
    Dim BookTitle As String
    Dim PageName As String
    Dim TableName As String

    Set myDoc = Application.ActiveDocument
    Set myPages = myDoc.Pages
    Set myAddon = Visio.Application.Addons("Export to Database...")

    BookTitle = myPages("Print Production").Shapes("Title").Text

    'set alert response to automatically choose "OK" on modal popups
    Application.AlertResponse = 1

    'loop through all pages
    For Each myPage In myPages
        PageName = myPage.Name
        TableName = BookTitle & "_" & PageName
        Application.ActiveWindow.Page = myPages.Item(PageName) 'page must be active page to set User.ODBCExportTable cell
        Set myCell = myDoc.Pages.Item(PageName).PageSheet.Cells("User.ODBCExportTable")
        myCell.Formula = """" & TableName & """"
        myAddon.Run ("")
    Next

    'reset application alert response
    Application.AlertResponse = 0

[skwyer]

Okay, I found that there is another command called "Re-export" which is not in the list of add-ons run from the tool bar. But this effectively reruns the last export exactly and does not give any prompts. So by changing the add-on that's run to re-export then I could get rid of the AlertResponse and get a fully automated export of all pages, with no user prompts.

Here is my code.

Code Select Expand


Dim myDoc As Visio.Document
    Dim myPages As Visio.Pages
    Dim myPage As Visio.Page
    Dim myAddon As Addon
    Dim myCell As Visio.Cell
    Dim BookTitle As String
    Dim PageName As String
    Dim TableName As String

    Set myDoc = Application.ActiveDocument
    Set myPages = myDoc.Pages
    Set myAddon = Visio.Application.Addons("DBREX")

    BookTitle = myPages("Print Production").Shapes("Title").Text

    'loop through all pages
    For Each myPage In myPages
        PageName = myPage.Name
        TableName = BookTitle & "_" & PageName
        Application.ActiveWindow.Page = myPages.Item(PageName) 'page must be active page to set User.ODBCExportTable cell
        Set myCell = myDoc.Pages.Item(PageName).PageSheet.Cells("User.ODBCExportTable")
        myCell.Formula = """" & TableName & """"
        myAddon.Run ("")
    Next


Now the code runs through each page, resets the table name and exports to an Access database