Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic macro for copying into Excel
I am trying to create a very basic macro in Excel, but keeping coming
unstuck and hope someone can help. I am trying to copy some text fields from an old DOS based programme into Excel. If you simply copy and paste, the text will copy itself into one cell only as a long string of text. I want to separate this text into different columns; the manual way of doing it is to click on paste options, use text import wizard, setting the columns as fixed width. I have tried recording this as a macro, but it is not working at all. Any suggestions on a basic macro to copy this data in as separate columns? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic macro for copying into Excel
You need to paste it in first, then use data=Text to Columns to do the same
as you describe. If do it that way with the recorder on, you will get the code you need. -- Regards, Tom Ogilvy "Moo" wrote: I am trying to create a very basic macro in Excel, but keeping coming unstuck and hope someone can help. I am trying to copy some text fields from an old DOS based programme into Excel. If you simply copy and paste, the text will copy itself into one cell only as a long string of text. I want to separate this text into different columns; the manual way of doing it is to click on paste options, use text import wizard, setting the columns as fixed width. I have tried recording this as a macro, but it is not working at all. Any suggestions on a basic macro to copy this data in as separate columns? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic macro for copying into Excel
Tom,
Thanks for your response. I've just tried that and it mainly works. However, how do I get the pasting to go into the cells I've highlighted (current cell), rather than cell A3, which is what I recorded the original macro from (see code below): ActiveSheet.Paste Selection.TextToColumns Destination:=Range("A3"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(14, 1), Array(21, 1), Array(26, 1), _ Array(53, 9), Array(55, 1)), TrailingMinusNumbers:=True End Sub I also keep getting the following message when running the macro, even though the cells I'm pasting into are blank and I have turned off the option to prompt when overwriting text! "Do you want to replace the contents of the destination cells?" Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic macro for copying into Excel
Selection.TextToColumns Destination:=Range("A3"),
would become Selection.TextToColumns Destination:=ActiveCell, I suspect. -- Regards, Tom Ogilvy "Moo" wrote: Tom, Thanks for your response. I've just tried that and it mainly works. However, how do I get the pasting to go into the cells I've highlighted (current cell), rather than cell A3, which is what I recorded the original macro from (see code below): ActiveSheet.Paste Selection.TextToColumns Destination:=Range("A3"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(14, 1), Array(21, 1), Array(26, 1), _ Array(53, 9), Array(55, 1)), TrailingMinusNumbers:=True End Sub I also keep getting the following message when running the macro, even though the cells I'm pasting into are blank and I have turned off the option to prompt when overwriting text! "Do you want to replace the contents of the destination cells?" Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set up basic Macro in EXcel to use in other applications | Excel Discussion (Misc queries) | |||
Some Basic Doubts in Excel Macro | Excel Programming | |||
Excel Macro Basic Help | Excel Programming | |||
visual basic macro in excel | Excel Programming | |||
visual basic macro in excel | Excel Programming |