remove "Merge and Center" and "Wrap Text" and special charaters from label fields from an Excel file before import it in Access 2010 using vba code
Wednesday, September 19, 2012 1:41 PM
I have couple of Excel 2010 files, here few columns are "merged" and few have "wrap text". And even I cannot see it, the label for few fields contain a special charater like "/" or "_".
I want to clean up the file before I do the import into Access 2010 tables, but the clean up I wanted in Access using VB code. I know how to do it in Excel but I'm not so familiar with code in Access for Excel files. I have too many Excel files to import the data in Access so that's why I want to use Access to do it kind of "automatic" .
I need you help here!!!!
Wednesday, September 19, 2012 2:42 PM
I know you are looking for help to do this in Access however, there is an overlap of forum helpers. Many Access users also help in Excel and vice versa. I would double your chances of help by posting in both forums.
I think there would be issues doing this in Access with the headers that are merged because when you remove the merge then some columns will not have headers and will not import correctly. My first instinct is to do thisin Excel first even though it is not what you want.
- Edited by KCDW Wednesday, September 19, 2012 2:44 PM
Thursday, September 20, 2012 8:46 AMModerator
Welcome to the Access forum.
If you want to automate excel from Access, please make sure to check the reference for "Microsoft Excel 14.0 Object Library".
Please refer to this KB for the basic details:
How to automate Microsoft Excel from Visual Basic
Have a nice day.
@Other readers: I'm not merging the threads currently, but I've added the thread link in Excel forum for your reference:
MSDN Community Support | Feedback to us
- Marked As Answer by klaus73 Thursday, September 20, 2012 11:42 AM
Thursday, September 20, 2012 11:41 AMthank you
Saturday, September 29, 2012 4:15 AM
Stay away from merged cells. Nothing but trouble there . . .