HealdsburgCA
09-12-2012, 08:33 AM
Howdy all,
Howdy all,
I have created an Access database and am ready to import data stored inExcel worksheets. I found the macro I need to combine the worksheets into asingle work sheet. The problem I have is that the character count in the"subject" column ranges from 100-700 characters but a text field inAccess is only 255 characters. Memo fields are not an option. To overcome thisin Access I have created multiple text fields (Subject1, Subject2, Subject3,etc.) to contain the data. But I need to get the Excel data formatted for animport.
I am still a bit green in the VBA/Macro world and I would appreciate somehelp in finding some code to take the "subject" column (column B) andbreak it up into multiple columns based on the # of characters.
i.e new column F = Characters 1-255 from Column B; new column G = Characters256-510 from Column B; etc.
It would be great if the code had the option of ensuring that a word isn’tsplit between columns, (meaning if the 255th character landed in the middle of “municipality”Column F wouldn’t end with “munici” and Column G begin with “pality”). Instead the full word would get pushed intoColumn G.
Any thoughts or ideas are greatly appreciated!
-Joshua
Howdy all,
I have created an Access database and am ready to import data stored inExcel worksheets. I found the macro I need to combine the worksheets into asingle work sheet. The problem I have is that the character count in the"subject" column ranges from 100-700 characters but a text field inAccess is only 255 characters. Memo fields are not an option. To overcome thisin Access I have created multiple text fields (Subject1, Subject2, Subject3,etc.) to contain the data. But I need to get the Excel data formatted for animport.
I am still a bit green in the VBA/Macro world and I would appreciate somehelp in finding some code to take the "subject" column (column B) andbreak it up into multiple columns based on the # of characters.
i.e new column F = Characters 1-255 from Column B; new column G = Characters256-510 from Column B; etc.
It would be great if the code had the option of ensuring that a word isn’tsplit between columns, (meaning if the 255th character landed in the middle of “municipality”Column F wouldn’t end with “munici” and Column G begin with “pality”). Instead the full word would get pushed intoColumn G.
Any thoughts or ideas are greatly appreciated!
-Joshua