Author Topic: Auto draw batch process wrt run times  (Read 33816 times)

0 Members and 1 Guest are viewing this topic.

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: Auto draw batch process wrt run times
« Reply #45 on: August 31, 2010, 01:08:54 PM »
You say the Timestamp is after the update stored in a shape, too.
If it is always the same shape, you know it's name, or even better it's ID.
You could search in your VBA code for that shape and read the ShapeData Value from it, that you need.
Store it in a variable and use that instaead of the NOW().

visio

  • Jr. Member
  • **
  • Posts: 38
Re: Auto draw batch process wrt run times
« Reply #46 on: September 08, 2010, 04:24:59 AM »
The last word on this..no really.

Got some code that does the trick for the autosave. This can be used to scan any visio page for a shape and extract a property. The tricky bit was working out what pages and shapes are actually called. This code is called as a Visio macro.

After the diagram data is updated it scans the current page (set to the Sessions page) for a particular shape (by shape name as these are unique in this case a shape called upr_sten - look at format > special to find shape name). Once found it checks if a property is valid for that shape and if it is then sets a variable, as a string, equal to the value. In this case its the date in the format dd/mm/yyyy which is no use as a filename extension as the spashes mean directories. To get round this the mid command is used extract the relevent fields of the string so the output is in the form ddmmyyyy. This is used as a timestamp for saving the diagram as a webpage.

Finally the Visio sheet is saved as a webpage, silent is set to true to prevent the saved page being automatically opened in a browser following its creation.


Sub Update()

    'Set variables to be used to scan shapes on page

    'Visio Page object
    Dim pagObj As Visio.Page
    'Visio Shapes collection
    Dim shpsObj As Visio.Shapes
    'Visio Shape object
    Dim shpObj As Visio.Shape
    'Visio Cell object
    Dim celObj As Visio.Cell
    'Array to hold purchase order info
    Dim OrderInfo() As String
    'Counter
    Dim iShapeCount As Integer
    'Counter
    Dim i As Integer
   
    'Update the Visio diagram using data from the Excel spreadsheet (data source)

    Dim UndoScopeID1 As Long
    UndoScopeID1 = Application.BeginUndoScope("Refresh Data")
    Application.ActiveDocument.DataRecordsets.ItemFromID(5).Refresh
    Application.EndUndoScope UndoScopeID1, True

    Dim UndoScopeID2 As Long
    UndoScopeID2 = Application.BeginUndoScope("Refresh Data")
    Application.ActiveDocument.DataRecordsets.ItemFromID(9).Refresh
    Application.EndUndoScope UndoScopeID2, True

    Dim UndoScopeID3 As Long
    UndoScopeID3 = Application.BeginUndoScope("Refresh Data")
    Application.ActiveDocument.DataRecordsets.ItemFromID(10).Refresh
    Application.EndUndoScope UndoScopeID3, True
   
    Application.ActiveDocument.Save
   
    'Update of data now complete, now find the batch date from data stored in a shape property - shape is called upr_sten   
   
    'Extract the batch run date from the Visio diagram and use as date to save webpage
    'Get the active page
    Application.ActiveWindow.Page = Application.ActiveDocument.Pages.ItemU("Sessions")
    Set pagObj = ActivePage
    'Get the Shapes collection of the page.
    Set shpsObj = pagObj.Shapes
    'Total number of shapes.
    iShapeCount = shpsObj.Count

   'For each shape on the page check if the shape name matches the target shape
   
    For i = 1 To iShapeCount
        'Get the i'th shape.
        Set shpObj = shpsObj(i)
       
        If shpObj.Name = "upr_sten" Then
        'Get the date from shape upr_sten as a string.
            If shpObj.CellExists("Prop._VisDM_Date", visExistsLocally) Then
                Set celObj = shpObj.Cells("Prop._VisDM_Date")
            End If
        End If
        'Release Shape object.
        Set shpObj = Nothing
    Next
   
    'Save as webpage, the cadate changes the date number to dd/mm/yyyy and the mid bits print the date without the '/'
    'Silent mode used to prevent webpage starting up automatically

Application.Addons("SaveAsWeb").Run "/silent=True /target=c:\batch_time_master_" & Mid(CDate(celObj), 1, 2) & Mid(CDate(celObj), 4, 2) & Mid(CDate(celObj), 7, 4) & ".htm"

End Sub

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: Auto draw batch process wrt run times
« Reply #47 on: September 08, 2010, 09:38:32 AM »
Good to see, that it works. So my following tipps may not neccessary, but maybe you can use them anyway:

Quote
Application.ActiveWindow.Page = Application.ActiveDocument.Pages.ItemU("Sessions")
Set pagObj = ActivePage

could be shorter with

Code
Set pagObj = Application.ActiveDocument.Pages.ItemU("Sessions")

unless it is neccessary to change the ActivePage for the later saving as Web.


Because you have many shapes on your page it could be conveniant, to leave the For-Next loop, with "Exit For", when you hav found the right shape. So your code won't have to make many unneccessary loops.


Also it may be better to loop through the shapes with sth. like
For Each shaObj in shpsObj

Next

I read in an Excel-Book, that it is recommended to use this technik (in Excel) because it is faster. Maybe that's true for Visio, too.
And it has the benefit, that you need less variables.

By the way?What is the Orderinfo() as String for?

Just my 2 cents.

visio

  • Jr. Member
  • **
  • Posts: 38
Re: Auto draw batch process wrt run times
« Reply #48 on: September 09, 2010, 09:34:04 AM »
The order variable is a red herring and has been removed.

Changed the line as you suggested. Couldn't figure syntax out at first, and the 2 lines ended up being a workaround.

The If startement now has extra line so if the correct shape is found then i is set to iShapecount essentially stopping the search.

For i = 1 To iShapeCount
        'Get the i'th shape.
        Set shpObj = shpsObj(i)
       
        If shpObj.Name = "upr_sten" Then
        'Get the date from shape upr_sten as a string.
            If shpObj.CellExists("Prop._VisDM_Date", visExistsLocally) Then
                Set celObj = shpObj.Cells("Prop._VisDM_Date")
                i = iShapeCount
            End If
        End If
        'Release Shape object.
        Set shpObj = Nothing
    Next

Last bit now.

Writing an external vbs script to open excel, run a macro to update data from source then open visio and run the macro to update visio and save as webpage.

Both macros work fine when run manually. The code I have for the external vbs sript will do the excel bit and for Visio it will open visio then open the doc but when it cones to run the macro having difficulty. Looks like syntax is not the same as for Excel. The ideal scanario would be the schedule the vbs script to run daily and produce a daily webpage from updated visio drawing based on the updated excel data.

My code below, relatively simple. The excel macro is called "Visio_data" and the Visio macro is called "Update"

Dim excel
Dim visio

set excel = createobject("Excel.Application") '##create excel object
set visio = createobject("Visio.Application") '##create visio object

excel.visible = true
excel.workbooks.open "c:\batch\Visio_data.xls"
excel.Run "Visio_Data"
excel.quit

visio.visible = true
visio.documents.open "c:\batch\batch_time_master.vsd"
visio.run "Update"
visio.quit

The visio.run command is not being liked by Visio, error is 'does not support this property or method:run'. Tried other commands like visio.addons("RunMacro").Run ("Update") or visio.cmdrun but to no avail. Any ideas?

Jumpy

  • Hero Member
  • *****
  • Posts: 1061
Re: Auto draw batch process wrt run times
« Reply #49 on: September 09, 2010, 01:37:36 PM »
I don't know, how to trigger Macros in Visio from another app, but maybe the following is a workarround:

You could place a Macro in the ThisDocument Modul of the Visio drawing. It is reacts to the document opened event and starts your real macro:

Code
Private Sub Document_DocumentOpened(ByVal doc As IVDocument)
  'Eventually you could place a small timeout here before you start the macro
  'to enable Excel to be fully updated and closed again
  'and Visio to be completly started
  'Only if neccessary

  YourMacroNameHere

End Sub

Didn't try it out, but should work. At least if document_opened event is triggered, when Visio is opened via VB.

--------
Another thing came to mind, concerning the search for the right shape loop. If you always use the same drawing or template for this, it may be that the Item number of that shape is always the same. You could test this with going to the right page and starting the following proc.

Code
Sub Nr_Test()
Dim i As Integer

For i = 1 to ActivePage.Shapes.Count
 If ActivePage.Shapes(i).Name = "urp_sten" Then
    MsgBox i
 End If
Next i
End Sub

If i is always the same you could simply adress your shape in the code without any loop at all, with sth. like (if i is for example 5):

Set celObj = shpsObj(5).Cells("Prop._VisDM_Date")

In this case you could mabe do much from the above in a One-Liner:
Code
Dim Dat as String
Dat = ActiveDocument.Pages("Name of the right page").Shapes(TheNumberFromAbove).Cells("Prop._VisDM_Date").ResultStr("")

Than you could use a String-Function to cut out the "/":
Code
Dat = Replace(Dat,"/","")

and even that could be put in one line but would not be easy to look at any longer...

visio

  • Jr. Member
  • **
  • Posts: 38
Re: Auto draw batch process wrt run times
« Reply #50 on: September 10, 2010, 05:28:08 AM »
The run macro on Doc open does the trick. The external vb script opens Visio then opens the doc which automaticlly runs the update macro, the effect is the same as if the external vb script did everything. Thanks