import specific column from excel to access

[eshtul]

Hello everyone,

The whole access thing is pretty new for me and i will apprecite any help from you.


I have an existing excel data on more then 3600 file who I want to collect to one database.
The file structure is same so it shouldnt be a problem, but I cant do it with the wizard because there are too many files.
I need to select only few column from the excel file and not the whole sheet. I wrote a macro and i tried to fill the range fileld with the right syntex without succes (When I leave the range field empety it works).
lets say i need coulmn a:b & c
how sould i write it.

Thanks a lot

[June7]

You are using macro or VBA? Show the code you use. This worked for me:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table1", "C:\Book1.xlsx", , "Sheet1!A1:C10"
so did
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table1", "C:\Book1.xlsx", , "Sheet1!A:C"

[eshtul]

sorry i didnt asked it well
your code is great, but lets say i want to import only column A:B and D. without importing column C

I hope now its more clear

thanks

[alansidman]

The following is from MS Knowledge Base. There may be a work around but this is what the gods have to say on the matter at this site.

http://office.microsoft.com/en-us/ac...x#BMimportdata

Review the source data and take action as described in this table.
ELEMENT DESCRIPTION
Number of columns The number of source columns that you want to import cannot exceed 255, because Access does not support more than 255 fields in a table.

Skipping columns and rows
It is a good practice to include only the rows and columns that you want to import in the source worksheet or named range.
Rows You cannot filter or skip rows during the import operation.
Columns You cannot skip columns during the operation if you choose to add the data to an existing table.

Tabular format Ensure that the cells are in tabular format. If the worksheet or named range includes merged cells, the contents of the cell are placed in the field that corresponds to the leftmost column, and the other fields are left blank.

Blank columns, rows, and cells Delete all unnecessary blank columns and blank rows in the worksheet or range. If the worksheet or range contains blank cells, try to add the missing data. If you are planning to append the records to an existing table, ensure that the corresponding field in the table accepts null (missing or unknown) values. A field will accept null values if its Required field property is set to No and its ValidationRule property setting doesn't prevent null values.

A workaround in my mind is to import A to D and then delete C once it is in Access.

Alan

[June7]

Confirmed. Cannot skip columns, even when the table did not exist. I tried "Sheet1!A:B,D" and "Sheet1!A:B,Sheet1!D" and both errored. Code replaced the ! with $.

If this is a one-time effort, then Alan's suggestion will get it done.

[eshtul]

its wierd cause when I used the Wizard there is an option of "skip the column".
Its not one time matter, I need to import hunder of files and, without it I might exceed szie limit.
I also tried I tried "Sheet1!A:B,D" and "Sheet1!A:B,Sheet1!D" but maybe there is a right syntax for it.
I can open a new Table if its necessary.

if someone knows i will apreecite that a lot
thanks

[June7]

Google: VBA Access import from Excel skip column