Check if VBAProject reference is loaded.

gsnidow

New member
Joined
Aug 30, 2011
Messages
38
Reaction score
0
Points
0
Location
Virginia
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Why not just use late-binding?
 

gsnidow

New member
Joined
Aug 30, 2011
Messages
38
Reaction score
0
Points
0
Location
Virginia
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Post your code Greg, and we will talk you through it.
 

gsnidow

New member
Joined
Aug 30, 2011
Messages
38
Reaction score
0
Points
0
Location
Virginia
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
 

gsnidow

New member
Joined
Aug 30, 2011
Messages
38
Reaction score
0
Points
0
Location
Virginia
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 
Top