best way to get data from Access to Excel?

[deko]

Is there a simple way to get data into Excel from Access? I've tried
TransferSpreadsheet and OutputTo - neither meet my needs. I've alse tried
using a select statement - does nto work on my system - something about an
ISAM driver... Can I loop through a recordset or something? I have one
table with one column - I just want to import the one column into Excel - no
formatting, no headerow.

Thanks in advance.

 

[deko]

Is there a simple way to get data into Excel from Access? I've tried

TransferSpreadsheet and OutputTo - neither meet my needs. I've alse tried
using a select statement - does nto work on my system - something about an
ISAM driver... Can I loop through a recordset or something? I have one
table with one column - I just want to import the one column into Excel - no
formatting, no headerow.

I think I discovered why TransferSpreadsheet was failing -

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblName",
strTarget

The path (strTarget) is more than 64 characters long. Apparently this barfs
it.

 

[Ken Wright]

What's wrong with going into Excel, and using Data / Get External data.

You can also just copy and paste the column from one to the other and vice
versa.

 

[Jim May]

From within Access, get to the Table or query result table youn want to
"send to Excel"
and at the menu select Tools, Office Links, Analyze with MS Excel..
Done. Save data within the receiving excel file.
HTH

 

[Guest]

Hi deko

Try the following sample to get a feel for an ADO connection.


Private Sub CommandButton4_Click()

On Error GoTo ErrHandler

Dim rg As Range
Set rg = ThisWorkbook.Worksheets(2).Range("a1")

'To use ADO objects in an application add a reference
'to the ADO component. From the VBA window select
'>Tools/References< check the box
' "Microsoft ActiveX Data Objects 2.x Library"

'You should fully quality the path to your file

Dim db_Name As String
db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb")
Dim DB_CONNECT_STRING As String

DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data Source=" & db_Name & ";" & ", , , adConnectAsync;"

'Create the connection
Dim cnn As New ADODB.Connection
Set cnn = New Connection
cnn.Open DB_CONNECT_STRING

'Create the recordset
Dim rs As ADODB.Recordset
Set rs = New Recordset

'Determines what records to show
Dim strSQL As String
strSQL = "SELECT CompanyName, ContactName, City, Country " & _
"FROM Customers ORDER BY CompanyName"

'Retreive the records
rs.CursorLocation = adUseClient
rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic

'Test to see if we are connected and have records
Dim num As Integer
num = rs.RecordCount

Dim num1 As Integer
num1 = rs.Fields.Count

If cnn.State = adStateOpen Then
MsgBox "Welcome to! " & db_Name & " Records = " & num & " Fields =
" & num1, vbInformation, _
"Good Luck TK"
Else
MsgBox "Sorry. No Data today."
End If

'Copy recordset to the range
rs.MoveLast
rs.MoveFirst
rg.CopyFromRecordset rs
rg.CurrentRegion.Columns.AutoFit

'close connection
cnn.Close
Set cnn = Nothing
Set rs = Nothing

Exit Sub

ErrHandler:
MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly
End Sub