I've written some code that requires the MS ActiveX Data Objects 2.8 Library. I've exported the book as an excel addin (.xla) and installed it on another computer. Apparently, that computer doesn't have this library and the code won't run. It gets hung up on:
Dim cnPubs As ADODB.Connection
Both computers have Microsoft DAO 3.6 Object Library. I don't know if its even the same thing. But when I un-reference the 2.8 and reference the 3.6, the code still won't work. It gets hung up on the same spot. Is there a way I can encapsule the 2.8 library with my xla so it works on any computer? or is there a way I can re-write my code to work with 3.6? Or are these 2 libraries not even related to each other? Also, I'm using Office 2007 on both computers if that matters, but I'd like my code to work with old versions also.
Below is my entire code. It just queries a SQL server.
Thx
Sub GetData()
Dim sPort As String
Dim sEffDate As Date
Dim aCols As Variant
Dim sCols As String
ActiveWorkbook.ActiveSheet.Cells.Clear
sPort = InputBox("Portfolio: ", , "INA")
dEffDate = InputBox("Date: ", , Date)
aCols = Array("Portfolio", _
"Ticker", _
"Name", _
"[Purchase Date]", _
"Shares", _
"Cost", _
"Weight", _
"Reports")
sCols = Join(aCols, ",")
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=192.168.3.5\SQLEXPRESS;INITIAL CATALOG=Models;"
'Use an integrated login.
strConn = strConn & " UID=NAME; PWD=PASS"
'Now open the connection.
cnPubs.Open strConn
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "SELECT " & _
sCols & " FROM HoldingsTBL WHERE Date='" & _
CStr(Format(dEffDate, "yyyy-mm-dd")) & "' AND Portfolio='" & _
sPort & "'"
' Copy the records into cell A1 on Sheet1.
ActiveWorkbook.ActiveSheet.Range(Cells(1, 1), Cells(1, UBound(aCols))) = aCols
ActiveWorkbook.ActiveSheet.Range("A2").CopyFromRecordset rsPubs
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
Bookmarks