Cell format change mess
 

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?

Cell format change mess
 

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?

Cell format change mess
 

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?

Cell format change mess
 

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?

Cell format change mess
 

If that doesnt work you could try changing the cell values to put the leading
zeros back in. If you data is simple to select it can be done easily with
code like this -

Sub test()

Dim cell As Range
For Each cell In Selection
cell = "'0" & cell.Value
Next cell
End Sub

This will force Access to read these cells as text rather than numeric.

"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?

Cell format change mess
 

I see...it gets a little more complicated because one of the received fields
is in long date format with spaces and time. That messes up my text format
paste because of no clear delimiter.

"johnM" wrote:

If that doesnt work you could try changing the cell values to put the leading
zeros back in. If you data is simple to select it can be done easily with
code like this -

Sub test()

Dim cell As Range
For Each cell In Selection
cell = "'0" & cell.Value
Next cell
End Sub

This will force Access to read these cells as text rather than numeric.

"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?