Visio Guy

Hi folks,
       I have a functioning timeline style Visio document linked to an external excel workbook that is working fine. I just need a little something extra.
I wanted the Visio document to display the current linked excel document name and address.

I am using the following code to refresh the external excel document. Works good.
I do it this way because I did not want the users to have to open the excel document for any reason.
This excel document gets its updates from multiple sources.

Note: the (ME.LocationName) is a text box located inside the Visio document that allows the users to point to a different excel workbook by address location and name should the need arise.

Function OpenSaveExcel()
Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open(Me.LocationName)
objXLApp.Application.Visible = False
With objXLApp.ActiveWorkbook
.RefreshAll
.Save
.Close
End With
objXLApp.Quit
End Function

Thanks for your time.

the recordset properties has the query string stored as one of the properties.

https://msdn.microsoft.com/EN-US/library/office/dn508175.aspx

hth,
al

Thanks for the response, I had help with the fancy VBA that I posted earlier, so I may not be the sharpest tool.

I looked at the Application.ActiveDocument.DataRecordsets from a macro I recorded as an effort to understand this logic.

I can't seem to get the correct coding into the Open Document routine.
I am attempting to get a text box within the Visio document to display the current linked address for the excel document it is reading data from.

Thanks for your patience.

SubPlanner

If left alone long enough I will get my own solutions. Thought I would share this one.
Since my Visio drawing is linked to an excel workbook I figured I can get the file and path name from excel then display it in the visio drawing as I see fit.

In excel paste these formula's into any cell.
Path:  =LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)
File:    =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

SubPlanner

Thanks for the submission and sorry for the lack of help.
It probably was too special.

Thanks Yacine for your follow up.
I do appreciate your help in this forum as others do I am sure.

I will try my best to keep a two way flow of information with my problems and solutions.


SubPlanner.