Data Connection

[Abvlecxe]

Hi,
I have two excel files, worksheet1 is linked to worksheet2 via a data connection query to retrieve table information.

Everything works fine when the files are in the same location, however when I send the files to someone else on a different server the connection falls over. Other than guide the user through changing the data connection file path what alternative solutions is there so there is as little user requirement as possible?

I thought of potentially a macro button which when pressed asked for the new connection file path, the user types that into the input box and the macro updates the connection, is this possible? Or are there better solutions?

Thanks in advance

 

[ranman256]

the connection should not use Drive letters, instead have full UNC paths: \\server\folder\file

the user must have access rights to the location \\server\folders

 

[Abvlecxe]

I have come across this code which requires the user toselect a new source file, however when selected the query connections don’tactually update.

Any ideas?

Sub xlTest()

Dim i As Long

Dim cnt As Long

Dim cn


cnt = ActiveWorkbook.Connections.Count


'Choose a File

strPath = Application.GetOpenFilename(Title:="Choosea file", _

FileFilter:="Excel Files *.xls* (*.xls*),")

If strPath = False Then

MsgBox "Nofile selected. Exiting now.", vbExclamation


Exit Sub

End If

'Change Connection source filepath for each connection

For i = cnt To 1 Step -1

Set cn =ActiveWorkbook.Connections.Item(i)

cn.OLEDBConnection.SourceDataFile = strPath

Next i


End Sub