Preferably using a common dialog?

Thanks

J

You could try to use automation to do that. Check an example how to transfer
data from Access to Excel. It could give you some ideas

http://support.microsoft.com/default.aspx?scid=kb;en-us;210288

--
Val Mazur
Microsoft MVP

Hi,
    Is there an article on how to Import Excel data into an Access 2000
Table using VB 6.0.

Preferably using a common dialog?

You can use the CommonDialog to get the file name and ADO to perform the import:

Sub ImportExcelToAccess()

Dim cnn As New ADODB.Connection
Dim sqlString As String

cnn.Open _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=D:\My Documents\Accessdb.mdb;" & _
   "Jet OLEDB:Engine Type=4"

'Assumes Access table does not already exist
sqlString = "SELECT * INTO [tblExcelNew] FROM [Excel 8.0;DATABASE=D:\My
Documents\Test.xls;HDR=NO].[Sheet1$]"

cnn.Execute sqlString
cnn.Close
Set cnn = Nothing

End Sub

If you need to import into an existing Access table post a follow-up.

Microsoft MVP (Visual Basic)

This is good, but what if the location of the spreadsheet is not known?

You can build your connection string based upon the location supplied from the Common Dialog
control. Assuming you know how to use the Command Dialog control and have assigned the value to a
variable (strExcelWorkbookPath in this example):

sqlString = "SELECT * INTO [tblExcelNew] FROM [Excel 8.0;DATABASE=" & strExcelWorkbookPath &
";HDR=NO].[Sheet1$]"

Microsoft MVP (Visual Basic)