Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
Changing the cell format doesn't change existing cell content | Excel Discussion (Misc queries) | |||
Can cell format come from and change with reference cell format | Excel Discussion (Misc queries) | |||
find word in a cell then change cell format | Excel Programming | |||
How do i change the format of a cell based on the condition of another cell in same row? | Excel Programming |