Pasting data from clipboard between workbooks 1

[mrfitness]

thread707-1380005

I used the code in the above thread and it gives me a message box showing the data stored in memory. However, I want to paste the data and I am having problems.
I tried using ActiveCell.Value = MyData.GetText(1) as well as ActiveCell.Value = Selection but I am getting all the data into one cell with ctrl breaks instead of pasting the data in multiple cells

Please help!

Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function CloseClipboard Lib "user32" () As Long

Sub Clear_Clipboard()
   OpenClipboard (0)
   EmptyClipboard
   CloseClipboard
End Sub

Sub myFunc()
   Set MyData = New DataObject
   MyData.GetFromClipboard
   
   On Error GoTo ERRHANDLER
   
   strClip = MyData.GetText(1)
   MyData.SetText strClip
   MyData.PutInClipboard
   
   MsgBox MyData.GetText
   
'Cpy.GetFromClipboard

    Selection = MyData.GetText(1)

'this is where I want to paste the data, but not getting the result
   ActiveCell.Value = Selection
   

    Application.CutCopyMode = False
   
   Exit Sub
ERRHANDLER:
   MsgBox "Empty Clipboard"
End Sub

 

[SkipVought]

Hi,

You WILL have this problem is you have TWO SEPARATE INSTANCES OF EXCEL open.

Open all workbooks that you want to copy data between in THE SAME INSTANCE OF EXCEL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrfitness]

I unfortunately cannot keep this in the same excel book....
This is for a program I am developing where users are to copy data from one excel book and import it into my application.

Because the users may paste one column at a time, or for the most part only a part of their data file is needed, I cannot do an import function from another file as there are too many variables with their files (different headings, columns, etc) that I would have no clue where to pull the data from in their files.

The data actually shows up from one workbook to this one. If you paste the code above in a new workbook, then open up a 2nd excel workbook, type in random data into cells, copy, then select your first excel book (with code) and paste. You will see the msgbox actually shows the data and the paste puts it all into one cell

 

[SkipVought]

unfortunately cannot keep this in the same excel book....

I did NOT say the SAME WORKBOOK!!!!!!

The SAME INSTANCE OF THE APPLICATION.

Please read carefully!!!

Here's where you go wrong.
[tt]
1. Click on the EXCEL icon (opens a NEW INSTANCE of Excel w/ empty workbook)

2. Click on the EXCEL icon (opens a NEW INSTANCE of Excel w/ empty workbook)
[/tt]
Now you have TWO TOTALLY SEPARATE instances of the Excel APPLICATION. You cannot copy between them.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrfitness]

Wow sorry for offending you...that is what I meant. Not sure if you noticed but I am a new user, please don't yell at me

I did step 1, and put my above code in a module
I did step 2, typed in random data into cells, copied them, and clicked on the first INSTANCE of excel (ie workbook) and pasted. The message box came up showing the data copied from the 2nd instance, and then the data was pasted into one cell.

Can you please follow those steps and see if you can help me? If the data in the 2nd instance of Excel goes into a message box in the 1st instance of Excel, there has to be a way to split the data that gets pasted into one cell with control breaks.

Thank you

 

[SkipVought]

How about something like this...

Sub CopyFunc()
    Selection.Copy
    Workbooks("OtherWorkbook.xls").Worksheets("OtherSheet").Range("OtherRange").PasteSpecial xlPasteAll
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrfitness]

I see where you are going with this, but let me explain a bit more about the application.

It is a shared book on the server used by over 200 Sales reps.
At one point we ask them to enter client shipment information.

They open up an excel book that the client sent them (therefore, a second instance of excel), then they rearrange data to match the columns requested in my application, then they copy the data and paste it into my application.

Problem is, sometimes they get more than 1 file so they are pasting data more than once, and it can be in any location (I don't want to restrict data entering in certain spots), any sometimes they want to paste different columns at a time.

Is there any way you can help me code how to break up data in one cell that is separated with Chr(10) and Alt+Enters?
(there is actually the square boxes in between columns and rows)

col1r1	col2r1	col3r1  col4r1

col1r2	col2r2	col3r2  col4r2

col1r3	col2r3	col3r3  col4r3

[\code]

 

[PHV]

Have a look at the TextToColumns method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrfitness]

If I use the text to columns function when the original cell has more then one line (lines are separated with Alt+Enter), Excel does only notice the first line.

 

[SkipVought]

As far as I know, this can only happen if you EDIT the cell and paste into the cell.

However, if you SELECT the TOP-LEFT cell (very different than EDIT the cell) and paste, the copies cells will be pasted into cells relative to to the selected cell.

For instance if you COPY B2:C3

and then SELECT the other sheet cell Z5,

the result will be pasted into Z5:AA6

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrfitness]

Not sure what you mean with Editing the cell....were you answering me or PHV?
I want the data to be pasted from wherever they click as a starting point, moving over the same number of columns and rows as the data they are copying from.
Right now it is only pasting into one cell, and text to columns only works for the first row of data.

 

[SkipVought]

When you SELECT a cell,
[tt]
1. your cursor is NOT VISIBLE.
2. the Formula Bar displays only the INSERT FUNCTION symbol
[/tt]

When you EDIT a cell,
[tt]
1. your cursor is INSIDE the cell BLINKING.
2. the Formula Bar displays a CANCEL symbol & ENTER symbol, next to the INSERT FUNCTION symbol
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrfitness]

ok...can someone please paste the following code into a module and run it to see what I am working with?

I want to split out this data into rows and columns (anywhere from 1 to 4 columns, up to 50,000 rows)

Sub pastedvalex()
ActiveCell.Value = "col1row1" & Chr(1) & "col2row1" & Chr(1) & "col3row1" & Chr(1) & "col4row1" & Chr(1) & Chr(44) & Chr(10) & _
                    "col1row2" & Chr(1) & "col2row2" & Chr(1) & "col3row2" & Chr(1) & "col4row2" & Chr(1) & Chr(44) & Chr(10) & _
                    "col1row3" & Chr(1) & "col2row3" & Chr(1) & "col3row3" & Chr(1) & "col4row3" & Chr(1) & Chr(44) & Chr(10)
End Sub

Thanks in advance!

 

[SkipVought]

Now you have a DIFFERENT issue. This is not COPY and PASTE!

Was this the real issue all along?

Of course! everything is in ONE CELL!

Use Text to columns to parse data ON THE SAME ROW.

But you seem to have MULTIPLE ROWS assigned to this cell as well, not by COPY & PASTE, unless you EDIT the cell before pasting.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrfitness]

I gave that code to show you the result of the original code in my first post (I guess you did not run it?)

The result is a duplication of the code I pasted just recently, and as explained before I have multiple rows so text to columns will not work

 

[SkipVought]

and you have never answered the EDIT question.

Are you ABSOLUTELY SURE that you are NOT editing the cell when the PASTE occurs?

I can ONLY duplicate this situation, when I EDIT a cell before pasting.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrfitness]

Sorry, but again this is the code used to paste the data in (I have this being called when a user hits Ctrl+v)
I wouldn't see how the EDIT is being used?

Sub myFunc()
   Set MyData = New DataObject
   MyData.GetFromClipboard
   
   On Error GoTo ERRHANDLER
   
   strClip = MyData.GetText(1)
   MyData.SetText strClip
   MyData.PutInClipboard
   
   MsgBox MyData.GetText
   
'Cpy.GetFromClipboard

    Selection = MyData.GetText(1)
    activecell.value = selection   

    Application.CutCopyMode = False
   
   Exit Sub
ERRHANDLER:
   MsgBox "Empty Clipboard"
End Sub

 

[SkipVought]

Just REMOVE your MyFunc macro from ctr+v and allow NATIVE EXCEL PASTE to function as designed!

You're shooting yourself in the foot!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrfitness]

But I only want values pasted, not formats
My application runs other macros upon workbook activate/deactivate, when I run a simple Ctrl+v the format of the cells from the other book (including locked cell by default) goes into my application which sheet is protected. Therefore a user cannot copy over existing data as the cells are locked. Trust me this is the only way to do pasting of values alone

 

[SkipVought]

But I only want values pasted, not formats

The Edit > Paste Special -- VALUES

Native Excel Functionality!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}