I do more flat files than xls. Pitfalls can be consistent first row haveing
the field names or not having field names. However, a CSV file is not
typically considered as spreadsheet. You might have better luckwith a
TransferText command. When looking over you code it looks like your are doing
more that just importing a a CSV file data to a single table. I hope this
helps.
DoCmd.TransferText acImportFixed, "SpecificaitonName", "TableName", "FileNmae"
TransferText method carries out the TransferText action in Visual Basic.
expression.TransferText(TransferType, SpecificationName, TableName,
FileName, HasFieldNames, HTMLTableName, CodePage)
expression Required. An expression that returns one of the objects in the
Applies To list.
TransferType Optional AcTextTransferType.
AcTextTransferType can be one of these AcTextTransferType constants.
acExportDelim
acExportFixed
acExportHTML
acExportMerge
acImportDelim default
acImportFixed
acImportHTML
acLinkDelim
acLinkFixed
acLinkHTML
If you leave this argument blank, the default constant (acImportDelim) is
assumed.
Notes
Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or
acExportMerge transfer types are supported in a Microsoft Access project
(.adp).
SpecificationName Optional Variant. A string expression that's the name of
an import or export specification you've created and saved in the current
database. For a fixed-width text file, you must either specify an argument or
use a schema.ini file, which must be stored in the same folder as the
imported, linked, or exported text file. To create a schema file, you can use
the text import/export wizard to create the file. For delimited text files
and Microsoft Word mail merge data files, you can leave this argument blank
to select the default import/export specifications.
TableName Optional Variant. A string expression that's the name of the
Microsoft Access table you want to import text data to, export text data
from, or link text data to, or the Microsoft Access query whose results you
want to export to a text file.
FileName Optional Variant. A string expression that's the full name,
including the path, of the text file you want to import from, export to, or
link to.
HasFieldNames Optional Variant. Use True (–1) to use the first row of the
text file as field names when importing, exporting, or linking. Use False (0)
to treat the first row of the text file as normal data. If you leave this
argument blank, the default (False) is assumed. This argument is ignored for
Microsoft Word mail merge data files, which must always contain the field
names in the first row.
HTMLTableName Optional Variant. A string expression that's the name of the
table or list in the HTML file that you want to import or link. This argument
is ignored unless the transfertype argument is set to acImportHTML or
acLinkHTML. If you leave this argument blank, the first table or list in the
HTML file is imported or linked. The name of the table or list in the HTML
file is determined by the text specified by the <CAPTION> tag, if there's a
<CAPTION> tag. If there's no <CAPTION> tag, the name is determined by the
text specified by the <TITLE> tag. If more than one table or list has the
same name, Microsoft Access distinguishes them by adding a number to the end
of each table or list name; for example, Employees1 and Employees2.
CodePage Optional Variant. A Long value indicating the character set of
the code page.
Remarks
For more information on how the action and its arguments work, see the
action topic.
You can leave an optional argument blank in the middle of the syntax, but
you must include the argument's comma. If you leave a trailing argument
blank, don't use a comma following the last argument you specify.
Note You can also use ActiveX Data Objects (ADO) to create a link by using
ActiveConnection property for the Recordset object.
Example
The following example exports the data from the Microsoft Access table
External Report to the delimited text file April.doc by using the
specification Standard Output:
DoCmd.TransferText acExportDelim, "Standard Output", "External Report",
"C:\Txtfiles\April.doc"
TransferSpreadsheet Method
See AlsoApplies ToExampleSpecificsThe TransferSpreadsheet method carries out
the TransferSpreadsheet action in Visual Basic.
expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName,
FileName, HasFieldNames, Range, UseOA)
expression Required. An expression that returns one of the objects in the
Applies To list.
TransferType Optional AcDataTransferType.
AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.
SpreadsheetType Optional AcSpreadSheetType.
AcSpreadSheetType can be one of these AcSpreadSheetType constants.
acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8 default
acSpreadsheetTypeExcel9 default
acSpreadsheetTypeLotusWJ2 - Japanese version only
acSpreadsheetTypeLotusWK1
acSpreadsheetTypeLotusWK3
acSpreadsheetTypeLotusWK4
Note You can link to data in a Lotus 1-2-3 spreadsheet file, but this data
is read-only in Microsoft Access. You can import from and link (read-only) to
Lotus .WK4 files, but you can't export Microsoft Access data to this
spreadsheet format. Microsoft Access also no longer supports importing,
exporting, or linking data from Lotus .WKS or Microsoft Excel version 2.0
spreadsheets by using this method.
If you leave this argument blank, the default constant
(acSpreadsheetTypeExcel8) is assumed.
TableName Optional Variant. A string expression that's the name of the
Microsoft Access table you want to import spreadsheet data into, export
spreadsheet data from, or link spreadsheet data to, or the Microsoft Access
select query whose results you want to export to a spreadsheet.
FileName Optional Variant. A string expression that's the file name and
path of the spreadsheet you want to import from, export to, or link to.
HasFieldNames Optional Variant. Use True (–1) to use the first row of the
spreadsheet as field names when importing or linking. Use False (0) to treat
the first row of the spreadsheet as normal data. If you leave this argument
blank, the default (False) is assumed. When you export Microsoft Access table
or select query data to a spreadsheet, the field names are inserted into the
first row of the spreadsheet no matter what you enter for this argument.
Range Optional Variant. A string expression that's a valid range of cells
or the name of a range in the spreadsheet. This argument applies only to
importing. Leave this argument blank to import the entire spreadsheet. When
you export to a spreadsheet, you must leave this argument blank. If you enter
a range, the export will fail.
UseOA Optional Variant.
Remarks
For more information on how the action and its arguments work, see the
action topic.
You can leave an optional argument blank in the middle of the syntax, but
you must include the argument's comma. If you leave a trailing argument
blank, don't use a comma following the last argument you specify.
Note You can also use ActiveX Data Objects (ADO) to create a link by using
the ActiveConnection property for the Recordset object.
Example
The following example imports the data from the specified range of the Lotus
spreadsheet Newemps.wk3 into the Microsoft Access Employees table. It uses
the first row of the spreadsheet as field names.
DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"
