You should choose the largest number Library for Microsoft ActiveX Data
Objects 2.? Library. I have 2.8. You probably do also, but
just use the highest one you've got. As to your code, I generally use this
for getting stuff into Excel from Access. It would only
require minor adjustments to pull from an Excel source:
1st, here is the general format for the code:
Sub DsnLess()
' Public Sub DsnLess
' Purpose:
' This is an example of opening a ODBC DSN-Less connection
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConn As String
' Assign the connection string to a variable
strConn = "DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=;DATABASE=pubs"
' Create the Connection object
Set Conn = New ADODB.Connection
' Assign the connection string and provider, then open the
'connection
Conn.ConnectionString = strConn
Conn.Provider = "msdasql"
Conn.Open strConn
' Create a new Recordset object and set it's Active connection
'property to the previously opened connection.
Set rst = New ADODB.Recordset
rst.ActiveConnection = Conn
rst.Open "Select * From Authors"
' Print a field value
Debug.Print rst("au_id")
' Clean up
Set rst = Nothing
Conn.Close
End Sub
NOW, here is some actual code:
Sub GetItFromAccess()
Application.ScreenUpdating = False
Sheets("Import").Activate
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
' Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
' Set the string to the path of the database
strDB = "H:\Payroll Stuff\My Payroll Database.mdb;" & "Jet
OLEDB
atabase Password=klasflkd"
' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
' Open recordset based on Access Payroll XYZ table
rst.Open "SELECT Social,[Unit Type], val(format(Units,""0.00""))AS
Units,val(format(Wages,""0.00""))AS Wages,SubAcct AS Sub " &
_
" FROM [Prod Rpt Data] ;", cnt '& _
'"GROUP BY [Acct],SubAcct,Social,[Unit Type];"
Range("Payroll1").Activate
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select
Selection.Cells.Clear
' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
ActiveSheet.Cells(3, iCol).Value = rst.Fields(iCol - 1).Name
Next
' Copy the recordset to the worksheet, starting in cell A2
ActiveSheet.Cells(4, 1).CopyFromRecordset rst
ActiveSheet.Cells(3, 1).Activate
Names.Add Name:="Payroll1", RefersTo:=ActiveCell
' Auto-fit the column widths and row heights
Selection.CurrentRegion.Columns.AutoFit
Selection.CurrentRegion.Rows.AutoFit
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.MergeCells = False
End With
Selection.Font.Bold = True
Range("Payroll1").Offset(1, 2).Activate
Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlDown)).Select
Selection.NumberFormat = "#,##0.00"
' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
' Cells(4, 3).Activate
' Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlDown)).Select
' Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Cells(3, 1).Activate
Range(ActiveCell, ActiveCell.Offset(0, 4).End(xlDown)).Select
Names.Add Name:="Payroll", RefersTo:=Selection
End Sub
HTH
--
RMC,CPA
I have recently inherited an excel macro that was working fine until we
installed office 2003. I see that in office 2003 it says not to use
XLODBC.xla, rather use the functions and methods in ADO. My question is
what library do I use for ADO I see that I have several to choose from and
then how do I convert this small bit of code to use ADO.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Worksheets("Sheet1").Select QueryString = "SELECT wiplot.wlot_lot_number,
wiplot.wlot_prod, wiplot.wlot_route FROM comets:wsopen.wiplot wiplot WHERE
wiplot.wlot_lot_number= " & "'" & lotnumber & "'"
chan =
SQLOpen("DSN=xxxxxxxx;UID=xxxxxxxx;DB=xxxxxxx;HOST=xxxxxxxxx;SERV=xxxxxxx;PRO=onsoctcp;PWD=xxxxxxx")
SQLExecQuery chan, QueryString
Set output = Worksheets("Sheet1").Range("G1")
SQLRetrieve chan, output, , , True
SQLClose chan
+++++++++++++++++++
TIA
Josh
