Problem with embedded html code when pasting text into XLS from online bank statement

Question

hi

I dont dabble with Excel all that much . . but i have an Excel sheet that is linked into an MS Access DB.

I use it to copy the bank statement details  - doing a copy on the screen and pasting into an XLS . . works fine . . columns line-up etc

that XLS data is then copied into an Access table with an append query.

My problem is that one statement format now includes a magifying glass icon - which is a hyperlink to details about the supplier where i made a credit card payment.

When i do the copy - this link gets included . . and pasted into the sheet.

Once there . . i cant get rid of it . .easily. . . after much fiddling - i figured I can right-click on it and delete it manually. Tedious when there may be a 100 lines.

Also if i use paste-special and choose text instead of html - the link is gone but the whole text becomes a single lump - no more nice columns.

Any suggestions about how I can do the Paste - keep the columns and not paste the hyperlink icon ???

I am using MSA2010 now .. but have had this problem in MSA97 / 2003 and 2007

Cheers

PaulG

Answer 1

Hi Paul:

Insert and run the following macro after the paste:

Sub cleanup()

ActiveSheet.Pictures.Delete

End Sub

Macros are very easy to install and use:

1. ALT-F11  brings up the VBE window
2. ALT-I

    ALT-M opens a fresh module

3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

   1. bring up the VBE window as above

   2. clear the code out

   3. close the VBE window

To use the macro from Excel:

   1. ALT-F8

   2. Select the macro

   3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Answer 2

Thank You

Followed instructions anf it worked perfectly

cheers

PaulG