Results 1 to 8 of 8

Thread: Check if VBAProject reference is loaded.

  1. #1
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0

    Check if VBAProject reference is loaded.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Greetings. Is it possible to check if a specific reference is loaded in the VBA project prior to a user running a macro? If the required reference is not loaded, I would like to load it before the rest of the macro runs. Thanks.

    Greg

  2. #2
    Why not just use late-binding?

  3. #3
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    Why not just use late-binding?
    Thanks once again Bob. I had to google that one. I will say that would have made my life much easier when I was working on some macros that called solver. I would not even begin to know how to do that in my code. Would you happen to know of any good links for newbies?

    Greg

  4. #4
    Post your code Greg, and we will talk you through it.

  5. #5
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    Post your code Greg, and we will talk you through it.
    Thanks Bob. This is the same thing I posted in my other thread, but it is actually what caused me to ask the question. In this case it is the ADO library in question. Although I knew to check it, I closed the workbook without saving, and got the error message when I tried to run it. It's not a big deal for me, but if I roll it out to some other folks, I don't want to have to walk them all through it. Thanks for your good time.
    Code:
    Sub RSTest_2007_ADO()
    '   This sub will pull data from an external .xlsx file.  The ADO object
    '   library reference must be loaded.
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim strSQL As String
        Dim WS As Worksheet
        Dim RSCount As Integer
        Dim FieldCount As Integer
        Dim i, j As Integer
        Dim NoRecords As Boolean
        Dim FilePath As String
        Dim FileName As String
            
        Set WS = ActiveWorkbook.ActiveSheet
        Set cn = New ADODB.Connection
        
        FilePath = "C:\Test\"
        FileName = "Data.xlsx"
        strSQL = "SELECT * FROM [Sheet1$]"
        
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" & FilePath & FileName & ";" & _
                "Extended Properties=Excel 12.0;"
            .Open
        End With
        
        Set rs = cn.Execute(strSQL)
        
        With WS
            TopRow = .Range("A20000").End(xlUp).Row
            NoRecords = False
            With rs
                FieldCount = .Fields.Count
                If Not (.BOF And .EOF) Then
                    NoRecords = False
                    .MoveFirst
                    While Not .EOF
                        .MoveNext
                        RSCount = RSCount + 1
                    Wend
                Else
                    NoRecords = True
                    MsgBox ("No records in target")
                    Exit Sub
                End If
                .MoveFirst
                While Not .EOF
                    For i = TopRow To TopRow + RSCount - 1 Step 1
                        For j = 1 To FieldCount Step 1
                            WS.Cells(i, j) = .Fields(j - 1)
                        Next j
                        .MoveNext
                    Next i
                Wend
            End With
        End With

  6. #6
    Ah ADO! My favourite.

    There are two things to watch out for that the library addresses, objects and named constants. The objects, such as ADODB.Connection are easily handled by declaring those objects as type object, and using CreateObject to instantiate them, rather than New. Constants, of which you seem to have none, are harder, as you need to replace them with their actual value. The way I do this is to set a reference, display the constant in the immedtae windo to get its value, such as ?adForwardOnly, and create my own constants with these values. With late-binding, you lose intellisense, so it makes sense to develop using early binding, and then change to late binding for release (see my article at http://www.xldynamic.com/source/xld.EarlyLate.html).

    A couple of other points. Don't use the integer data type, Windows converts that to a long to work with, then reconverts to integer to return a result. Better to use longs in the first place. Also a line lik

    Code:
    Dim i, j As Integer
    is not declaring both as type integer, the first is variant. You have to be explicit

    Code:
    Dim i As Integer, j As Integer
    Here is your code in late binding form, and the other changes

    Code:
    Sub RSTest_2007_ADO()
    '   This sub will pull data from an external .xlsx file.  The ADO object
    '   library reference must be loaded.  <<<<<<< not any more
        Dim cn As Object 'ADODB.Connection
        Dim rs As Object 'ADODB.Recordset
        Dim strSQL As String
        Dim WS As Worksheet
        Dim RSCount As Long 'Integer
        Dim FieldCount As Long 'Integer
        Dim i As Long, j As Long 'Integer
        Dim NoRecords As Boolean
        Dim FilePath As String
        Dim FileName As String
            
        Set WS = ActiveWorkbook.ActiveSheet
        Set cn = CreateObject("ADODB.Connection")  'New ADODB.Connection
        
        FilePath = "C:\Test\"
        FileName = "Data.xlsx"
        strSQL = "SELECT * FROM [Sheet1$]"
        
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" & FilePath & FileName & ";" & _
                "Extended Properties=Excel 12.0;"
            .Open
        End With
        
        Set rs = cn.Execute(strSQL)
        
        With WS
            TopRow = .Range("A20000").End(xlUp).Row
            NoRecords = False
            With rs
                FieldCount = .Fields.Count
                If Not (.BOF And .EOF) Then
                    NoRecords = False
                    .MoveFirst
                    While Not .EOF
                        .MoveNext
                        RSCount = RSCount + 1
                    Wend
                Else
                    NoRecords = True
                    MsgBox ("No records in target")
                    Exit Sub
                End If
                .MoveFirst
                While Not .EOF
                    For i = TopRow To TopRow + RSCount - 1 Step 1
                        For j = 1 To FieldCount Step 1
                            WS.Cells(i, j) = .Fields(j - 1)
                        Next j
                        .MoveNext
                    Next i
                Wend
            End With
        End With

  7. #7
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Thank you so much for the tips, Bob. There is so much to know beyond the simple IF blocks and while loops with which most folks start to learn. I really got a good look into what is going on by starting with Wikipedia's page on late-binding. Now, as for ADO, were you being sarcastic in saying it's your favorite? Is there another method to do what I need to do? Also, I very much appreciate the time you good folks devote to considering others' problems, when your day is probably busy enough as it is. Thanks.

    Greg

  8. #8
    No sarcasm Greg, I love ADO. There is another way, DAO, but I prefer ADO (although DAO does not suffer the problem of memory leaks with open workbooks that ADO has). Notice that we are using the ACE OLEDB provider. I have recently read that MS are saying that Denali will be the last version of SQL server that supports OLEDB. If this means that MS are abandoning OLEDB, even if slowly, I wonder what the implications are for those of us using OLEDB elsehwere.

Posting Permissions

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