Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: ADO Recordset Error Excel 2013

    Hi all,

    Using Office 365 Pro+

    My code returns this error
    Run-time error '3001':
    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another
    Debug points here
    Code:
    With adoRs
                .CursorLocation = adUseClient
    I also tried asUseServer, but I received the same error

    Do you have any other ideas or see anything else that is incorrect?

    thx
    w

    Code:
    Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
    Sub ExportToWorksheet()
        '
        'Get data from external workbook
        'Write data to tab delimited file
        'Uses Late Binding
        '
        'Start timer
            Dim t As Long
            t = GetTickCount
    
        'Declare variables
            Dim adoCnn As Object
            Dim adoRs As Object
            Dim adoCmd As Object
            Dim adoField As Object
            Dim FSO As Object
            Dim fsoFile As Object
    
            Dim sPath As String
            Dim sFile As String
            Dim sDestinationFile As String
            Dim sRecord As String
            Dim sFields As String
            Dim adoConnString As String
            Dim sSQL As String
    
            Dim i As Long
    
        'Initialize
            sPath = "C:\Data\" '<- Change as needed
            sDestinationFile = "Text" '<- Change as needed
            sFile = "SourceData.xlsx" '<- Change as needed
            sRecord = ""
            sFields = ""
    
        'ADO Source Connection - See function
            Set adoCnn = GetADOConnection
    
        'ADO connectionstring (DSN-Less)
            adoConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sPath & sFile & "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1';"
    
        'Open ADO Connection
            adoCnn.Open (adoConnString)
    
        'SQL String
            sSQL = "SELECT * FROM [Sheet1$]"
    
        'Create recordset object - See function
            Set adoRs = GetRecordset
    
        'Open recordset
            With adoRs
                .CursorLocation = adUseClient
                .CursorType = adOpenStatic
                .LockType = adLockOptimistic
                .Open sSQL, adoCnn
            End With
    
        'Create filesystem object
            Set FSO = GetFileSystemObject
    
        'Create a text file
            Set fsoFile = FSO.CreateTextFile(sPath & sDestinationFile & Format(Now(), "YYYYMMDD_HHMMSS") & ".tsv", ForWriting)
    
        'Write recrdset field headers to file
            For Each adoField In adoRs.Fields
                sFields = sFields & adoField.Name & vbTab
            Next adoField
            fsoFile.WriteLine sFields
    
        'Make sure first record
            adoRs.MoveFirst
    
        'Write records to file
            With adoRs
                Do While Not .EOF
                    For i = 0 To adoRs.Fields.Count - 1
                        sRecord = sRecord & .Fields(i) & vbTab '<- Loop each field to build the string
                    Next i
                    fsoFile.WriteLine sRecord '<- Write the string
                    sRecord = "" '<- Reset the string
                    .MoveNext
                Loop
            End With
    
        'Tidy up
            'Close objects
                adoRs.Close
                adoCnn.Close
                fsoFile.Close
        
            'Destroy objects
                Set fsoFile = Nothing
                Set FSO = Nothing
                Set adoCmd = Nothing
                Set adoRs = Nothing
                Set adoCnn = Nothing
                 
            'Timer
                MsgBox GetTickCount - t & " Milliseconds", , " Milliseconds"
    
    End Sub
    
    
    Private Function GetADOConnection() As Object
    
    On Error Resume Next
    Set GetADOConnection = CreateObject("ADODB.Connection")
    
    End Function
    
    Private Function GetRecordset() As Object
    
    On Error Resume Next
    Set GetRecordset = CreateObject("ADODB.recordset")
    
    End Function
    
    Private Function GetADOCommand() As Object
    
    On Error Resume Next
    Set GetADOCommand = CreateObject("ADODB.command")
    
    End Function
    
    Private Function GetFileSystemObject() As Object
    
    On Error Resume Next
    Set GetFileSystemObject = CreateObject("Scripting.FileSystemObject")
    
    End Function

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you comment the line out does the next line throw an error?

    Are you sure the recordset object is being created? Your function call includes "On Error Resume Next" which will not throw errors when this has a problem.

    You may wish to comment that line out also (might give you a better place to debug)!
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks George,

    I removed "On Error Resume Next" from all functions
    I then added a little test immediately after creating the recordset object
    Code:
    If Not adoRs Is Nothing Then
                Debug.Print "The object, ""adoRs"" does exist"
            End If
    Which returned,
    The object, "adoRs" does exist
    I then commented out
    Code:
    '            .CursorLocation = adUseClient
    Same error message, but now debug points to
    Code:
    .LockType = adLockOptimistic
    thx
    w

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •