Need Excel macro for exporting data to Access
gocush wrote:
Has someone written a macro to run in Excel that will append a
fixed range of
data into an existing Access table.
The following is code that I got from Andy Wiggins some time ago
<snip
That's a lot of code <g (and anyhow doesn't do what the OP asked i.e.
creates a new table rather than inserting into an existing table). I
think this could be a job for 'ADO in just four lines':
Sub JustFourBygLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyJetDB.mdb"
con.Execute _
"INSERT INTO MyNewtable (MyCol1, MyCol2, MyCol2)" & _
" SELECT MyCol1, MyCol2, MyCol3 FROM" & _
" [Excel 8.0;HDR=YES;Database=C:\MyWorkbook;].MyRange;"
End Sub
Jamie.
--
|