Trouble Pasting

[RNorfolk]

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.

 

[Don Guillett]

I went to that site>copied all of the table>pasted 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

 

[Dave Peterson]

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]

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

I went to that site>copied all of the table>pasted 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]

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
(e-mail address removed)

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

 

[RNorfolk]

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

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]

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

I went to that site>copied all of the table>pasted 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

 

[Dave Peterson]

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

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

 

[RNorfolk]

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

 

[Dave Peterson]

I don't have a guess.

It works perfectly for me in xl2003 (also sp3), MSIE 7.x, and win XP home.

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