Run-time error '13' setting long file path in Connections

[mcswiggen]

Hi

I've created a workbook with tables that are refreshed on opening the workbook, their source is queries on other workbook sheets.

In order to facilitate the distribution of the workbook I need to set the Connection properties of the MS Query on opening.

I have the script below, which when the file is saved high in the directory structure runs fine, but when deeper (longer file name/path string) throws a "Run-time error '13'" error. I've tried breaking the string up and using arrays but can't get it to work.

Any help appreciated.

Sub SetupConnection()

Dim str1 As String
Dim str As String
Dim str1_1 As String
Dim str1_2 As String
Dim str_1 As String
Dim str_2 As String

str1 = ActiveWorkbook.Path
str = str1 & "\" & ActiveWorkbook.Name

str1_1 = Left(str1, 50)
str1_2 = Mid(str1, 51, 100)

str_1 = Left(str, 50)
str_2 = Mid(str, 51, 100)

ActiveWorkbook.Connections("Query from Excel Files2").ODBCConnection.Connection = Array(Array( _
"ODBC;DSN=Excel Files;DBQ=" & str & ";DefaultDir=" & str1_1 & "), Array(" & str1_2 & ";DriverId=1046;MaxBufferSize=2048;Pa" _
), Array("geTimeout=5;"))

ActiveWorkbook.Connections("Query from Excel Files2").Refresh

End Sub

 

[mcswiggen]

Found the solution, frustratingly simple.

Just don't use an array -

Sub SetupConnection()

Application.EnableEvents = False

Dim str1 As String
Dim str As String

str1 = ActiveWorkbook.Path
str = str1 & "\" & ActiveWorkbook.Name

With ActiveWorkbook.Connections("Query from Excel Files2")
.ODBCConnection.Connection = "ODBC;DSN=Excel Files;DBQ=" & str & ";DefaultDir=" & str1 & ";DriverId=1046;MaxBufferSize=2048;Pa)"
End With

Application.EnableEvents = True

End Sub

and the problem is solved!

 

[anthonyk1]

Hello,




Simply download new Long Path Tool software that simply allows you to work easily on Long Path files.


Thank you.