Access to Excel to (MSDOS)CSV

[tomw99]

I have a table in Access XP that I currently export to excel then I perform the following steps:

1. Delete Row 1 (Header row)
2. Set the format on Column A to "Alignment" - Left (Indent)
3. Set the format on Column B to "Number" - 2 decimal places
4. Set the format on Columns C and D to "Date" - 3/14/2001
5. File "Save As" - Type - csv(MS-DOS)(*.csv)

The file has to be formated exactly as above since it is sent to another company to be imported into their AS-400 system. I have tried to record a macr to do this but when I use the macro they are unable to import the file.

Thanks in advance
Tom

 

[ETID]

Do this directly from Access.........

look in the help on imort/export specifications


About import/export specifications and Schema.ini files
An import/export specification contains information, such as file format, date order, or number formats, that Microsoft Access uses to import or export a fixed-width or delimited text file. An import/export specification is stored with the default name: Filename_ImportSpec or Filename_ExportSpec in the database that you import to or export from.

You create an import/export specification by using the Import Text Wizard or by using the Export Text Wizard. On the first screen of either wizard, click Advanced, define the specification, and then click Save As. Click the Specs button to select the import/export specification the next time you use either wizard.

Use an import/export specification when you want to repeatedly import to the same table, export to the same file, or automate the import or export process.

Alternatively, you can use a Schema.ini file in a Visual Basic program to provide even more control over data in the text file, such as specifying special currency formats or handling floating point data types. A schema.ini file is a text file containing entries that override default text driver settings in the Windows Registry. You store a schema.ini in the same folder as the imported or exported text file, and it must always be named schema.ini.