Biff clipboard format - what do we need it for ?

[dima69]

Biff format is defined as default format for copying data from Access to Excel (under registry key ...\Access\Clipboard Formats). This format has a known problem as numbers are pasted as text using Access 2002 with Excel 2002. So M$ suggest using older Biff5 format (from Office 2000) instead of Biff8.
But Biff5 has a problem too. Numbers from fields with input mask set are pasted in Excel as text, if you copy the record (not one field).
On the other hand, I've seen no problem pasting from Access to Excel in HTML or Text format (using paste special).
So I am considering to just erase that Biff format definition during application installation, that way HTML becomes default.

My question - is there any reason why I should't do that ?

[nico5038]

Can't think of any, the BIFF format did annoy me too :-)

Nic;o)

[NeoPa]

If data is converted to text as part of the transfer this may confuse Excel.
Excel is very particular about data being numeric or text.
Many people get confused over this. Sometimes data is a string of numeric digits; sometimes simply a number; sometimes a number but formatted to look like it could be a string.
If transferring as text loses this info then I'd be wary of it.

[dima69]

If data is converted to text as part of the transfer this may confuse Excel.
Excel is very particular about data being numeric or text.
Many people get confused over this. Sometimes data is a string of numeric digits; sometimes simply a number; sometimes a number but formatted to look like it could be a string.
If transferring as text loses this info then I'd be wary of it.

Seems like when pasting data as text, Excel automatically recognizes data types, just as it does when typing the data in. So as far as I see this is not a problem.

[NeoPa]

That IS the problem.
Consider string data which contains only digits?
Excel is prone to decide, on your behalf, that you don't want string data but numeric data. This can confuse the inexperienced no end. Trust me I have spent a lot of time fixing these problems for my users.

[dima69]

That IS the problem.
Consider string data which contains only digits?
Excel is prone to decide, on your behalf, that you don't want string data but numeric data. This can confuse the inexperienced no end. Trust me I have spent a lot of time fixing these problems for my users.

Ok, maybe it is a problem. But you must agree that having strings interpreted as numbers is much better than having numbers interpreted as text :)
And how do you get out of all this mess anyway ?

[NeoPa]

I can't claim to be a great expert in this area I'm afraid. I just chipped in with something I've learned from experience. If you can find a method that doesn't cause you any problems you're sorted ;) You know your data better than ... well anyone else hopefully.
Personally I get my Excel spreadsheet to query the database directly, but my situation is probably different from yours in many ways. That's an option of course, but you need to find the method that suits you (your data) best.