You'll need a DoCmd.TransferSpreadsheet statement. Look up VBA help on
TransferSpreadsheet method for details of the parameters of this method
(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range,
UseOA)
Your syntax will be something like:
DoCmd.TransferSpreadsheet acImport, , "YourTableName",
"PathTo/DataSchedule", True, "ImportData!"
if the Excel sheet has column headings which you want to use as field names,
or
DoCmd.TransferSpreadsheet acImport, , "YourTableName",
"PathTo/DataSchedule", False, "ImportData!"
if you want to import into predefined fields in your table.
The second method is more controllable, as you can define the datatype for
your field, rather than accepting the default type based on Access's
analysis of the first few records. And even better is to import into a set
of text fields, and then query the table to give only data of the correct
datatype in each field (via expressions such as ImportedDate:
Iif(IsNumeric([Field1],Cdate([Field1]),Null) in your query).
The FileName parameter needs to include the path (either full or relative)
if the Excel file is not in the same folder as the .mdb file. The Range
parameter can include either/both sheet name and range within the sheet,
separated by the "!" character: eg. to import range B2:G100 from sheet
ImportData, you would enter "ImportDate!B2:G100". Omitting the sheet name
will import from the first sheet in the workbook.
You can, naturally, use string variables for the string parameters, eg:
Dim strTableName As String, strFileName as String
...
strTableName = "YourTableName"
strFileName = "C:\Excel Folder\DataSchedule"
DoCmd.TransferSpreadsheet acImport, , strTableName, strFileName, False,
"ImportData!"
The omitted second parameter in each of these TransferSpreadsheet commands
is the SpreadsheetType; the default is generally fine, the Help file gives
details of all available. The final parameter (UseOA) is also omitted in
these commands; it refers to Office Automation, it's not covered in the Help
file, and I've never used it.
HTH,
Rob
