Hi Group. I am using the Office 2000 package.

Some details.
I have created a query in Access and saved it. The query
collects columns of data from a few employee databases.

I need this data entered into a "control" sheet on a
template I have created.

Does anyone know if there is code that can be run from
excel that will go out to a local drive and run an Access
query, then take this data and save it to a specific range
in my control sheet?

Thanks,

Kelly

See Mr Erlandsen's site for examples using ADO:

http://www.erlandsendata.no/english/vba/adodao/


http://support.microsoft.com/?kbid=295646
HOWTO: Transfer Data from ADO Data Source to Excel with ADO


http://support.microsoft.com/?kbid=246335
HOWTO: Transfer Data from an ADO Recordset to Excel with Automation




--
Regards,
Tom Ogilvy

"Kelly" wrote in message
...
Hi Group. I am using the Office 2000 package.

Some details.
I have created a query in Access and saved it. The query
collects columns of data from a few employee databases.

I need this data entered into a "control" sheet on a
template I have created.

Does anyone know if there is code that can be run from
excel that will go out to a local drive and run an Access
query, then take this data and save it to a specific range
in my control sheet?

Thanks,

Kelly

-----Original Message-----
See Mr Erlandsen's site for examples using ADO:

http://www.erlandsendata.no/english/vba/adodao/


http://support.microsoft.com/?kbid=295646
HOWTO: Transfer Data from ADO Data Source to Excel with
ADO


http://support.microsoft.com/?kbid=246335
HOWTO: Transfer Data from an ADO Recordset to Excel with
Automation




--
Regards,
Tom Ogilvy

"Kelly" wrote in
message
...
Hi Group. I am using the Office 2000 package.

Some details.
I have created a query in Access and saved it. The query
collects columns of data from a few employee databases.

I need this data entered into a "control" sheet on a
template I have created.

Does anyone know if there is code that can be run from
excel that will go out to a local drive and run an
Access
query, then take this data and save it to a specific
range
in my control sheet?

Thanks,

Kelly



.
Thankyou Thankyou Thankyou. That is way cool!

If you don't mind I have another question..

I used the code titled HOWTO:Transfer Data from ADO
Recordset to Excel with Automation.

The way this code works is it opens a new workbook and
transfers the Access data to sheet 1.

Would you be able to help me modify the code a little so
that the data is transferred to a specific workbook
already created to a specific worksheet already created
and named inside that workbook.

I will include the code....

Private Sub Command1_Click()
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 your Northwind database
strDB = "c:\program files\Microsoft office\office11
\samples\Northwind.mdb"

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

' Open recordset based on Orders table
rst.Open "Select * From Orders", cnt

' Create an instance of Excel and add a workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")

' Display Excel and give user control of Excel's
lifetime
xlApp.Visible = True
xlApp.UserControl = True

' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol -
1).Name
Next

' Check version of Excel
If Val(Mid(xlApp.Version, 1, InStr(1,
xlApp.Version, ".") - 1)) 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset

' Copy the recordset to the worksheet, starting in
cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets

Else
'EXCEL 97 or earlier: Use GetRows then copy array
to Excel

' Copy recordset to an array
recArray = rst.GetRows
'Note: GetRows returns a 0-based array where the
first
'dimension contains fields and the second dimension
'contains records. We will transpose this array so
that
'the first dimension contains records, allowing the
'data to appears properly when copied to Excel

' Determine number of records

recCount = UBound(recArray, 2) + 1 '+ 1 since 0-
based array


' Check the array for contents that are not valid
when
' copying the array to an Excel worksheet
For iCol = 0 To fldCount - 1
For iRow = 0 To recCount - 1
' Take care of Date fields
If IsDate(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = Format(recArray
(iCol, iRow))
' Take care of OLE object fields or array
fields
ElseIf IsArray(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = "Array Field"
End If
Next iRow 'next record
Next iCol 'next field

' Transpose and Copy the array to the worksheet,
' starting in cell A2
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value
= _
TransposeDim(recArray)
End If

' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit

' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

' Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing

Set xlApp = Nothing

End Sub


Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)

Dim X As Long, Y As Long, Xupper As Long, Yupper As
Long
Dim tempArray As Variant

Xupper = UBound(v, 2)
Yupper = UBound(v, 1)

ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X

TransposeDim = tempArray

End Function

Set xlWb = xlApp.Workbooks.Open("C:\My Documents\MyFiles.xls")

in the next line change Sheet1 to the name of your specific sheet.

--
Regards,
Tom Ogilvy

Kelly wrote in message
...

-----Original Message-----
See Mr Erlandsen's site for examples using ADO:

http://www.erlandsendata.no/english/vba/adodao/


http://support.microsoft.com/?kbid=295646
HOWTO: Transfer Data from ADO Data Source to Excel with
ADO


http://support.microsoft.com/?kbid=246335
HOWTO: Transfer Data from an ADO Recordset to Excel with
Automation




--
Regards,
Tom Ogilvy

"Kelly" wrote in
message
...
Hi Group. I am using the Office 2000 package.

Some details.
I have created a query in Access and saved it. The query
collects columns of data from a few employee databases.

I need this data entered into a "control" sheet on a
template I have created.

Does anyone know if there is code that can be run from
excel that will go out to a local drive and run an
Access
query, then take this data and save it to a specific
range
in my control sheet?

Thanks,

Kelly



.
Thankyou Thankyou Thankyou. That is way cool!

If you don't mind I have another question..

I used the code titled HOWTO:Transfer Data from ADO
Recordset to Excel with Automation.

The way this code works is it opens a new workbook and
transfers the Access data to sheet 1.

Would you be able to help me modify the code a little so
that the data is transferred to a specific workbook
already created to a specific worksheet already created
and named inside that workbook.

I will include the code....

Private Sub Command1_Click()
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 your Northwind database
strDB = "c:\program files\Microsoft office\office11
\samples\Northwind.mdb"

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

' Open recordset based on Orders table
rst.Open "Select * From Orders", cnt

' Create an instance of Excel and add a workbook
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")

' Display Excel and give user control of Excel's
lifetime
xlApp.Visible = True
xlApp.UserControl = True

' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol -
1).Name
Next

' Check version of Excel
If Val(Mid(xlApp.Version, 1, InStr(1,
xlApp.Version, ".") - 1)) 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset

' Copy the recordset to the worksheet, starting in
cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets

Else
'EXCEL 97 or earlier: Use GetRows then copy array
to Excel

' Copy recordset to an array
recArray = rst.GetRows
'Note: GetRows returns a 0-based array where the
first
'dimension contains fields and the second dimension
'contains records. We will transpose this array so
that
'the first dimension contains records, allowing the
'data to appears properly when copied to Excel

' Determine number of records

recCount = UBound(recArray, 2) + 1 '+ 1 since 0-
based array


' Check the array for contents that are not valid
when
' copying the array to an Excel worksheet
For iCol = 0 To fldCount - 1
For iRow = 0 To recCount - 1
' Take care of Date fields
If IsDate(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = Format(recArray
(iCol, iRow))
' Take care of OLE object fields or array
fields
ElseIf IsArray(recArray(iCol, iRow)) Then
recArray(iCol, iRow) = "Array Field"
End If
Next iRow 'next record
Next iCol 'next field

' Transpose and Copy the array to the worksheet,
' starting in cell A2
xlWs.Cells(2, 1).Resize(recCount, fldCount).Value
= _
TransposeDim(recArray)
End If

' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit

' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

' Release Excel references
Set xlWs = Nothing
Set xlWb = Nothing

Set xlApp = Nothing

End Sub


Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)

Dim X As Long, Y As Long, Xupper As Long, Yupper As
Long
Dim tempArray As Variant

Xupper = UBound(v, 2)
Yupper = UBound(v, 1)

ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next Y
Next X

TransposeDim = tempArray

End Function