• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 701
  • Last Modified:

VBA: Copying cells from an excel spreadsheet

Hi, i need to know how to copy the data in cells as text and not with the extra formatting it usually copies as well.
Basically, what I need to do is the same as copying a range of cells and the selecting the paste special, and pasting as text.  Unfortunately, the other app, I am pasting into, does not have the paste special option....So I want to do something like a Copy special - Text only.  I would need this command to replace the standard <Ctrl-C> in my excel spreadsheet as well.
0
sbishop
Asked:
sbishop
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
In VB i would do Clipboard.SetText "Text" (ie Range.Text)
but i realized that in Excel (97) the Clipboard object is not available.
What Excel Version are you speaking of?

BTW, this question would have better fit in Office Section.
0
 
sbishopAuthor Commented:
I am using version 97. doh!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Sorry, couldn't find any thing :-(
Maybe other Expert knows...
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
tureCommented:
sbishop,

The DataObject will help you. To use that, you must add a reference to the Microsoft Forms 2.0 Object library (this is done automatically if you add a UserForm).

Perhaps you want to change
  t = t & Selection.Cells(r, c).Text
to
  t = t & Selection.Cells(r, c).Value

Sub CopyTextToClipBoard()
  Dim d As New DataObject
  Dim t As String
  Dim r As Long
  Dim c As Long
 
  If TypeOf Selection Is Range Then
    If Selection.Areas.Count = 1 Then
      For r = 1 To Selection.Rows.Count
        For c = 1 To Selection.Columns.Count
          t = t & Selection.Cells(r, c).Text
          If c <> Selection.Columns.Count Then t = t & vbTab
        Next c
        If r <> Selection.Rows.Count Then t = t & vbCrLf
      Next r
      d.SetText t
      d.PutInClipboard
    End If
  End If
End Sub

Ture Magnusson
Karlstad, Sweden
0
 
calacucciaCommented:
Another method would be to pcreate a combined command, which pastes text only into a temporary Range, and then copies the text without formatting now. The temporary range is put in the personal.xls file, which normally resides in your C:\Program Files\Microsoft Office\Office\XLStart\ directory, and runs hidden on the background. If you don't have it, just create a new blank file, name it personal.xls, and hide it (Windows/Hide).
Save all changes, close Excel, and answer yes to the question if Personal.xls should be saved.

The macro can also be put in the personal.xls file.

To assign a shortcut to it, hit Alt+F8, look for CopySpecial in the macro list, select it, click options and assign a shortcut key in the appropriate box.

Sub CopySpecial()
Dim mRow As Long, mCol As Long
mRow = Selection.Rows.Count
mCol = Selection.Columns.Count
Selection.Copy
Workbooks("Personal.xls").Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
Workbooks("Personal.xls").Sheets("Sheet1").Cells(mRow, mCol).Copy
End Sub
 
Hope this helps
Calacuccia
0
 
BullerCommented:
You don't specify which other app you use to paste the cells into. But you say that you need this function in your excel-spreadsheets as well.

This code works with Excel, as a VBA-module. I guess you could easily generalize it to be used across different apps. In order to make the code worjk, paste it into a module in a workbook. Set the macro-options to make the two procedures use whatever shortcut-key you want to. In any worksheet in the workbook, select a range and press the shortcut-key for copy_range. Next choose another worksheet in the workbook, choose where you want to paste and press the shortcut-key for paste-range. That's it. It could probably be done in a more sophisticated way - but it works.

Option Explicit
Private arrCopy
Private cellstart As String
Private cellslut As String
Private intRow As Integer
Private intCol As Integer
   
Public Sub copy_range()
    cellstart = ActiveCell.Address
    cellslut = ActiveWindow.RangeSelection.End(xlDown).End(xlToRight).Address
    ReDim arrCopy((Range(cellslut).Column), (Range(cellslut).Row))
   
    For intRow = Range(cellstart).Row To Range(cellslut).Row
        For intCol = Range(cellstart).Column To Range(cellslut).Column
            arrCopy(intCol, intRow) = "'" & Cells(intRow, intCol)
        Next
    Next

End Sub

Public Sub paste_range()
    Dim newstart As String
    newstart = ActiveCell.Address
    For intRow = Range(cellstart).Row To Range(cellslut).Row
        For intCol = Range(cellstart).Column To Range(cellslut).Column
            ActiveCell = arrCopy(intCol, intRow)
            Cells(ActiveCell.Row, ActiveCell.Column + 1).Select
        Next
        Cells(ActiveCell.Row + 1, Range(newstart).Column).Select
    Next
End Sub
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Buller, your code is very nice, but the data is unfortunately not on the clipboard...
0
 
BullerCommented:
OK. Then try this workabout which uses a txt-file and a VB-exe to carry the copying from Execl to something else.

First, make this procedure as VBA in your workbook:

Public Sub copy_range()
    cellstart = ActiveCell.Address
    Dim intstart As Byte
    intstart = InStr(1, ActiveWindow.RangeSelection.Address, ":", vbTextCompare)
    cellslut = Mid(ActiveWindow.RangeSelection.Address, intstart + 1)
    ReDim arrCopy((Range(cellslut).Column), (Range(cellslut).Row))
    For intRow = Range(cellstart).Row To Range(cellslut).Row
        For intCol = Range(cellstart).Column To Range(cellslut).Column
            arrCopy(intCol, intRow) = Cells(intRow, intCol)
        Next
    Next
End Sub

This would account for the copying of your cells. Then send the copy to a text-file:

Public Sub paste_range()
    Dim newstart As String
    Dim strclip As String
    newstart = ActiveCell.Address
    Dim filnummer As Integer
    Dim filnavn As String
    filnummer = FreeFile
    filnavn = "c:\copy.txt"
    Open filnavn For Output As #filnummer
    For intRow = Range(cellstart).Row To Range(cellslut).Row
        For intCol = Range(cellstart).Column To Range(cellslut).Column
        strclip = strclip & arrCopy(intCol, intRow) & ";"
        Next
        Print #filnummer, strclip
        strclip = vbNullString
    Next
    Close #filnummer
    Shell "c:\Testpaste.exe", vbHide
 End Sub

And then run the VB-exe (Testpaste.exe), which consists of only one procedure (on load):

Private Sub Form_Load()
    Dim strclip As String
    Dim strclipx As String
    Dim filnummer As Integer
    Dim filnavn As String
    filnummer = FreeFile
    filnavn = "c:\copy.txt"
    Open filnavn For Input As #filnummer
    Do
        Line Input #filnummer, strclipx
        strclip = strclip & CStr(strclipx) & vbCrLf
    Loop Until EOF(filnummer)
    Dim d As DataObject
    Close #filnummer
    Clipboard.Clear
    Clipboard.SetText strclip
    Shell "c:\windows\notepad.exe", vbNormalFocus
    Unload Me
End Sub

As you can see, the exe closes down after sending the contents of the text-file to the clipboard, and then opens Notepad (but it could be any other app). All you need to do now is to paste the content of the clipboard into the Notepad (or whatever).
0
 
sbishopAuthor Commented:
Thanks very much for all of the replies.  Very much appreciated, however Ture's answer seemed to be the best option, due to the fact that it doesn't use any intermediate storage mechanism(like a file(text or xls), or a worksheet.)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now