What Excel Document am I currently linked to ?

[SubPlanner]

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.

[aledlund]

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

[SubPlanner]

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

[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

[Yacine]

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

[SubPlanner]

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.