Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel and access
Hi,
I am looking for a VBA way of connecting to aces via excel. I want to click a button and data appears depending on what input I give from and inputbox eg all data from table one that starts with name=fred. My other thread on this was closed. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel and access
Hi at
The following site has some details and code snippets of common tasks in excel/access http://www.excelkb.com/?cNode=1I7C3V&pNodes=8M3M1M Other Sites that I find usefull all the time http://www.cpearson.com/excel/topic.aspx http://www.j-walk.com/ http://www.mcgimpsey.com/index.html http://www.mvps.org/dmcritchie/excel/excel.htm http://www.rondebruin.nl/ HTH "at" wrote: Hi, I am looking for a VBA way of connecting to aces via excel. I want to click a button and data appears depending on what input I give from and inputbox eg all data from table one that starts with name=fred. My other thread on this was closed. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel and access
I found this code but I get an error when writing data to excel Dim DBFullName As String Dim TableName As String Dim TargetRange As Range DBFullName = "F:\accsess_ecel\db1.mdb" TableName = "Table1" Set TargetRange = Range("c1") Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) ' open the database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ DBFullName & ";" Set rs = New ADODB.Recordset With rs ' open the recordset .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' RS2WS rs, TargetRange ' write data from the recordset to the worksheet For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' error here End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing "steve_doc" wrote: Hi at The following site has some details and code snippets of common tasks in excel/access http://www.excelkb.com/?cNode=1I7C3V&pNodes=8M3M1M Other Sites that I find usefull all the time http://www.cpearson.com/excel/topic.aspx http://www.j-walk.com/ http://www.mcgimpsey.com/index.html http://www.mvps.org/dmcritchie/excel/excel.htm http://www.rondebruin.nl/ HTH "at" wrote: Hi, I am looking for a VBA way of connecting to aces via excel. I want to click a button and data appears depending on what input I give from and inputbox eg all data from table one that starts with name=fred. My other thread on this was closed. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel and access
i found this but get an error
Dim DBFullName As String Dim TableName As String Dim TargetRange As Range DBFullName = "F:\accsess_ecel\db1.mdb" TableName = "Table1" Set TargetRange = Range("c1") Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) ' open the database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ DBFullName & ";" Set rs = New ADODB.Recordset With rs ' open the recordset .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' RS2WS rs, TargetRange ' write data from the recordset to the worksheet For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs 'error End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing "steve_doc" wrote: Hi at The following site has some details and code snippets of common tasks in excel/access http://www.excelkb.com/?cNode=1I7C3V&pNodes=8M3M1M Other Sites that I find usefull all the time http://www.cpearson.com/excel/topic.aspx http://www.j-walk.com/ http://www.mcgimpsey.com/index.html http://www.mvps.org/dmcritchie/excel/excel.htm http://www.rondebruin.nl/ HTH "at" wrote: Hi, I am looking for a VBA way of connecting to aces via excel. I want to click a button and data appears depending on what input I give from and inputbox eg all data from table one that starts with name=fred. My other thread on this was closed. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel and access
What error message or number, and if you press the [Debug] button, what line
of the code is hightlighted? Is it the line you have a 'error comment at the end of? "at" wrote: i found this but get an error Dim DBFullName As String Dim TableName As String Dim TargetRange As Range DBFullName = "F:\accsess_ecel\db1.mdb" TableName = "Table1" Set TargetRange = Range("c1") Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) ' open the database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ DBFullName & ";" Set rs = New ADODB.Recordset With rs ' open the recordset .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' RS2WS rs, TargetRange ' write data from the recordset to the worksheet For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs 'error End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing "steve_doc" wrote: Hi at The following site has some details and code snippets of common tasks in excel/access http://www.excelkb.com/?cNode=1I7C3V&pNodes=8M3M1M Other Sites that I find usefull all the time http://www.cpearson.com/excel/topic.aspx http://www.j-walk.com/ http://www.mcgimpsey.com/index.html http://www.mvps.org/dmcritchie/excel/excel.htm http://www.rondebruin.nl/ HTH "at" wrote: Hi, I am looking for a VBA way of connecting to aces via excel. I want to click a button and data appears depending on what input I give from and inputbox eg all data from table one that starts with name=fred. My other thread on this was closed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel to Access | Excel Discussion (Misc queries) | |||
Excel to Access (or something) | Excel Discussion (Misc queries) | |||
Excel/Access | Excel Discussion (Misc queries) | |||
Access Form In An Access Report (SubForm) Question | Links and Linking in Excel | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) |