Bypass "Data Link Properties"; Changing connection path w/ VBA

[theoneandonlysmith]

I have a procedure I used with Office 2003 to change connection paths of a template Excel file to the current MDB before refreshing. Now, with 2010, a "Data Link Properties" window pops up and stops all automation. How do I bypass this window? Set oxl = CreateObject("Excel.Application") oxl.Visible = True oxl.DisplayAlerts = False oxl.Workbooks.Open Template For Each ws In oxl.Workbooks(Template).Worksheets For Each lo In ws.ListObjects If lo.SourceType = 3 Then oldmdb = Mid(lo.QueryTable.Connection, _ InStr(1, lo.QueryTable.Connection, "Data Source=") + 12, _ InStr(InStr(1, lo.QueryTable.Connection, "Data Source="), _ lo.QueryTable.Connection, ";") _ - (InStr(1, lo.QueryTable.Connection, "Data Source=") + 12)) lo.QueryTable.Connection = Replace(lo.QueryTable.Connection, _ oldmdb, newPath & "\" & newmdb, , , 1) lo.QueryTable.SourceDataFile = Replace(lo.QueryTable.SourceDataFile, _ oldmdb, newPath & "\" & newmdb) lo.QueryTable.textfileparsetype = 2 lo.QueryTable.Refresh False 'Displays Data Link Properties window lo.QueryTable.Delete End If Next lo Next ws

[theoneandonlysmith]

Ok... I figured it out. Because the MDB running the code was also the same MDB containing the data, Excel had trouble sharing the connection. I had to force the connection mode to "Share Deny None". The following is how I did it. oldmdb = Mid(lo.QueryTable.Connection, InStr(1, lo.QueryTable.Connection, "Data Source=") + 12, InStr(InStr(1, lo.QueryTable.Connection, "Data Source="), lo.QueryTable.Connection, ";") - (InStr(1, lo.QueryTable.Connection, "Data Source=") + 12)) rfmode = Mid(lo.QueryTable.Connection, InStr(1, lo.QueryTable.Connection, ";Mode="), InStr(InStr(1, lo.QueryTable.Connection, ";Mode=") + 1, lo.QueryTable.Connection, ";") - (InStr(1, lo.QueryTable.Connection, ";Mode="))) lo.QueryTable.Connection = Replace(Replace(lo.QueryTable.Connection, oldmdb, newloc & "\" & newmdb, , , 1), rfmode, ";Mode=Share Deny None") lo.QueryTable.SourceDataFile = Replace(lo.QueryTable.SourceDataFile, oldmdb, newloc & "\" & newmdb) lo.QueryTable.Refresh False lo.QueryTable.Delete