CODEWITHSUNDEEP
vbaexcel
kyusan93
kyusan93

Reputation: 422

VBA Number (11-01) as text/number in excel

I created VBA excel script to get data from website.

When it captures 11-01, excel automatically converts to 11-Jan (11/01/2012) instead of the original 11-01.

What is the method not to convert the 11-01 to 11-Jan?

Address         Unit   Postal
JALAN LEMPHENG  11-01  128791

Thanks.

Set clip = New DataObject
clip.SetText "<html><table><tr>" _
& "<td>" & ieSearchGet.outerHTML & "</td>" _
& "</tr></table></html>"
clip.PutInClipboard
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
    ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.PasteSpecial "Unicode Text"

Upvotes: 0

Views: 304

Answers (2)

Ann L.
Ann L.

Reputation: 13965

You could try adding the character "'" (that is, a single quote) before the text you paste.

I have never tried to do this, so this will probably not be quite the right code. You will probably have to experiment. But it's a start:

clip.SetText "<html><table><tr>" _
    & "<td>'" & ieSearchGet.outerHTML & "</td>" _
    & "</tr></table></html>"

Upvotes: 0

lmb
lmb

Reputation: 108

I think the best is to just change the format of the cell you want to paste your data in, before doing so. Does the following work ?

Set clip = New DataObject
                clip.SetText "<html><table><tr>" _
                & "<td>" & ieSearchGet.outerHTML & "</td>" _
                & "</tr></table></html>"
                clip.PutInClipboard
                ActiveCell.Offset(1, 0).Select
                Do While Not IsEmpty(ActiveCell)
                    ActiveCell.Offset(1, 0).Select
                    Selection.NumberFormat = "@" 'Format as Text the selected cell
                Loop
                ActiveSheet.PasteSpecial "Unicode Text"

Upvotes: 2

Related Questions