Excel file without header into access

[ria_arora]

Dear Friends,

I need to import Excel file into access table using VBA code and Excel file DOES NOT have any column heading. Any idea how to do import data without changing the file.

Thanks
Ria

 

[CharlesWhiteman]

Create a 'Import Specification'

Select the table in your database, right click and choose import, and create the import specificaiton from there. Then you can simply refer to the import specification in your code

 

[ria_arora]

Dear Charles,

Thanks for the prompt reply. I need to do this using VBA not manually. Please send me the sample code if you have.

Thanks and regards
Ria

 

[John Big Booty]

Perhaps the sample posted here will give you some pointers.

 

[ria_arora]

Looking something which can be done without creating the tmp table?

 

[vbaInet]

Like Charles mentioned, first create an Import Specification manually, then you can use that one Import Spec in code for any Excel spreadsheet.

You haven't even mentioned what your main goal is.

 

[ria_arora]

We get the file without header and I have table in MS Access database with all the fields but we don't want to change the file manually.

No of records also very every month.

Thanks
Ria

 

[gemma-the-husky]

when you say Excel file do you mean a csv or an xls

a csv is imported with docmd.transfertext, and you CAN specify which columns you import, with a file import specification

a xls is imported with docmd.transferspreadsheet, and you get rather less control. Access makes its own mind up about what format columns are, and sometimes gets it wrong - and you can't use a file import spec.

With an xls, you have two options
a) import based on column headers, irrespective of the actual column order OR
b) import respecting the column order, ignoring the column headers

given you don't have headers, you can just use option b.

 

[vbaInet]

In relation to Import Specs, perhaps these links will throw more light:

http://www.databasedev.co.uk/access2007_import_specs.html

http://office.microsoft.com/en-us/access-help/import-or-link-to-data-in-a-text-file-HA001232227.aspx