Solved

VBA:  Copying cells from an excel spreadsheet

Posted on 2000-05-11
9
667 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]
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2803419
Sorry, couldn't find any thing :-(
Maybe other Expert knows...
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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 142

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB 6.0 printer how to align 6 62
Best way to parse out a json string in VB6? 10 163
passing parameter in sql procedure 9 61
VB6 - Scroll Mouse wheel on Picturebox 13 43
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

786 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