How do I use late binding to avoid references libraries?

Sort by date Sort by votes
Code: 
Option Explicit
 
Private Sub Workbook_Open()
 
'==================================================
'Grabs material list from master materials index using ActiveX Data Objects
 
'Var        Purpose
 
'a1         Connection object
'a2         Recordset object
'b1         SQL connection string
'b2         SQL recordset "Source" parameter
'b3         Source workbook path
'b4         Source workbook name
'==================================================
 
On Error GoTo Catch
 
Dim a1      As Connection
Dim a2      As Recordset
Dim b1, _
    b2, _
    b3, _
    b4      As String
 
'==================================================
'Media
'==================================================
 
'Assigns inventory path and filename to "b3" and "b4"
 
b3 = "internal link"
 
b4 = "\Inventory Updater.xls"
 
 
'Assigns connection string to "b1"
 
b1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & b3 & b4 & ";" & _
     "Extended Properties=""Excel 8.0;HDR=No"";"
 
 
'Assigns SQL select statement to "b2"
 
b2 = "SELECT * FROM [" & "Media$A:B" & "];"
 
 
'Sets connection and recordset object variables
 
Set a1 = CreateObject("ADODB.Connection")
Set a2 = CreateObject("ADODB.Recordset")
 
 
'Opens the connection and the recordset
 
a1.Open b1
a2.Open b2, b1
 
 
'Copies from the recordset
 
With ThisWorkbook.Sheets("Media")
 
    .Cells.ClearContents
    .Range("A1").CopyFromRecordset a2
 
End With
 
 
'Closes connection and recordset so that they can be reopened with a tweaked SQL statement
 
a1.Close
a2.Close
 
'==================================================
'Leads and employees
'==================================================
 
'Assigns SQL select statement to "b2"
 
b2 = "SELECT * FROM [" & "Names$A:D" & "];"
 
 
'Opens the connection and the recordset
 
a1.Open b1
a2.Open b2, b1
 
 
'Copies from the recordset
 
With ThisWorkbook.Sheets("Names")
 
    .Cells.ClearContents
    .Range("A1").CopyFromRecordset a2
 
End With
 
 
'Closes the connection and the recordset
 
a1.Close
a2.Close
 
 
'==================================================
'Error Handler
'==================================================
 
Catch:
 
On Error Resume Next
 
 
'Ensures connection and recordset are closed
 
a1.Close
a2.Close
 
 
'Cleanup
 
Set a1 = Nothing
Set a2 = Nothing
 
 
'If the event errors out, then a caption appears which reads "Lists outdated"
 
With ThisWorkbook.Sheets("Media Electronic")
 
    Select Case Err.Number
 
        Case Not 0, Not 3704
 
            .Range("G43") = "Lists outdated"
 
        Case Else
 
            .Range("G43") = "Lists current"
 
    End Select
 
End With
 
End Sub
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Glory said:
Code: 
Option Explicit
 
Private Sub Workbook_Open()
 
'==================================================
'Grabs material list from master materials index using ActiveX Data Objects
 
'Var        Purpose
 
'a1         Connection object
'a2         Recordset object
'b1         SQL connection string
'b2         SQL recordset "Source" parameter
'b3         Source workbook path
'b4         Source workbook name
'==================================================
 
On Error GoTo Catch
 
Dim a1      As Connection
Dim a2      As Recordset
Dim b1, _
    b2, _
    b3, _
    b4      As String
 
'==================================================
'Media
'==================================================
 
'Assigns inventory path and filename to "b3" and "b4"
 
b3 = "internal link"
 
b4 = "\Inventory Updater.xls"
 
 
'Assigns connection string to "b1"
 
b1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & b3 & b4 & ";" & _
     "Extended Properties=""Excel 8.0;HDR=No"";"
 
 
'Assigns SQL select statement to "b2"
 
b2 = "SELECT * FROM [" & "Media$A:B" & "];"
 
 
'Sets connection and recordset object variables
 
Set a1 = CreateObject("ADODB.Connection")
Set a2 = CreateObject("ADODB.Recordset")
 
 
'Opens the connection and the recordset
 
a1.Open b1
a2.Open b2, b1
 
 
'Copies from the recordset
 
With ThisWorkbook.Sheets("Media")
 
    .Cells.ClearContents
    .Range("A1").CopyFromRecordset a2
 
End With
 
 
'Closes connection and recordset so that they can be reopened with a tweaked SQL statement
 
a1.Close
a2.Close
 
'==================================================
'Leads and employees
'==================================================
 
'Assigns SQL select statement to "b2"
 
b2 = "SELECT * FROM [" & "Names$A:D" & "];"
 
 
'Opens the connection and the recordset
 
a1.Open b1
a2.Open b2, b1
 
 
'Copies from the recordset
 
With ThisWorkbook.Sheets("Names")
 
    .Cells.ClearContents
    .Range("A1").CopyFromRecordset a2
 
End With
 
 
'Closes the connection and the recordset
 
a1.Close
a2.Close
 
 
'==================================================
'Error Handler
'==================================================
 
Catch:
 
On Error Resume Next
 
 
'Ensures connection and recordset are closed
 
a1.Close
a2.Close
 
 
'Cleanup
 
Set a1 = Nothing
Set a2 = Nothing
 
 
'If the event errors out, then a caption appears which reads "Lists outdated"
 
With ThisWorkbook.Sheets("Media Electronic")
 
    Select Case Err.Number
 
        Case Not 0, Not 3704
 
            .Range("G43") = "Lists outdated"
 
        Case Else
 
            .Range("G43") = "Lists current"
 
    End Select
 
End With
 
End Sub
Click to expand...


Try this :

Code: 
Dim a1      As Object 'Connection
Dim a2      As Object 'Recordset
 
Upvote 0
Jaafar Tribak said:
Late binding is done by using the CreateObject or GetObject Methods to get a reference to a COM object as opposed to setting a reference to the COM Class and using the New Keyword . Late binding is slower and you loose the Intelsense functionality but it is more flexible... and of course, you need to declare all the variables as generic Object
Click to expand...

Actually, late binding is just the declaring of the objects as Object. You can still use CreateObject and GetObject with early binding.
 
Upvote 0
rorya said:
Actually, late binding is just the declaring of the objects as Object. You can still use CreateObject and GetObject with early binding.
Click to expand...

Yes that's thecnically true but I guess one wouldn't use the above Functions if a reference to the COM server is already set.

Thanks.
 
Upvote 0
It depends. I seem to recall that Colin (the Beard) mentioned that MS actually recommends using CreateObject rather than New for automation.
 
Upvote 0
... MS actually recommends using CreateObject rather than New for automation.
Click to expand...
Something about better interface marshalling?
 
Upvote 0
Glory

This is totally off-topic and quite unimportant I suppose, but are those realy the variable names you use?

Don't you get confused?

Is a1 a recordset, no wait it's a connection...
 
Upvote 0
Norie: The connection object has a very dominant personality, so it always has to be indexed first.

If it ever got confusing I would split the screen until the meanings sank in.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>


<o:p>Jaafar: That's exactly the solution I settled on last night; in fact, I thought I posted the code that included the modification. Thanks.</o:p>
<o:p></o:p>
<o:p></o:p>
<o:p>
Code: 
[COLOR=black][FONT=Verdana]<o:p>Dim a5    As Object 'Connection[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]Dim a6    As Object 'Recordset; generic class prevents compile error if PC lacks ADOX references[/COLOR][/FONT]
[FONT=Verdana][COLOR=black][/COLOR][/FONT] 
[FONT=Verdana][COLOR=black]Set a5 = CreateObject("ADODB.Connection")
Set a6 = CreateObject("ADODB.Recordset")</o:p>[/COLOR][/FONT]
</o:p>
<o:p></o:p><o:p></o:p>
<o:p>shg: Both articles are highly informative. My question is answered in the second.</o:p>
<o:p></o:p>
<o:p></o:p>
<o:p>
<o:p>If your declaring your objects by type name, you are binding early:

Dim cnADO as ADODB.Recordset

If you are declaring your objects as generic Object(s), then you are binding late:

Dim cnADO as Object
Set cnADO = CreateObject("ADODB.Recordset")</o:p>
Click to expand...

</o:p><o:p>
</o:p>
<o:p>
</o:p><o:p>But the first appears to contradict the second.</o:p>
<o:p></o:p>
<o:p></o:p>
<o:p><o:p>2nd: "The CreateObject() function has all of the functio:ality of the New keyword."</o:p></o:p>
<o:p><o:p></o:p>
</o:p><o:p></o:p>
<o:p>1st: "The CreateObject function cannot be used on classes whose Instancing property is Private or PublicNotCreatable. The New operator can be used on any class."</o:p>
 
Upvote 0
Would that be before or after the code errors because you've used the wrong variable somewhere?

Also, what if someone else has to use and understand the code and you aren't available?

Wouldn't it be far easier to give the variables names that give at least a hint of what they are?

PS You haven't actually typed some of your variables, b1-b3 will be Variant.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Assign array value from late binded object (VBA)
Replies
2
Views
795
gifariz
G
N
  • Solved
How do I: Reference a sheets 'codename' and increment? - VBA
Replies
4
Views
3K
NRGZ
N
R
  • Solved
Late Binding: extract Access to Excel with formatting
2 3
Replies
22
Views
5K
roelandwatteeuw
R
S
  • Question Question
Late-binding and Adding references
Replies
3
Views
328
Kenneth Hobson
Kenneth Hobson
B
  • Question Question
Excel to Outlook VBA Library References
Replies
1
Views
1K
RickXL
RickXL

Forum statistics

Threads
1,224,602
Messages
6,179,843
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top