| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trouble Pasting
I have an example that best shows what I am trying to do. If you go to
http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trouble Pasting
I went to that sitecopied all of the tablepasted into excel 2003 sp3 just
fine. Same with only the first few rows..? But, why are you doing copy/paste when you can use an external query to just bring in the table and then cull what is needed. Below recorded and should be cleaned up further Sub Macro2() ' ' Macro2 Macro ' Macro recorded 6/23/2009 by Donald B. Guillett ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.ssa.gov/OACT/STATS/table4c6.html", Destination:=Range("A3")) .Name = "table4c6" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "7" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub Again, recorded and should be cleaned up to avoid selections. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 6/23/2009 by Donald B. Guillett ' ' Range("A2:G12").Select Selection.Copy Sheets("Sheet3").Select Range("A2").Select ActiveSheet.Paste End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "RNorfolk" wrote in message ... I have an example that best shows what I am trying to do. If you go to http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trouble Pasting
I used xl2003 to test and I copied entire rows of that table and pasted into A1
of a new worksheet. And all the cells were properly populated--and so were the borders! If you close excel and reopen it, then try the copy|paste, do things work ok? Excel likes to help by remembering the choices you made from the last text to columns. If this doesn't help, maybe you could describe what happens in more detail. RNorfolk wrote: I have an example that best shows what I am trying to do. If you go to http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trouble Pasting
Don, I am mightily impressed! I am a rookie at any kind of programming.
The user I support goes into a government site that requires a password, then creates a table of injuries and causes with protected data. She ten copies the resulting table and pastes it into Excel so she can drunch the numbers and make graphs. I'm looking for a simple fix, if I can find one. In 2007, there are 3 options in Past Special, html, Unicode Text and Text. With html it goes into the cells pretty, it the other 2 it doesn't. 2003 doesn't have the html option, and that is causing our problem. For the macro, since the table has to be created on a secure site each time it is needed, will it work? Thanks and I am impressed... Roger -- Roger Norfolk Office 97 MOUS Master "Don Guillett" wrote: I went to that sitecopied all of the tablepasted into excel 2003 sp3 just fine. Same with only the first few rows..? But, why are you doing copy/paste when you can use an external query to just bring in the table and then cull what is needed. Below recorded and should be cleaned up further Sub Macro2() ' ' Macro2 Macro ' Macro recorded 6/23/2009 by Donald B. Guillett ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.ssa.gov/OACT/STATS/table4c6.html", Destination:=Range("A3")) .Name = "table4c6" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "7" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub Again, recorded and should be cleaned up to avoid selections. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 6/23/2009 by Donald B. Guillett ' ' Range("A2:G12").Select Selection.Copy Sheets("Sheet3").Select Range("A2").Select ActiveSheet.Paste End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "RNorfolk" wrote in message ... I have an example that best shows what I am trying to do. If you go to http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trouble Pasting
Thanks for the reply, Dave.
I just tried a row as you had indicated, and it dropped all the numbers from the 7 columns on the website into the cell I pasted into the first, not across the cells. I was able to take this table and do a text to columns, but that didn't work for the table my user gets as the fields are vastly different widths, and not comma or other delimited. Thanks Roger -- Roger Norfolk Office 97 MOUS Master "Dave Peterson" wrote: I used xl2003 to test and I copied entire rows of that table and pasted into A1 of a new worksheet. And all the cells were properly populated--and so were the borders! If you close excel and reopen it, then try the copy|paste, do things work ok? Excel likes to help by remembering the choices you made from the last text to columns. If this doesn't help, maybe you could describe what happens in more detail. RNorfolk wrote: I have an example that best shows what I am trying to do. If you go to http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trouble Pasting
Hmm - went just fine in 2003 for you.... what do I have different?
my brain hurts... ANy thoughts on what could cause the difference? -- Roger Norfolk Office 97 MOUS Master "Don Guillett" wrote: I went to that sitecopied all of the tablepasted into excel 2003 sp3 just fine. Same with only the first few rows..? But, why are you doing copy/paste when you can use an external query to just bring in the table and then cull what is needed. Below recorded and should be cleaned up further Sub Macro2() ' ' Macro2 Macro ' Macro recorded 6/23/2009 by Donald B. Guillett ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.ssa.gov/OACT/STATS/table4c6.html", Destination:=Range("A3")) .Name = "table4c6" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "7" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub Again, recorded and should be cleaned up to avoid selections. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 6/23/2009 by Donald B. Guillett ' ' Range("A2:G12").Select Selection.Copy Sheets("Sheet3").Select Range("A2").Select ActiveSheet.Paste End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "RNorfolk" wrote in message ... I have an example that best shows what I am trying to do. If you go to http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trouble Pasting
Maybe it's how the browser puts the stuff in the clipboard.
I tried copying entire rows using Firefox 3.0.11 and all the rows went into a single cell. I tried copying entire rows using MSIE 7.x and it pasted very nicely. What browser are you using? I used xl2003 under WinXP Home, too. Maybe those are important???? RNorfolk wrote: Thanks for the reply, Dave. I just tried a row as you had indicated, and it dropped all the numbers from the 7 columns on the website into the cell I pasted into the first, not across the cells. I was able to take this table and do a text to columns, but that didn't work for the table my user gets as the fields are vastly different widths, and not comma or other delimited. Thanks Roger -- Roger Norfolk Office 97 MOUS Master "Dave Peterson" wrote: I used xl2003 to test and I copied entire rows of that table and pasted into A1 of a new worksheet. And all the cells were properly populated--and so were the borders! If you close excel and reopen it, then try the copy|paste, do things work ok? Excel likes to help by remembering the choices you made from the last text to columns. If this doesn't help, maybe you could describe what happens in more detail. RNorfolk wrote: I have an example that best shows what I am trying to do. If you go to http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trouble Pasting
We cannot with a regular paste from that url. It puts all the entries in
column A, but does split out the rows - I will try in another environment, but am at a loss. We cannot copy and paste from that table into Excel 2003 and make it work. I can with Excel 2007. I could with Excel 97. Any thoughts on what else to try? Any thoughts if patch level, operating system or any other influences might have on the action? Thanks for the help. Roger -- Roger Norfolk Office 97 MOUS Master "Don Guillett" wrote: From the url you gave, JUST a regular paste was fine. As I recall, if you sign in first you may then use external queries. But, I haven't tried it. -- Don Guillett Microsoft MVP Excel SalesAid Software "RNorfolk" wrote in message ... Don, I am mightily impressed! I am a rookie at any kind of programming. The user I support goes into a government site that requires a password, then creates a table of injuries and causes with protected data. She ten copies the resulting table and pastes it into Excel so she can drunch the numbers and make graphs. I'm looking for a simple fix, if I can find one. In 2007, there are 3 options in Past Special, html, Unicode Text and Text. With html it goes into the cells pretty, it the other 2 it doesn't. 2003 doesn't have the html option, and that is causing our problem. For the macro, since the table has to be created on a secure site each time it is needed, will it work? Thanks and I am impressed... Roger -- Roger Norfolk Office 97 MOUS Master "Don Guillett" wrote: I went to that sitecopied all of the tablepasted into excel 2003 sp3 just fine. Same with only the first few rows..? But, why are you doing copy/paste when you can use an external query to just bring in the table and then cull what is needed. Below recorded and should be cleaned up further Sub Macro2() ' ' Macro2 Macro ' Macro recorded 6/23/2009 by Donald B. Guillett ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.ssa.gov/OACT/STATS/table4c6.html", Destination:=Range("A3")) .Name = "table4c6" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "7" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub Again, recorded and should be cleaned up to avoid selections. Sub Macro3() ' ' Macro3 Macro ' Macro recorded 6/23/2009 by Donald B. Guillett ' ' Range("A2:G12").Select Selection.Copy Sheets("Sheet3").Select Range("A2").Select ActiveSheet.Paste End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "RNorfolk" wrote in message ... I have an example that best shows what I am trying to do. If you go to http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trouble Pasting
I am running Windows XP Pro with Service Pack 3 in a corporate setting. IE
7.0.5.... and Excel 2003 with service pack 3. It sure seems to me that 97 took a basic approach and pasted across the cells, 2007 uses html, text, and Unicode Text and 2003 cannot use the html, so only has the text and that fails for us. Not just one pc, multiple pcs. Thanks Roger -- Roger Norfolk Office 97 MOUS Master "Dave Peterson" wrote: Maybe it's how the browser puts the stuff in the clipboard. I tried copying entire rows using Firefox 3.0.11 and all the rows went into a single cell. I tried copying entire rows using MSIE 7.x and it pasted very nicely. What browser are you using? I used xl2003 under WinXP Home, too. Maybe those are important???? RNorfolk wrote: Thanks for the reply, Dave. I just tried a row as you had indicated, and it dropped all the numbers from the 7 columns on the website into the cell I pasted into the first, not across the cells. I was able to take this table and do a text to columns, but that didn't work for the table my user gets as the fields are vastly different widths, and not comma or other delimited. Thanks Roger -- Roger Norfolk Office 97 MOUS Master "Dave Peterson" wrote: I used xl2003 to test and I copied entire rows of that table and pasted into A1 of a new worksheet. And all the cells were properly populated--and so were the borders! If you close excel and reopen it, then try the copy|paste, do things work ok? Excel likes to help by remembering the choices you made from the last text to columns. If this doesn't help, maybe you could describe what happens in more detail. RNorfolk wrote: I have an example that best shows what I am trying to do. If you go to http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trouble Pasting
I don't have a guess.
It works perfectly for me in xl2003 (also sp3), MSIE 7.x, and win XP home. RNorfolk wrote: I am running Windows XP Pro with Service Pack 3 in a corporate setting. IE 7.0.5.... and Excel 2003 with service pack 3. It sure seems to me that 97 took a basic approach and pasted across the cells, 2007 uses html, text, and Unicode Text and 2003 cannot use the html, so only has the text and that fails for us. Not just one pc, multiple pcs. Thanks Roger -- Roger Norfolk Office 97 MOUS Master "Dave Peterson" wrote: Maybe it's how the browser puts the stuff in the clipboard. I tried copying entire rows using Firefox 3.0.11 and all the rows went into a single cell. I tried copying entire rows using MSIE 7.x and it pasted very nicely. What browser are you using? I used xl2003 under WinXP Home, too. Maybe those are important???? RNorfolk wrote: Thanks for the reply, Dave. I just tried a row as you had indicated, and it dropped all the numbers from the 7 columns on the website into the cell I pasted into the first, not across the cells. I was able to take this table and do a text to columns, but that didn't work for the table my user gets as the fields are vastly different widths, and not comma or other delimited. Thanks Roger -- Roger Norfolk Office 97 MOUS Master "Dave Peterson" wrote: I used xl2003 to test and I copied entire rows of that table and pasted into A1 of a new worksheet. And all the cells were properly populated--and so were the borders! If you close excel and reopen it, then try the copy|paste, do things work ok? Excel likes to help by remembering the choices you made from the last text to columns. If this doesn't help, maybe you could describe what happens in more detail. RNorfolk wrote: I have an example that best shows what I am trying to do. If you go to http://www.ssa.gov/OACT/STATS/table4c6.html and select part of the table and paste in Excel 2003 or 2007, the data does not go cleanly into the cells. If pasted into Excel 97, it goes into each cell so it could be manipulated. Any idea how to make 2003 paste into each cell? p.s. this is an example. The data I am trying to make work is of varying length fields that are not deliminated in any way when pasted. It does just the same though. -- Roger Norfolk Office 97 MOUS Master -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pasting a chart from Excel 2007 to Word 2007 trouble | Charts and Charting in Excel | |||
| TROUBLE COPYING AND PASTING FORMULAS | Excel Worksheet Functions | |||
| hairpulling-trouble with pasting a chart into word | Excel Discussion (Misc queries) | |||
| Trouble copying and pasting a formula | Excel Discussion (Misc queries) | |||
| Trouble copying and pasting a formula | Excel Worksheet Functions | |||
Linear Mode
