Run Time Error 13 when updating Data Source for OLEDB connection

ajoh2

New Member
Joined
Nov 30, 2006
Messages
8
I wanted an easy way for a user to change the file path for an excel report that gets run every day. The workbook I'm building is bringing the data from that separate report into a worksheet via an OLEDB connected Table. To do so, I recorded a macro to change the location. Then I modified the code to change the Data Source section into a variable which the user would select. The works fine, until the file selected has a path + filename which is longer than 141 characters. For some reason it then sends a Run Time error '13' Type Mismatch. I've searched all over, and I cannot for the life of me find a solution. I've worked in a check for this case into the code to work around it, but I'd much rather find a solution so this isn't an issue any more.

Can someone help me modify the code so the Data Source section can be updated to whatever the variable is from the users file selection without being limited to the 141 characters?

Much appreciated.


My code is below and at **I've added the condition to check for which is causing the error:

Sub A_Change_Query_File()
current_location = Application.ActiveWorkbook.Path
ChDir (current_location)

Choose_file:
getfilePath = Application.GetOpenFilename(Title:="Select a File to Import")
FileType = ".xlsx"

If getfilePath = False Then
Exit Sub 'Prompt was Cancelled so exiting

Else
FileName = Dir(getfilePath)
filePath = Replace(getfilePath, "\" & FileName, "")
path_name = filePath & "\" & FileName

'**Have to check to length of the chosen file. If it's greater than 141 code won't run and I get Run Time error 13 Type Mismatch**
If Len(path_name) > 141 Then
If MsgBox("The path is too deep. The file is located too far down within nested folders." & vbNewLine & vbNewLine & _
"File Location:" & vbNewLine & path_name & vbNewLine & vbNewLine & "Please move the report to a folder higher up and Retry." _
, vbRetryCancel) = vbCancel Then
Exit Sub
Else
GoTo Choose_file: 'File is nested into too many folders. I don't know why this matters, but for now it's a bug that I'm coding around
End If
Else

Debug.Print path_name, Len(path_name)

With ActiveWorkbook.Connections("Maximo_Update").OLEDBConnection
.BackgroundQuery = False
.CommandText = Array("Report$")
.CommandType = xlCmdTable
.Connection = Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & path_name & ";Mode=Share Deny Write;Extended Prop" _
, _
"erties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=3" _
, _
"7;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Datab" _
, _
"ase Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=F" _
, _
"alse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass Us" _
, _
"erInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
)
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = path_name
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Maximo_Update")
.name = "Maximo_Update"
.Description = ""
End With
ActiveWorkbook.Connections("Maximo_Update").Refresh
Calculate
End If
End If
End Sub
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
674
Please write declarations.

like this.
Option Explicit
Sub A_Change_Query_File()
Dim current_location As String, getfilePath As String, FileType As String, Filename As String, filePath As String, path_name As String

And try it again.
 

ajoh2

New Member
Joined
Nov 30, 2006
Messages
8
Unfortunately that doesn't do anything for me. Still getting the same error if the path is greater than 141 characters.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,234
Messages
5,443,271
Members
405,222
Latest member
Summer01

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top