Access ignores Excel cell formating...

[MarkTac]

Happy holiday everyone!

So I have employee number column formatted ##-##### using Format Cell in Excel. After transferring my sheet over to Access formatting disappears:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTmp", strFile, True

Are there any another "easy" ways to edit / format Excel cell values before transferring them over to Access?

 

[MazeWorX]

Did you use an Import Specification File? what is the Field data type in Access? I find Access gets a little funky with some imports and excel, I prefer a text file however you could run an update query after the data is in the db reformatting it.

With an update query you could use something like:

EmployeeNumber:Left([EmployeeNumber],2) & "-" & Right([EmployeeNumber],4)

this assumes the field is named EmployeeNumber

Thoughts?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[SkipVought]

Hi,

FORMATS do not get imported. In this case, you have STORED the Employee ID as a NUMBER. Do you plan to perform arithmetic on your Employee ID? Of course not. So it REALLY ought to have been stored as TEXT--WITH the DASH in the appropriate place, just as MazeWorx has suggested with the Query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MarkTac]

So I guess proper way of formatting employee numbers would be after importing to Access and using DAO.Recordset.

Forgot to mention that spreadsheet contains roughly 20,000 records.