Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
transfer data from excel automatic to access
Hello,
I use often a certain spreadsheet to get data from a website. To store this data, I manually copy this data the an access database. However, I know this can be done automatically (probably using VBA), but I don't have a clue how to do this. Anybody an idea?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
transfer data from excel automatic to access
Since every webpage will have different tables and data the best way to staer
ia to record a macro while you are performing the operation manually and then modifiy the the recorded macro as necessary. Record macros are not general purpose and range need to be modified. also there is a lot of erraneous instructions that can be eliminated or wimplified after the macro is recorded. for example recorded macros contain scrolling statements that are not needed when trying to automate the saving of web data to a datebase. 1) On worksheet menu - Tools - Macro - Record new macro 2) On worksheet menu - Data - Import External Data - New Web Query 3) Save Excel data to your database 4) On worksheet menu - Macro - Stop Recording You can post your recorded macro if you need additional help. " wrote: Hello, I use often a certain spreadsheet to get data from a website. To store this data, I manually copy this data the an access database. However, I know this can be done automatically (probably using VBA), but I don't have a clue how to do this. Anybody an idea?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
transfer data from excel automatic to access
Try this and replace table,field names and path to your database
Private Sub ADOFromExcelToAccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection 'Replace with the path to your database Cn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\PathToYour\Database\" _ & "Data.mdb;Persist Security Info=False" ' open a recordset Set rs = New ADODB.Recordset 'Replace "NameOfTheTableToUploadTo" with your table name rs.Open "NameOfTheTableToUploadTo", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 6 ' the starting 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("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("C" & r).Value .Fields("FieldName3") = Range("D" & r).Value .Fields("FieldName4") = Range("F" & 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 " wrote: Hello, I use often a certain spreadsheet to get data from a website. To store this data, I manually copy this data the an access database. However, I know this can be done automatically (probably using VBA), but I don't have a clue how to do this. Anybody an idea?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find a data in Sheet1,automatic transfer to Sheet2(Excel) | Excel Discussion (Misc queries) | |||
Transfer data from Excel to Access | Excel Discussion (Misc queries) | |||
Transfer data from Excel to Access | Excel Programming | |||
how do i set up a repeating transfer data from excel to access? | Excel Programming | |||
Transfer of data from excel to access | Excel Programming |