Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scientific Notation - I wish MS Gave us a switch to turn it off
Microsoft really needs a feature to turn off Scientific Notation. It's
causes us no end of aggravation and we give them a ton of businsess. I'm importing a spreadsheet into Access 2003 from Excel 2003. There are account numbers such as 0123456789 or 0123E456987 that undesirably convert to scientific notation upon import. Users enter these values in Excel, so the column is formatted as text (if it were General, the lead 0 would drop out.) I then, using automation from Access running Excel macros, append an "x" to the front of the cell values and convert the format to General, then use TransferSpreadsheet to bring into Access. That's a hassel. Is there an algorithm I can us in Access to convert scientific notation back to text or will the fact some accounts start with a 0 create a problem? We've had this issue for over a year withou satisfactory resolution. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scientific Notation - I wish MS Gave us a switch to turn it off
Hi Perico,
I hope that I have read your question correctly. This type of problem usually occurs because when Access imports an Excel file, it reads the first few rows of the Excel worksheet and sets data types for each column based on its own algorithm. Unfortunately Access can make mistakes at times. Here is a little trick I use to import worksheets into Access. Set up the Excel worksheet with column headings, then three hidden lines, then user data. In the three hidden lines enter dummy data that is in the correct format for each column. i.e. for your account numbers enter "xxxx" to force Access to recognise this as a text column. In Access, set up a link to the Excel worksheet. Write a make table query that copies the linked Excel data to a new table. Use a criteria in this query to exclude the dummy data in the worksheet. e.g. in the above example WHERE [account number] < 'xxxx'. Now you can just run the make table query each time you wish to import the data. Ed Ferrero Microsoft really needs a feature to turn off Scientific Notation. It's causes us no end of aggravation and we give them a ton of businsess. I'm importing a spreadsheet into Access 2003 from Excel 2003. There are account numbers such as 0123456789 or 0123E456987 that undesirably convert to scientific notation upon import. Users enter these values in Excel, so the column is formatted as text (if it were General, the lead 0 would drop out.) I then, using automation from Access running Excel macros, append an "x" to the front of the cell values and convert the format to General, then use TransferSpreadsheet to bring into Access. That's a hassel. Is there an algorithm I can us in Access to convert scientific notation back to text or will the fact some accounts start with a 0 create a problem? We've had this issue for over a year withou satisfactory resolution. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scientific Notation - I wish MS Gave us a switch to turn it of
Interesting, Ed. (I still wish MS made it easier.) I use the
transferspreadsheet method to pull the data into Access. Would your technique work with that? What does you code look like setting up and breaking the link? "Ed Ferrero" wrote: Hi Perico, I hope that I have read your question correctly. This type of problem usually occurs because when Access imports an Excel file, it reads the first few rows of the Excel worksheet and sets data types for each column based on its own algorithm. Unfortunately Access can make mistakes at times. Here is a little trick I use to import worksheets into Access. Set up the Excel worksheet with column headings, then three hidden lines, then user data. In the three hidden lines enter dummy data that is in the correct format for each column. i.e. for your account numbers enter "xxxx" to force Access to recognise this as a text column. In Access, set up a link to the Excel worksheet. Write a make table query that copies the linked Excel data to a new table. Use a criteria in this query to exclude the dummy data in the worksheet. e.g. in the above example WHERE [account number] < 'xxxx'. Now you can just run the make table query each time you wish to import the data. Ed Ferrero Microsoft really needs a feature to turn off Scientific Notation. It's causes us no end of aggravation and we give them a ton of businsess. I'm importing a spreadsheet into Access 2003 from Excel 2003. There are account numbers such as 0123456789 or 0123E456987 that undesirably convert to scientific notation upon import. Users enter these values in Excel, so the column is formatted as text (if it were General, the lead 0 would drop out.) I then, using automation from Access running Excel macros, append an "x" to the front of the cell values and convert the format to General, then use TransferSpreadsheet to bring into Access. That's a hassel. Is there an algorithm I can us in Access to convert scientific notation back to text or will the fact some accounts start with a 0 create a problem? We've had this issue for over a year withou satisfactory resolution. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scientific Notation - I wish MS Gave us a switch to turn it of
Hi Perico,
Interesting, Ed. (I still wish MS made it easier.) I use the transferspreadsheet method to pull the data into Access. Would your technique work with that? What does you code look like setting up and breaking the link? With my method there is no need to use code. I just leave the link in Access and replace the Excel Workbook as necessary. Yes, the method would work if you use the TransferSpreadsheet method Code would be; DoCmd.TransferSpreadsheet acImport, 8, "ExcelLink", "C:\myPath\myBook.xls", True, "" DoCmd.OpenQuery "qryDelDummy" Where the query qryDelDummy is something like DELETE * FROM ExcelLink WHERE ExcelLink.[account number] Like "xxxx*" Hope this helps. If you wish to continue this thread, perhaps the access newsgroup might be a better place? Ed Ferrero |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
turn off scientific notation | Excel Discussion (Misc queries) | |||
Scientific notation | Excel Worksheet Functions | |||
how can I turn off scientific notation | Excel Discussion (Misc queries) | |||
Turn Off Scientific Notation | Excel Programming | |||
Turn Off Scientific Notation | Excel Programming |