Excel 2013 ADO connection to an XLS file blow ups

I am trying to extract a long string value from a closed worksheet and ExecuteExcel4Macro returns Error 2015 - the string  is longer than 255 chars.

I  have switched to ADO and am having problems with the connection string, it generates an error

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\john\Documents\BSMA\Financials\OpenInvoices\2017-03\2017-03-29 243 Carnival UK.xls;Extended Properties="Excel 12.0;HDR=No;";

The Sub Routine Code itself is based on a sample I found on the Web

Public Sub GetCrewData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

    Dim rsCon As Object
    Dim rsData As Object
    Dim szConnect As String
    Dim szSQL As String
    Dim lCount As Long

    ' Create the connection string.
    If Header = False Then
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No;"";"
        End If
    Else
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=Yes"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"
        End If
    End If

    If SourceSheet = "" Then
        ' workbook level name
        szSQL = "SELECT * FROM " & SourceRange$ & ";"
    Else
        ' worksheet level name or range
        szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
    End If

    On Error GoTo SomethingWrong

    Set rsCon = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")

    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, adOpenStatic

    ' Check to make sure we received data and copy the data
    If Not rsData.EOF Then

        If Header = False Then
            TargetRange.Cells(1, 1).CopyFromRecordset rsData
        Else
            'Add the header cell in each column if the last argument is True
            If UseHeaderRow Then
                For lCount = 0 To rsData.Fields.Count - 1
                    TargetRange.Cells(1, 1 + lCount).Value = _
                    rsData.Fields(lCount).Name
                Next lCount
                TargetRange.Cells(2, 1).CopyFromRecordset rsData
            Else
                TargetRange.Cells(1, 1).CopyFromRecordset rsData
            End If
        End If

    Else
        MsgBox "No records returned from : " & SourceFile, vbCritical
    End If

    ' Clean up our Recordset object.
    rsData.Close
    Set rsData = Nothing
    rsCon.Close
    Set rsCon = Nothing
    Exit Sub

SomethingWrong:
    MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile & " " & SourceSheet & " " & SourceRange & " " & TargetRange, _
           vbExclamation, "Error"
    On Error GoTo 0

End Sub

Open in new window


The Source is correct when I copy the path into file explorer up pops the spreadsheet.

Incidentally My first thought was to just open the file get the data and close it without saving but ExecuteExcel4Macro must have a lock on it which it does not clear when it returns an error so when I open it in the error handling section it says it is already open.

I have already extracted about 30 pieces of data from the file using ExecuteExcel4Macro so all the references etc. are correct

If ThisWorkbook.ActiveSheet.Range("H29") > 0 Then
            Vsheet = "ROSF. 103"
            Vaddress = "F33"
            VSourceFile = Vfilepath & Vtargetfile
            Set VtargetRange = Range("H40")
            On Error GoTo ToMuchData

            'Call GetCrewData(VSourceFile, Vsheet, Vaddress, VtargetRange, False, False)
            VNameList = GetData(Vfilepath, Vtargetfile, Vsheet, Vaddress)
ToMuchData:
            If IsError(VNameList) = True Then
                 Call GetCrewData(VSourceFile, Vsheet, Vaddress, VtargetRange, False, False)
                            
            End If
            Call StripText
        End If

Open in new window


Open in new window


Any Ideas?

As always this is driving  me crazy  !
John
JOKL4444Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Hi,

First, this must be an old code, and unless it is intended to be running on win XP OS (or older), there is no point choosing between ACE and JET driver (useless defensive programming in action!), just use ACE, it support any Excel format.

Second, the workbook's name have no business in the SQL statement, you must use either the worksheet's name, or range's name you want to query (again, useless defensive programming in action!).

Third, ACE driver use the 5 first rows to determine each column data type, and this can lead to unexpected results, so th best way to deal with this is to consider all data type as string. This is achieved by adding the IMEX=1 extended property in the connection string.
(see https://www.connectionstrings.com/ace-oledb-12-0/ for details).

Fourth, concerning your issue where the excel file remain open, always properly close the ADODB objects (recordsets and connection), even in your error handler.
this can be achieved with the following code (assuming cn is an open connection, and rs is an open recordset):
If Not(rs Is Nothing) Then
    If (rs.State = adStateOpen[) Then
        rs.close
    End If
    Set rs = Nothing
End If
If Not(cn is nothing) Then
    If(cn.state = adStateOpen) Then
        cn.close
    End If
    Set cn = Nothing
End If

Open in new window

Also, ensure that the workbook is properly closed before performing any SQL query against it.
(mixing OLE automation with ADO on the same excel file isn't the best idea)

Fifth, concerning defensive programming:
As I stated, it is useless, and pollute your code with inifficient and hard to read variables checking statements. Plus,it can hide rampant bugs.
Today, it is better to do contract programming:
A functions expect valid parameters to perform its work, if parameters are wrong, this is a programming error, and it should crash the program.
This is done via the debug.assert statement.
This statement check if the expression given as parameter is true or false. If the expression evaluate to false, the program crash.
Ex:
You write a function assuming a non empty string as parameter, you'll write:
Public Function testFunction(ByVal in As String) As String
    Debug.Assert in <> vbNullString
    '// ...
    '// ...
    '// ...
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JOKL4444Author Commented:
Thanks Fabrice

I have picked through your comments and can understand where you are coming from. In this case the ADO code does in fact pick out the correct connection string - I had used the IMEX= 1  flag in an earlier attempt and it failed too. The specific problem is the construction of szConnect. What is wrong  with the connection string:

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No;IMEX=1;"

When the VsourceFile variable passed to it is:

C:\Users\john\Documents\BSMA\Financials\OpenInvoices\2017-03\2017-03-29 243 Carnival UK.xls


John
0
Fabrice LambertFabrice LambertCommented:
This is weird, bc after some testing, the following 2 connection strings worked:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\.......\file.xls;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1;";
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\.......\file.xls;Extended Properties="Excel 12.0;HDR=YES;IMEX=1;";

Wich error message do you recieve ?
Wich line generate it ?
0
JOKL4444Author Commented:
The error is generated on line 46 of getcrewdata

 rsCon.Open szConnect
0
Fabrice LambertFabrice LambertCommented:
Even tho, solution was given, the original poster seems to ignore it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.