Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
best way to get data from Access to Excel?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
best way to get data from Access to Excel?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
best way to get data from Access to Excel?
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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "deko" wrote in message m... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
best way to get data from Access to Excel?
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 "deko" wrote in message m... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
best way to get data from Access to Excel?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel access data from Access?! | Excel Discussion (Misc queries) | |||
Access data -work in Excel- save in Access | Excel Programming | |||
Excel data to Access? | Excel Programming | |||
Data from Access to Excel | Excel Programming | |||
data from excel to access | Excel Programming |