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
Quotea document that uses several pages of external data
Can you tell more about this structure?
"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
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 (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!
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)
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 :)
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. :)
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 ?
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?
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...
I did not get your point.
Is there a problem if you start a new export, before the previous one is finished?
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 :)
Would this help?
http://www.vbaexpress.com/forum/showthread.php?29785-How-to-wait-till-the-save-operation-is-completed
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 :)
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.
Andrei,
How frustrating and exhilarating at the same time. I just made a parallel between your problem and one of mine.
There are many entities linked to a certain status (configuration in your case) and they need to be shown one after the other.
In my previous attempts, I set up quite heavy databases and code to switch from one status to the next.
Your approach is so much lighter and straight forward.
I tried it and it WORKS!
Thank you.
In my case I try to highlight the status of a process line (pipeworks, valves and pumps) according to either a certain sequence or a state of the process line.
I make some snapshots (automatically) of the drawing and comment the picture).
He he :)
You are welcome ! You know - some times the best idea comes from the stupid :D
And I still haven't figured out how to wait until all shapes on the page receive information from the data...
Have you tried the code I posted previously?
DoEvents
Do
Loop Until
I don`t know how to implement it.
I don`t have problem with save.
on the page there is a blue rectangle that must show letter and number.
when is selected in the shape data it says that this is connector 999
the problem is with this shape. the macro exports to PDF too fast, and this shape is not getting the letter and number.
It stays "0"
Interesting part is that name of exported file is the text from this shape.... :/
I don`t know how to explain it :( Sorry.
I tried GoTo
Something like
Line1:
If shape.cells(prop.connector) = 0 then
goto line1
else
end if
And it stops. the sape stays with 0 and it continues to return to line1
The suggestion from the Yacine should work fine - try "DoEvents" before export, it should do the trick. This VBA function is equivalent to the Application.DoEvents in .NET, or just pumping windows message loop until there are no messages (in principle). Visio processes messages & events using the Windows message queue.
Also, you could try the following approach:
Subscribe to the "ApplicationIdle" (or "NoEventsPending" which may come earlier) event and do your pdf export from that even handler.
Hi Andrei
the doevents command allows to enter a loop without loosing control over the application.
In your case the code could look something like:
Public R4S_udated As Boolean
Public S4S_udated As Boolean
Sub RUN_R4S()
unlinkAll
clear_R4S
refresh_R4S
link_R4S 'this Sub must set R4S to true
Do
DoEvents
Loop Until r4s_updated
ExportToPDF1
r4s_updated = False
NEXT_R4S
End Sub
It loops and not doing anything. Something is wrong :/
Sure! It waits for R4S_updated to become true.
You need to set this variable to true at the end of the previous routine. ie, when the job is finished.
I'm not sure if it is enough to write an assignment at the end of the sub-routine, or if you need to check for a "recordset updated"-something. Please google this by yourself.