transfer data from excel automatic to access

[intxausti]

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??

 

[Guest]

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.

 

[Guest]

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