Excel file without header into access (1 Viewer)

ria_arora · Jan 22, 2011

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 · Jan 22, 2011

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 · Jan 24, 2011

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 · Jan 24, 2011

Perhaps the sample posted here will give you some pointers.

ria_arora · Feb 11, 2011

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

vbaInet · Feb 11, 2011

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 · Mar 11, 2011

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 · Mar 11, 2011

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 · Mar 11, 2011

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

Excel file without header into access (1 Viewer)

ria_arora

Registered User.

CharlesWhiteman

Registered User.

ria_arora

Registered User.

John Big Booty

AWF VIP

ria_arora

Registered User.

vbaInet

AWF VIP

ria_arora

Registered User.

gemma-the-husky

Super Moderator

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread