Solved

VBA:  Copying cells from an excel spreadsheet

Posted on 2000-05-11
9
675 Views
Last Modified: 2008-02-26
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
Comment
Question by:sbishop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2803406
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
 

Author Comment

by:sbishop
ID: 2803409
I am using version 97. doh!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2803419
Sorry, couldn't find any thing :-(
Maybe other Expert knows...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Accepted Solution

by:
ture earned 150 total points
ID: 2803731
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 2803978
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
 

Expert Comment

by:Buller
ID: 2805074
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2805092
Buller, your code is very nice, but the data is unfortunately not on the clipboard...
0
 

Expert Comment

by:Buller
ID: 2807199
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
 

Author Comment

by:sbishop
ID: 2809035
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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question