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

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:Database 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


"Josh" wrote in message ...
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

Thank you so much I will mess with this today. Do you know where I could
get DSN example?


"R. Choate" wrote in message
...
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:Database 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


"Josh" wrote in message
...
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

Why would you want that? The DSN is where all the extra code comes into play.

--
RMC,CPA


"Josh" wrote in message ...
Thank you so much I will mess with this today. Do you know where I could
get DSN example?


"R. Choate" wrote in message
...
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:Database 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


"Josh" wrote in message
...
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

Give this a go.

http://www.connectionstrings.com/

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Josh" wrote:
| Thank you so much I will mess with this today. Do you know where I could
| get DSN example?

Hi Dave,

I don't know if that is going to get the OP where he wants to go, but I went to the site and I want to thank you for the information
and the link. That is a great site with a ton of great syntax information in one nice, tidy spot. I will definitely keep that one
bookmarked in my programming favorites.

Thanks again!

Richard
--
RMC,CPA


"Dave Patrick" wrote in message ...
Give this a go.

http://www.connectionstrings.com/

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Josh" wrote:
| Thank you so much I will mess with this today. Do you know where I could
| get DSN example?

You're welcome.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"R. Choate" wrote:
| Hi Dave,
|
| I don't know if that is going to get the OP where he wants to go, but I
went to the site and I want to thank you for the information
| and the link. That is a great site with a ton of great syntax information
in one nice, tidy spot. I will definitely keep that one
| bookmarked in my programming favorites.
|
| Thanks again!
|
| Richard
| --
| RMC,CPA