Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
Hi
I am working on a webpage about this and hope to finish it next week You can look here for information http://www.erlandsendata.no/english/...php?t=envbadac -- Regards Ron de Bruin http://www.rondebruin.nl "nuti" wrote in message ups.com... Hello group, I want to know the Code which exports data to an Access table from an Excel worksheet and the code which imports data from an Access table to a worksheet. i want to run it as a macro . Please help me with this problem. cheers Nuti |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
Hi Ron,
I have visited the link. But it is throwing error.-----runtime error 3265 please help me out. cheers, nuti |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
Have you set the reference in the VBA editor and are the File, Table and field names correct in the code
-- Regards Ron de Bruin http://www.rondebruin.nl "nuti" wrote in message oups.com... Hi Ron, I have visited the link. But it is throwing error.-----runtime error 3265 please help me out. cheers, nuti |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
what shuold be the reference?
correct me if iam wrong--- ---does the code take the excel sheet which is open/from the excel from which the macro is run? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
what shuold be the reference?
From the website : The macro examples assumes that your VBA project has added a reference to the ADO object library. You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft ActiveX Data Objects x.x Object Library. Use ADO if you can choose between ADO and DAO for data import or export. correct me if iam wrong--- ---does the code take the excel sheet which is open/from the excel from which the macro is run? It use the active sheet but you can include the sheet name if you want Range("C1") ' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _ "TableName", Range("C1") -- Regards Ron de Bruin http://www.rondebruin.nl "nuti" wrote in message ups.com... what shuold be the reference? correct me if iam wrong--- ---does the code take the excel sheet which is open/from the excel from which the macro is run? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
Hi Ron,
Actually i hav added the reference in the very begining. the excel sheet has 7 columns....ServerName,PrinterName....... when im running the debugger it is pointing to ---.Fields("ServerName") = Range("A" & r).Value i had created a database by the name testaccess and tablename as table1. ******************************* Sub Macro2() ' ' Macro2 Macro ' Macro recorded 1/4/2006 by Infosys ' ' ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=E:\testaccess\testaccess.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 2 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("ServerName") = Range("A" & r).Value .Fields("PrinterName") = Range("B" & r).Value .Fields("ShareName") = Range("C" & r).Value .Fields("PortName") = Range("D" & r).Value .Fields("DriverName") = Range("E" & r).Value .Fields("Comment") = Range("E" & r).Value .Fields("Location") = Range("E" & r).Value .Fields("Sepfile") = Range("E" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
Ron,
read the range of the last three fields in sequence....shuold b F,G,H.......... |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
Try to test it this evening
-- Regards Ron de Bruin http://www.rondebruin.nl "nuti" wrote in message ups.com... Ron, read the range of the last three fields in sequence....shuold b F,G,H.......... |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
Hi Ron,
I have tried to run this macro (from the active excel sheet). but it is throwing the same error. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
data import and export in to access
hi Nuti
Send me a private e-mail and i send you a very basic example -- Regards Ron de Bruin http://www.rondebruin.nl "nuti" wrote in message ups.com... Hi Ron, I have tried to run this macro (from the active excel sheet). but it is throwing the same error. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003, Convert EXISTING Worksheet Data to XML? | Excel Discussion (Misc queries) | |||
Export address list data in an Excel worksheet for importing into. | Excel Discussion (Misc queries) | |||
How to export excel column into Access without losing the leading. | Excel Discussion (Misc queries) | |||
How do I import MS Project data into Excel? | Excel Discussion (Misc queries) | |||
Import Excel Data from another workbook or file | Excel Discussion (Misc queries) |