Refreshing Data

Started by kiler40, November 12, 2014, 08:52:16 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

kiler40

Hello again.
I have a small issue with refreshing external data.
How can i understand what is the external data ID ?`

  Application.ActiveDocument.DataRecordsets.ItemFromID(1).refresh
I have a document that uses several pages of external data. And everytime i change imput file, i have to record a macro that refreshesh the external data so i can see its ID. Is there a nicer way ?

Thanks in advance

Andrei

Yacine

Quotea document that uses several pages of external data
Can you tell more about this structure?
Yacine

aledlund

"DataRecordsets.ItemFromID(1)"

Each DataRecordset will have an ID field that uniquely identifies the datarecordset stored in the  document.

http://msdn.microsoft.com/en-us/library/office/ff765835(v=office.15).aspx

It is important to take note of the sentence at the bottom that says a recordset.id is never recycled, they are not reused. So if you insert a recordset, delete it, and reload it there will be a new ID assigned to the recordset.

Al

kiler40

Good Morning All,

First to Al response - i understand this. it is ok for me. the question is how to understand this ID number what is it.

Yacine - there is the both files Visio and Excel
https://dl.dropboxusercontent.com/u/10255673/video.zip
I must say at start that this is still work in progress and there are several /a lot/ things i intense to improve

When you open the Visio file there need be done some adjustments to make it running.
In Module 1 all "C:\Users\music\Desktop\video_new.xlsx" must be replaced with the location of the excel file
If all is ok at this point when you dbl click the reset button it should quick open the excel file and then closes it (resetting some values inside)

Next step remove all external data and add it again (this i know it changes the ID number as Al mentioned)
From excel file it need be added 2 sheets - for_visioR4S and for_visioS4S

Now they have the 2 new ID numbers.
To find them out and to make the file running i will normally record a macro with the name refresh_R4S that will overwrite the previous one and while recording i will refresh the for_visioR4S external data.
Next i will do the same with refresh_S4S

Now need to open VBA editor and find in NewMacros refresh_R4S and refresh_S4S and from there to see

Application.ActiveDocument.DataRecordsets.ItemFromID(18).Refresh
for both.
Next need find Link_R4S and Link_S4S and there  in
Application.ActiveWindow.Selection.AutomaticLink 18
need change for both the number after AutomaticLink.
Now if all is ok :) back in document when you dbl click "GO !!!" button it should start creating PDF files in "C:\Video".
Every time is a msg says "OK"  to hold the program because it runs the macro more fast than can refresh the page and save it as PDF. this is really stupid issue but with no clue how to clear...

The question is how to understand this external data IDs.
The reason is that i don`t want the people at work  to touch the macro - record/overwrite and etc the code.
If i can check somehow the ID and put it in some shape in the pade so the code reads it from this shape i think it can be a solution :)

Hope i didn`t bored you with my post :)

Thanks in advance!

Yacine

Hi Andrei,
I'll give a quick first impression instead of answering your question (sorry, didn't have the time to dive in your code).

The data seem to be missing a unique ID. Even the combination of connector, position and used occurs several times.
There are also empty rows. Wouldn't it make sence to filter them out.
Setting up this ID would allow to link automatically to the shapes

Addressing shapes by hard-coding their ID in a document is very bad. I did not find your shapes 18 and 19, so I couldn't even figure out what was intended.
If you work whit IDs make sure to store them somewhere
(Set shp = create new shape ....
Then you can either use it's ID shp.ID or work directly with the shape--> shp.refresh)
Yacine

kiler40

It is not a shape ID 18 and 19 :)
it is data records 18 and 19
and in the moment you opened in a different PC it is changing the ID number of the data record :)

Excel file is controlling the shapes in the page.
almost every shape have prop.row.used
if prop.row.used = 1 the shape is visible. if 0 -> invisible
in the beginning of every cycle the macro is setting all shapes prop.row.used = 0 so they are invisible. after that it is linking from excel what to be visible.

And if i change the file the ID of excel data file is different and the macro is not running.
I want to know what is the new ID of data set :)


Yacine

#6
Hi Andrei,
you may try getting the datarecordset by its name, instead of the ID.


Sub test()
Dim rs As DataRecordset
Dim rsID As Integer

rsID = getRecordset("for_visioR4S")
    If Not rsID = 0 Then
        Set rs = ActiveDocument.DataRecordsets.ItemFromID(rsID)
        Debug.Print rs.ID; rs.Name
        rs.Refresh
    End If
End Sub

Function getRecordset(rsName As String) As Integer
    getRecordset = 0
    For Each rs In ActiveDocument.DataRecordsets
        If rs.Name = rsName Then
            getRecordset = rs.ID
            Exit For
        End If
    Next rs
End Function


Still I believe that you need a unique ID. :)
Yacine

kiler40

this is the one i was looking for :)
Thanks again for your help.

P.S.
out of the main topic. How can i check if the file i`m creating (the PDF one) is exported so i can start with the next cycle ? and dump the OK msg box? Any idea ?

Yacine

You could loop till a condition that the file exists is fullfilled.
But is it really an issue? What happens if you go straight to the next export?
Yacine

kiler40

Yes.
It is looping until a condiuon is met. But it cannot refresh fast enough to fill /understand show/  all shpes. And the code is exporting as pdf.
I was thinking for some time delay (actually there is a code called "proba" in the file) but for now i cannot make it work. But for me this is not the nicest way to do.
Somehow need check if all previous actions are lead to end and after that to save as Pdf...

Yacine

I did not get your point.
Is there a problem if you start a new export, before the previous one is finished?
Yacine

kiler40

The problem is that you start new export before all the shapes are refreshed and have the new valuesh and what need be visible is visible.
In the exported file happens that information/shapes  are missing :)


kiler40

Well no. The problem is not the save :)
The thing is that this need wait until "link data to shapes" is done.
I`ll look around for wait function :) Some delay procedure :)

Yacine

Andrei,
the example was not intended to show the save operation in itself, but how you can make your code wait until the operation is finished. Any operation.
That's better than any delay procedure, where the processing time differs from machine to machine.
Yacine