Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find row with more than 255 characters in a cell
I have code to import an Excel spreadsheet into an Access database. What I
am finding is that Access decides the data type for a field by looking in the first 25 rows of the spreadsheet. If it sees more than 255 characters in a cell it decides the column is "memo", and if it doesn't it decided the field is "text" and truncates after 255 characters--this is not desired! Right now I am manually looking at the rows to find one that has a large amount of data, cutting and inserting it at the top of the spreadsheet. I would really like to include this step in my code but have no idea how to go about it. Can anyone help? Thank you, Judy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find row with more than 255 characters in a cell
Judy,
The "Len" function works in both VBA and Excel. In VBA it can determine the length of string variables and the length of data in a cell.... x = Len(Range("B5") or x = Len(strVariable) In Excel this works... = Len(B5) I suspect that Access will do what you want, though I am not the person to ask. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Judy Ward" wrote in message I have code to import an Excel spreadsheet into an Access database. What I am finding is that Access decides the data type for a field by looking in the first 25 rows of the spreadsheet. If it sees more than 255 characters in a cell it decides the column is "memo", and if it doesn't it decided the field is "text" and truncates after 255 characters--this is not desired! Right now I am manually looking at the rows to find one that has a large amount of data, cutting and inserting it at the top of the spreadsheet. I would really like to include this step in my code but have no idea how to go about it. Can anyone help? Thank you, Judy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find row with more than 255 characters in a cell
Hi
If you change the MSAccess code so that it imports the data into an existing table rather than a new one each time then it won't 'guess' what data type the columns are but will use the field types already in the table. So 1. Create a new table with the correct field types 2. Update your MSAccess code so that it Imports the data into your new staging table Copies the data from the staging table into the desired data table Clears the data from the staging table so it's ready for next time. For specific code suggest you post in the Access discussion group. HTH Barry "Judy Ward" wrote: I have code to import an Excel spreadsheet into an Access database. What I am finding is that Access decides the data type for a field by looking in the first 25 rows of the spreadsheet. If it sees more than 255 characters in a cell it decides the column is "memo", and if it doesn't it decided the field is "text" and truncates after 255 characters--this is not desired! Right now I am manually looking at the rows to find one that has a large amount of data, cutting and inserting it at the top of the spreadsheet. I would really like to include this step in my code but have no idea how to go about it. Can anyone help? Thank you, Judy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find row with more than 255 characters in a cell
Thank you both for responding
I guess I wasn't clear enough in my question since you both think I am looking for Access code. I run Excel macros to clean up the data in the spreadsheet to get it ready to import to Access. I do import into an existing table with the correct field types. I perform this import on a daily basis and I can tell you from experience that the import behaves differently if it doesn't find the defined data type in the first 25 rows (and I read about this in Microsoft documentation). Sorry, I don't remember what version of Excel we use at work (I'm home now). I think I can figure out how to loop through the rows and check the length of the field I am interested in (thanks to Jim for suggesting the Len function). Thank you again for your responses, Judy "Barry" wrote: Hi If you change the MSAccess code so that it imports the data into an existing table rather than a new one each time then it won't 'guess' what data type the columns are but will use the field types already in the table. So 1. Create a new table with the correct field types 2. Update your MSAccess code so that it Imports the data into your new staging table Copies the data from the staging table into the desired data table Clears the data from the staging table so it's ready for next time. For specific code suggest you post in the Access discussion group. HTH Barry "Judy Ward" wrote: I have code to import an Excel spreadsheet into an Access database. What I am finding is that Access decides the data type for a field by looking in the first 25 rows of the spreadsheet. If it sees more than 255 characters in a cell it decides the column is "memo", and if it doesn't it decided the field is "text" and truncates after 255 characters--this is not desired! Right now I am manually looking at the rows to find one that has a large amount of data, cutting and inserting it at the top of the spreadsheet. I would really like to include this step in my code but have no idea how to go about it. Can anyone help? Thank you, Judy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to find row with more than 255 characters in a cell
Would it help you any if you Conditionally Formatted the range to highlight
any cells whose text was longer than 255 characters in order to see your "too long" cells? Highlight the first 25 rows, click on Format/Conditional Formatting in Excel's menu bar, select Formula Is in the first drop down box, and put this formula in the second field... =LEN(A1)255 click the Format button and select a pale color from the Patterns tab, then OK your way back to the worksheet. Any cells containing more than 255 characters should standout with the color you selected. Rick "Judy Ward" wrote in message ... I have code to import an Excel spreadsheet into an Access database. What I am finding is that Access decides the data type for a field by looking in the first 25 rows of the spreadsheet. If it sees more than 255 characters in a cell it decides the column is "memo", and if it doesn't it decided the field is "text" and truncates after 255 characters--this is not desired! Right now I am manually looking at the rows to find one that has a large amount of data, cutting and inserting it at the top of the spreadsheet. I would really like to include this step in my code but have no idea how to go about it. Can anyone help? Thank you, Judy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting characters in text cell to find 255 limit | Excel Discussion (Misc queries) | |||
How to find no.of characters in a cell? | Excel Discussion (Misc queries) | |||
How do i find a cell that contains more than 255 characters | Excel Programming | |||
find item in drop down cell by typing the first few characters | Excel Discussion (Misc queries) | |||
How do you find and replace tab characters in a cell in Excel? | Excel Discussion (Misc queries) |