Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Accessing Excel spreadsheet from Word using ADO

    I have a template that reads several worksheets in an Excel spreadsheet. I have set up a connection successfully before using just one worksheet in a spreadsheet. The code I used set up a connection to the database/spreadsheet, and then opened a recordset using ADO.

    I want to set up one connection, and then open a number of recordsets (one by one; so open one, use it, open the next one, use it, etc.) I want to open the connection in one procedure, check to see if a particular worksheet is present. If it is, I want to go to a different procedure to opens a recordsheet. Then I'll check to see if the next worksheet is present, etc.

    My first challenge is that I haven't worked out how to check if a worksheet is there. (I've tried different options and none worked so I deleted the code, hence the reason you won't find the code below.)

    My next challenge is that when I run the code, I get the a run-time error message "Operation is not allowed when the object is closed" (see attachment) on the line with the ***. My code has developed through trial and error so it's a bit clumsy as I've moved things around to get rid of error messages. (It worked until I got this one and then I figured I'd best ask for help before I got myself into too much of a muddle.)


    Code:
    Private Sub Customer_Initialize()
    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim sPath As String, strSQL As String, clTrgt As Range
    sPath = "path\" & SaveCustomerCode & " Equipment Schedule.xlsx"
      
     sConn = "Data Source =" & sPath & "; Extended Properties =""Excel 12.0 Xml;HDR=YES"";"
     
    'open the connection'
     With cnt
         .Provider = "Microsoft.ACE.OLEDB.12.0"
         .ConnectionString = sConn
         .Open
     End With
     
    ' this is where I want to add a check to see if there is a "filter" worksheet in the spreadsheet.  I'll only call the AddFilterChecklist if there is a worksheet.  There will be several checks for different worksheets. 
    
    AddFilterChecklist
    
    EarlyExit:
      'Close and release the ADO objects
      cnt.Close
      Set cnt = Nothing
                  
    End Sub
    
    Private Sub AddFilterChecklist()
        Dim rst As New ADODB.Recordset
        Dim sPath As String, strSQL As String, clTrgt As Range
        Dim myWB As Object
        Dim FirstRecord As Boolean
        
    rst.ActiveConnection = cnt.Connection ***
        sPath = "path\” & SaveCustomerCode & " Equipment Schedule.xlsx"
        strSQL = "Select * from [Filters$]"
        rst.Open strSQL, cnt, adOpenStatic
    
    etc., and then at the end of the sub:
    
    EarlyExit:
      'Close and release the ADO objects
      rst.Close
      Set rst = Nothing
    
    End sub
    Attached Images Attached Images
    Last edited by macropod; 2012-09-12 at 05:26. Reason: Added code tags

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Just from a quick glance, I don't think the AddFilterChecklist routine can "see" the cnt variable so it doesn't have a value in that routine!

    Try changing the definition of AddFilterChecklist to include "cnt as Connection" as a parameter passed into it and then call it like this:
    AddFilterChecklist cnt

    Does this help?
    Last edited by macropod; 2012-09-12 at 05:25. Reason: Deleted unnecessary quote of entire previous post

  3. #3
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Jeremy. Thanks for your help. I tried this and got a run-time error "424 Object Required". When I googled this it scared the pants off me as it required editing the registry. (So I've taken this out.)

    I had (at one stage) defined all the Dims as Public at the start of the code. When I did that I got this run-time error: "3001 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" I've googled this and it seems to be what you're saying - the routine can't 'see' the variable. I sort of understand what's gone wrong but I don't understand the fix suggested in "http://support.microsoft.com/kb/248287." (I don't think the people who write these articles can write plain english!)

    Any further ideas would be welcomed. In the meantime, I'll try to work out the suggested fix, and see if I can find out more.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by Niki B View Post
    Hi Jeremy. Thanks for your help. I tried this and got a run-time error "424 Object Required". When I googled this it scared the pants off me as it required editing the registry. (So I've taken this out.)

    I had (at one stage) defined all the Dims as Public at the start of the code. When I did that I got this run-time error: "3001 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" I've googled this and it seems to be what you're saying - the routine can't 'see' the variable. I sort of understand what's gone wrong but I don't understand the fix suggested in "http://support.microsoft.com/kb/248287." (I don't think the people who write these articles can write plain english!)

    Any further ideas would be welcomed. In the meantime, I'll try to work out the suggested fix, and see if I can find out more.
    Hi Niki, you'll be reassured to know that I read that support article too, and it made very little sense to me! The good news is I think it's referring to communication between different programs, not to subroutines within the same Word (or Excel) project!

    Below is some code I've patched together starting from your first post but changing the path, filename and sheet names to suit my test environment. I've also renamed / removed a couple of variables and separated some declaration lines to make it more readable. You should also add Option Explicit at the top of your code to catch any undeclared variables!

    This code works OK for me with Word 2007 interrogating Excel 2007. After creating an ADO connection, the main routine first creates a recordset to talk to the Filter1 sheet, and then calls a subroutine (passing it the connection object) to create a different recordset to talk to the Filter2 sheet.

    See if you can make sense of what I've written and if you can get it to work for your path / file / sheetname combinations.

    Code:
    Option Explicit
    Sub TestGetExcelData()
        ' Try to use ADO to interrogate an Excel 2007 file. File location and sheet names changed for me!
        ' J Barker September 2012.
        Dim cnt As New ADODB.Connection
        Dim rst As New ADODB.Recordset  ' this will be used in this subroutine
        Dim sPath As String
        Dim strSQL As String
        Dim strConn As String
        
        On Error GoTo TGEDError
        sPath = "c:\data\" & "people.xlsx"  ' My file has 3 worksheets: Filter1, Filter2, Filter3.
        strConn = "Data Source =" & sPath & "; Extended Properties =""Excel 12.0 Xml;HDR=YES"";"
        
        'open the connection
        With cnt
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = strConn
            .Open
        End With
        MsgBox "After connection opened", vbInformation, "Excel Connection Opened"
        
        ' Now try to open a recordset against the connection...
        strSQL = "Select * from [Filter1$]"     ' N.B. I have sheets called Filter1, Filter2 etc for testing!
        rst.Open strSQL, cnt, adOpenStatic
        MsgBox "After recordset opened on Filter1", vbInformation, "Recordset opened"
        MsgBox "Records on Filter1 sheet: " & rst.RecordCount, vbInformation, "Record count"
        rst.Close
        
        ' if the above code works OK for a recordset defined in this subroutine, what about creating a
        ' recordset in another subroutine by passing in the connection object?
        CalledRecordsetTest cnt
        MsgBox "After called routine returns", vbInformation, "Test calling a routine"
    TGEDExit:
        On Error Resume Next
        Set rst = Nothing
        cnt.Close
        Set cnt = Nothing
        Exit Sub
    TGEDError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Error in Connection"
        Resume TGEDExit
    End Sub
    
    Sub CalledRecordsetTest(cnn As ADODB.Connection)
        ' this routine tests that we can take an ADODB connection passed in
        ' and create and use an ADODB recordset based on that connection.
        ' This code interrogates the Filter2 sheet (just to make sure we really are reading a different sheet)
        Dim rstLocal As New ADODB.Recordset ' this recordset is only known about in this subroutine
        Dim strSQL As String
        
        On Error GoTo CRTError
        strSQL = "Select * from [Filter2$]"     ' N.B. I have sheets called Filter1, Filter2 etc for testing!
        rstLocal.Open strSQL, cnn, adOpenStatic
        MsgBox "After recordset opened on Filter2", vbInformation, "Recordset opened"
        MsgBox "Records on Filter2 sheet: " & rstLocal.RecordCount, vbInformation, "Record count"
        rstLocal.Close
    CRTExit:
        On Error Resume Next
        Set rstLocal = Nothing
        Exit Sub
    CRTError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Error in called routine"
        Resume CRTExit
    End Sub
    Let me know how you get on (I think we are about 12 hours apart!!)

    Jeremy

  5. #5
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts
    YES!!!! It worked! And it all made sense (and I feel stupid for not getting it right, but there you go).

    One question: Because we've used error routines, the challenge of checking for each worksheet has been resolved, but how do you 'analyse' problems? For example, I had a small coding error but it took me ages to work out what was happening because I wasn't getting a valid error message (and the line of code wasn't being highlighted.) Eventually I commented out the "On error" statement. Is this what you'd usually do while you were testing?

    Thank you so much for your help - it's awesome to think that someone on the other side of the world helped out.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by Niki B View Post
    YES!!!! It worked! And it all made sense (and I feel stupid for not getting it right, but there you go).

    One question: Because we've used error routines, the challenge of checking for each worksheet has been resolved, but how do you 'analyse' problems? For example, I had a small coding error but it took me ages to work out what was happening because I wasn't getting a valid error message (and the line of code wasn't being highlighted.) Eventually I commented out the "On error" statement. Is this what you'd usually do while you were testing?

    Thank you so much for your help - it's awesome to think that someone on the other side of the world helped out.
    Glad to be of service

    Yes, the error handling can be added in last, or written first and temporarily disabled whilst debugging. When I'm posting for others I usually try to offer something slightly polished (although I don't always stick to my good intentions!).

    Note that ADO can be a bit fiddly because it has its own Errors collection which you may or may not need to use depending on the complexity of the problem.

    Incidentally, to answer the (implied) question in your first post, it may be possible to determine the worksheets in the source file via ADO - I'll see if I can dig out some code to do this.

    Regards

    Jeremy

  7. #7
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Jeremy. I have been really careful about putting excellent comments (well, I think they're excellent at the time of writing!) into my code to help others make sense of it all. If you get a chance to look up the query about worksheets that would be awesome.

  8. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by Niki B View Post
    Thanks Jeremy. I have been really careful about putting excellent comments (well, I think they're excellent at the time of writing!) into my code to help others make sense of it all. If you get a chance to look up the query about worksheets that would be awesome.
    Hi Niki, I think I have 2 ways of bringing back the sheet names from Excel (actually both ways return both sheet names AND any named ranges, but the sheet names come back with a $ on the end so you can test for this).


    First method
    ========
    If rst is declared as a New ADODB recordset, then use code like this:

    Code:
        Set rst = cnt.OpenSchema(adSchemaTables)
        Do Until rst.EOF
            MsgBox "Schema row: " & rst![TABLE_NAME].Value & " of type " & rst![TABLE_TYPE].Value, vbInformation, "Schema row"
            rst.MoveNext
        Loop
        rst.Close
    Look up the OpenSchema method and adSchemaTables parameter for more details. OpenSchema is potentially quite powerful, but for an Excel source only brings back a few useful details like table name and type (in fact there only seems to be one type regardless of whether it is a worksheet or a named range).

    Second method
    ==========
    Add a reference to Microsoft ADO Ext 2.8 for DDL and Security to your project (may be a different version number for you!)
    declare cat as ADOX.Catalog, and then use code like this:

    Code:
        Set cat = New ADOX.Catalog
        cat.ActiveConnection = cnt
        For Each tbl In cat.Tables
            MsgBox "Table: " & tbl.Name & " of type " & tbl.Type, vbInformation, "Table from ADOX Catalog"
        Next tbl
    As far as I can see, both methods give me back the same results.


    Let me know how you get on.

    Jeremy

  9. #9
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    223
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Also I forgot to add in the second method you need to declare tbl as ADOX.Table!

    (I tried to Edit the post, but editing was playing up!)

    The cnt variable in both cases is the ADODB Connection object you have already set up.

    Jeremy

  10. #10
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    55
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks for your help Jeremy. I got the first option to work (i.e. it supplied the names), but I haven't added it to my code yet. The error routine is handling the situation if a worksheet can't be found (it's not an error if the worksheet is missing -just a case of missing that worksheet and moving on to the next one), but having said that, there is one worksheet that must be present so I want to add a check to ensure that one is found. Thanks again for the help.

Posting Permissions

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