Hi. I need some help importing data from excel. I know who to do it by using

the wizard from the File menu in Access 2000. What I need is a button on a

form to begin the wizard for importing data, also if the same file is to be

imported everytime can I automate it to select the file for the user?

 

Thanks

Alex Mather

Intranet Dev

CDNOW.com, Inc.

Alex,



Use this code as an example;



Option Explicit



' This declares my constant where the Excel spreadsheet is located and it's

name

Const constrFileName As String = "N:\COMMON\IMPORT\EXCEL123.XLS"



Private Sub cmdImport_Click()

On Error GoTo Err_cmdImport_Click



' This command imports an Excel 9.0 spreadsheet to a table called

tbl_Import, from the constrFileName file.

' The True at the end of the command tells us to import the the Field names

in the spreadsheet.  If there are no

' Field Names then change the True to False



DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImport",

constrFileName, True



Exit_cmdImport_Click:

    Exit Sub



Err_cmdImport_Click:

    MsgBox Err.Description

    Resume Exit_cmdImport_Click



End Sub



If the file name changes and or the directory where the spreadsheet changes,

then you can add a common dialog box and have the user select the file to

import.



Hope this helps,



John Ruff - The Eternal Optimist :)

 -----Original Message-----

From: 	Alex Mather [mailto:AMather@c...]

Sent:	Wednesday, June 13, 2001 12:22 PM

To:	Access

Subject:	[access] Importing Data From Excel



Hi. I need some help importing data from excel. I know who to do it by using

the wizard from the File menu in Access 2000. What I need is a button on a

form to begin the wizard for importing data, also if the same file is to be

imported everytime can I automate it to select the file for the user?



Thanks

Alex Mather

Intranet Dev

CDNOW.com, Inc.