Macro to run Import wizard in Access 1

[OzzieOwl]

Does anyone know how to create a macro in Access to import data from an Excel spreadsheet to an existing table in Access. I would nally record the macro to get some idea, but that option does not appear to be available on Access

Any help greatly appreciated.

Martin

 

[LSTAN]

Hi MartinLymn

You can write a code and attach it to a commandbutton, here is what I did

Sub GetXLData_Click()

Dim myXL As Object, myDB As Database, myRS As Recordset

Set myDB = CurrentDb()
Set myRS = myDB.OpenRecordset("tblYourTableName&quot
Set myXL = CreateObject("Excel.Application&quot
Set myXL = GetObject("c:\myPath\GetDataXL.xls&quot

myXL.Application.Visible = True
myXL.Application.windows(1).Visible = True

With myRS
.AddNew
.Fields("FieldNo1&quot = myXL.sheets("sheet1&quot.range("a1&quot.Value
.Fields("FieldNo2&quot = myXL.sheets("sheet1&quot.range("b1&quot.Value
.Fields("FieldNo3&quot = myXL.sheets("sheet1&quot.range("c1&quot.Value
.Update
End With

With myXL.Application
.Save
.Quit
End With
Set myXL = Nothing

DoCmd.OpenTable "tblYourTableName"

End Sub

Hope this is of some help to you.

Regards

LSTAN