Provided you have data on the clipboard:

Sub PasteData()
ActiveSheet.PasteSpecial Format:="Text"
End Sub


"Ray S." wrote:

how would I do that programmatically?

"johnmoreland21" wrote:

Can you not paste special as text?


"Ray S." wrote:

I thought I had this worked out, but it still craps out. I have to cut and
paste selected data from a web table into excel and ultimately into MS
Access. The problem is that Access insists on treating numeric looking data
as numbers when it is not. Leading zeros are dropped and if alphabetic
symbols occur I get data type conversion errors. So, I thought I figured out
a macro to drop the web data into my clipboard then send it to Excel before
transferring it to Access. I set the cell formatting in Excel to text, but
when the clipboard data is pasted into Excel, again the formatting drops the
leading zeros.

Essentially I was doing this:

Range("A1").Select
ActiveSheet.Paste
ActiveSheet.SaveAs ("...path\imported.xls")
ClearClipboard

How can I avoid this problem?