Solved

VBA:  Copying cells from an excel spreadsheet

Posted on 2000-05-11
9
658 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
I am using version 97. doh!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Sorry, couldn't find any thing :-(
Maybe other Expert knows...
0
 
LVL 22

Accepted Solution

by:
ture earned 150 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
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
Comment Utility
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 142

Expert Comment

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

Expert Comment

by:Buller
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now