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

Question

Hello All,

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!!!!

Thanks.

Answer 1

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.

---

Chris Ward

Answer 2

Hi Klaus,

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
http://support.microsoft.com/kb/219151  

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:

http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/c8496fa2-6f7e-46f1-8057-01d47bbb6317 

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 3

thank you

Answer 4

Stay away from merged cells.  Nothing but trouble there . . .

---

Ryan Shuell