I need to be able to change the excel document I am linked to without using the "Link Data" tool bar

I have a page named "Update". On that page I have a text box named "LocationC".
What I will have the users doing is, putting a new location/doc name in this text box then press a button to complete the data linking to the new Location/Document.
I am using this code to "Open and save the Excel document. Perhaps this would be a good place to insert the "Data Connection" abilities as well.
Oh, and of course, I will need to loop through the Excel tabs and get each one linked.
I have a Tab named "Start" and one named "Complete" and another named "Timestamp".

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

End Function

Any help would be appreciated.   

SubPlanner

Hi,

I wonder if this question would benefit from being broken down a bit?  I'm not clear on whether you're just asking for what a good approach might be or whether you code is failing in some way.  If it's the former then I'd like to understand in a more general sense what you're trying to do (ie more broadly than the button issue).  If the code is failing, can you highlight what line it's failing on and what you were expecting.

Best regards

John

Sorry If I did not state my issue correctly.
The code I pasted works fine.
What I needed was some extra "Add On" code.

My Visio drawing is linked to an external excel workbook and from time to time I may change the location and name of this workbook.
What I needed was some VBA code that would relink the Visio data source from the old location to the new location based on information entered into a text box.

Let me know if I am stating this clearly.
Thanks for your help.
SubPlanner

Seems like you need to generate a VBA pop-up form that allows user to enter the file location information.  Then, this info is used to replace the new address, which I guess is your "Me.LocationC"

Example of Pop-Up form and using it to control subsequent code actions is here:  http://visguy.com/vgforum/index.php?topic=6373.msg26266#msg26266

You may want to google VBA (Visio) pop up forms.

Wapperdude

Try this

http://blog.bvisual.net/2015/03/13/updating-the-file-path-of-data-linked-excel-tables-in-visio/

al Edlund

Thanks for the reply. I used some tips from within but in the end this problem was solved with the help of one of our young programmers on staff.

NOTE: Used in Visio 2010:

Here's how this example works.
First, I have 2 Excel workbooks saved in a Location on the C:\ drive of my PC.
  named Excel Data A and Excel Data B.
These 2 Excel workbooks each have 8 tabs inside them so they are basically The same. They just have different names and different data.

1. On a Visio drawing page I have a text box where folks can type in the new file location and excel document name.
      Example  C:\Visio Data\Excel Data A.xlsx
2. They will press a button that calls the code.

     Here is the code.

Sub changeSource(strSource As String)

    Dim DiagramServices As Integer
    DiagramServices = ActiveDocument.DiagramServicesEnabled
    ActiveDocument.DiagramServicesEnabled = visServiceVersion140
    Dim strTab As String
    Dim i As Integer
         
    For i = 1 To 8
     
Application.ActiveDocument.DataRecordsets.Item(i).DataConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "User ID=Admin;Data Source=" + strSource + ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;" _
& """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;" _
& "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" _
& "Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;" _
& "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;" _
& "Jet OLEDB:Bypass UserInfo Validation=False"

           
        Select Case I  'change the names as needed.
            Case 1
                strTab = "TimeLineStart"
            Case 2
                strTab = "TimeLineComp"
            Case 3
                strTab = "TimeStamp"
            Case 4
                strTab = "Weekday"
            Case 5
                strTab = "Month Start"
            Case 6
                strTab = "Starts"
            Case 7
                strTab = "Completes"
            Case 8
                strTab = "DateRefresh"
        End Select
       
        Application.ActiveDocument.DataRecordsets.Item(i).CommandString = "select * from `" + strTab + "$`"
        ActiveDocument.DiagramServicesEnabled = DiagramServices
        Application.ActiveDocument.DataRecordsets.Item(i).Refresh
    Next i
End Sub

You will see the tab names from inside the Excel document named within the case statement.
So after I have the Visio drawing completed with external excel data linked to my shapes or timelines, I can change the link to the excel workbooks to see a different result in my diagram.

If there are any questions from other folks reading this post, please post a reply here and I will get back to you.


SubPlanner