Exporting Input Masks or Format to Excel

[Guest]

I have an Input Mask of -

"NO-"AAAAA\-0000\-990

in a field in a table that I export to Excel via the
DoCmd.TransferSpreadsheet action.
The table's data is generated by a select (append) query.
The exported extract goes into a range on the specified worksheet and the
field in question goes into a column formatted as Text in Excel.

How do I export the 'static text' in the mask?
I have tried adding " ;0;_ " to the end of the mask to no avail.
I've tried adding literals to the Format property of the table's field but
appear to just append the data to the literals.
Importing the data in Excel works - but that's hardly automation! I don't
understand Excel Format Custom settings - but should I be looking there to
re-create the format I want to end up with?

I want " NO-ABCDE-1234-123 "
I'm getting ABCD1234123

(Using Office 2003 SP2)
(I wouldn't use Excel at all if I didn't have a stupid accountant!)

 

[Allan Murphy]

Dave

This might work

Export your data to your spreadsheet then in your spreadsheet format the
column using custom format to your format NO-ABCDE-1234-123.
Then save your spreadsheet and re-export your data.

Ihad a similar problem where the date and time columns were not in the
correct format, by saving the spreadsheet with the columns in the required
format I was able to export data into the spreadsheet with the correct time
etc displayed.

 

[Guest]

Thanks Allan,
I tried your idea - but apparently Excel can't cope with any Custom text
formatting. It only works with numbers (and dates etc) - so in the end I
removed the Input Mask from the 'exporting table' reformatted the data and
put ...

Expr3: "NO-" & Left([Iref],5) & "-" & Mid([iref],6,4) & "-" & Right([iref],3)

in the Access Apend Query which then writes to a standard text field.

So I basically ignored the available Input Mask in Access.

Dave.