Solved

How to use PasteSpecial in vb between two excel files?

Posted on 2003-10-26
30
780 Views
Last Modified: 2012-05-04
Hai all,
I want to copy a row from one excel file and want to paste in another excel file as a column.

The following code works fine if the target is the same excel file. but if it is a new excel file this it shows error in VB?

PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=True


Can any one tell me how can i copy a row from one excel file and paste in another as a column from VB?

Thanks
Narayanaswamy

0
Comment
Question by:Narayanaswamy
  • 14
  • 11
  • 5
30 Comments
 
LVL 3

Expert Comment

by:pg_india
ID: 9625313
try using the clipboard object of VB and the copy and paste it across ur excel sheets
0
 
LVL 7

Expert Comment

by:_agj_
ID: 9625365
this is a macro recording for a paste special operation:

Sub Macro1()
'
'
    Range("B7:D11").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Range("C12").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=False
End Sub

THis copies from one workbook to another and does a paste special.
0
 
LVL 7

Expert Comment

by:_agj_
ID: 9625374
to copy as a row and paste as a column do this:

Sub Macro2()

    Range("A3:D3").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Range("B8").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=True
End Sub

the row to column change is thanks to the transpose property.
0
 
LVL 7

Expert Comment

by:_agj_
ID: 9625379
send in ur code plz.
0
 

Author Comment

by:Narayanaswamy
ID: 9625511
Hai _agi_
I have two excel files pointed by
oSource
oTarget
for the RowNo = 1 in the oSource excel file my values are like
one row data
test      a      b      c      d
Now I want the oTarget sheet data as
test
a
b
c
d

My VB code is
    sourceRange = "A1:E1"
    oSource.Sheets(1).Range(sourceRange).Copy
    oTarget.Sheets(1).Range("A1").PasteSpecial Paste = xlAll, Operation = xlNone, SkipBlanks = False, Transpose = True

This code produces an error that PasteSpecial of Range method fails

Now if i give as
    sourceRange = "A1:E1"
    oSource.Sheets(1).Range(sourceRange).Copy
    oSource.Sheets(2).Range("A1").PasteSpecial Paste = xlAll,
Operation = xlNone, SkipBlanks = False, Transpose = True

Then i get the result in sheet2
How to get the result in sheet1 of oTarget
Can you help me in this

Narayanaswamy
0
 

Author Comment

by:Narayanaswamy
ID: 9625526
Hai ,
       I found a different paste special dialogbox appears when i try to do the same mannually?  
  I copy the values using ^C  and went to the sheet2 of same book then i get the PasteSpecial dialog box with the Transpose option. When I go to a new sheet and try for pastespecial ,then a different dialog box appears ( In that there is no option for Transpose)  ?!

Narayanaswamy

0
 
LVL 3

Expert Comment

by:pg_india
ID: 9625527
try this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/10/2003 by Mohanraj
'

'
    Range("A1:A4").Select
    Selection.Copy
    Windows("Book2").Activate
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

here book2 is the name of target excel file........
0
 

Author Comment

by:Narayanaswamy
ID: 9625553
Thanks Pg_india,
 This works in Excel Hope this will work in VB

   Windows("Book1").Activate
   Range("A1:E1").Select
    Selection.Copy
    Windows("Book2").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

I will now try that in VB

Narayanaswamy
0
 

Author Comment

by:Narayanaswamy
ID: 9625568
Hai
can you tell me..
In VB how to use the window() function?

Narayanaswamy

0
 

Author Comment

by:Narayanaswamy
ID: 9625574
sorry windows() function

Narayanaswamy
0
 

Author Comment

by:Narayanaswamy
ID: 9625695
I used the following code....

oSource.Windows(oBookSource.Name).Activate
oBookSource.Sheets(SheetNo).Range("A1:E1").Copy
oTarget.Windows(oBookTarget.Name).Activate
oBookTarget.Sheets(SheetNo).Range("a1").PasteSpecial Paste = xlAll, Operation = xlNone,SkipBlanks = False, Transpose = True

This says the error
 PasteSpecial method of Range class fails

I tried to use
ActiveWindow.ActivateNext as per _agi_ 's comment but this works in Excel if the active sheet is book1 else it pastes blanks and also I don't know how to use that in VB can you tell me how to use that from VB?

Thanks
Narayanaswamy
0
 
LVL 3

Accepted Solution

by:
pg_india earned 50 total points
ID: 9625748
Dim wka As Excel.Application
Dim wkbk1 As Excel.Workbook
Dim wkbk2 As Excel.Workbook
Dim wksh1 As Excel.Worksheet
Dim wksh2 As Excel.Worksheet

Private Sub Command1_Click()
Set wka = New Excel.Application
wka.Visible = True
Set wkbk1 = wka.Workbooks.Add("c:\1.xls")
Set wksh1 = wkbk1.ActiveSheet
Set wkbk2 = wka.Workbooks.Add("c:\2.xls")
Set wksh2 = wkbk2.ActiveSheet
wksh1.Range("a1:a5").Copy
wksh2.Range("a1").PasteSpecial xlPasteValues
End Sub

I did this and it is working fine...where r u getting the error
0
 

Author Comment

by:Narayanaswamy
ID: 9625797
Dim oSource As Object
Dim oBookSource As Object
Dim oTarget As Object
Dim oBookTarget As Object

Set oSource = CreateObject("Excel.Application")
Set oTarget = CreateObject("Excel.Application")

Set oBookSource = oSource.Workbooks.Open("c:\1.xls")
Set oBookTarget = oTarget.Workbooks.Add

oSource.Windows(oBookSource.Name).Activate
oBookSource.Sheets(1).Range("A1:E1").Copy
oTarget.Windows(oBookTarget.Name).Activate
oBookTarget.Sheets(1).Range("a1").PasteSpecial Paste = xlAll, Operation = xlNone, SkipBlanks = False, Transpose = True

I am getting the error in the last line

Narayanaswamy
0
 
LVL 3

Expert Comment

by:pg_india
ID: 9626050
Dim oSource As Object
Dim oBookSource As Object
Dim oTarget As Object
Dim oBookTarget As Object

Set oSource = CreateObject("Excel.Application")
Set oTarget = CreateObject("Excel.Application")

Set oBookSource = oSource.Workbooks.Open("c:\1.xls")
Set oBookTarget = oTarget.Workbooks.Add("c:\2.xls")
oSource.Visible = True
oTarget.Visible = True
oSource.Windows(oBookSource.Name).Activate
oBookSource.Sheets(1).Range("A1:a10").Copy
oTarget.Windows(oBookTarget.Name).Activate
oBookTarget.Sheets(1).Range("a1").PasteSpecial xlPasteValues


try this code..no error 2.xls is another excel file where it will paste the data
0
 

Author Comment

by:Narayanaswamy
ID: 9626169
Yes this code works but the column is not pasted
Insted it stays as
=EMBED("Excel.Sheet.8","")

But I used your above code  but it requires reference. If i use a reference then how to use the same code in another version of Excel?

Narayanaswamy

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Expert Comment

by:pg_india
ID: 9626344
No man i have tried this code and it is pasting into the new exel sheet  ..which version of ofice ur using??

Then without reference how are you accessing the worksheet...is that possible i don't know!!!!

When u use createobject("Excel.application") it wil work for all versions of excel, only the function which you are using should be supported by that particular version of excel.

0
 
LVL 3

Expert Comment

by:pg_india
ID: 9626348
is that the error message which you are saying related to embed??
0
 

Author Comment

by:Narayanaswamy
ID: 9626386
No it is not showing any errors.

But the data pasted is seperate from the excel sheet
(it is like a grid on the excel sheet)

Then sorry i used reference in this case also..

The data that is copied is not embed with the excel sheet and the formula =EMBED("Excel.Sheet.8","") appears in the formula bar...

I am using office XP

can you tell me the difference between using
Dim oSource As Object
Set oSource = CreateObject("Excel.Application")
and
Dim wka As Excel.Application
Set wka = New Excel.Application

I will any how accept your comment as my answer since it solved my problem but i am not getting what is the difference?


Narayanaswamy

0
 
LVL 3

Expert Comment

by:pg_india
ID: 9626936
Thanks for the points....

Dim oSource As Object
Set oSource = CreateObject("Excel.Application")
and
Dim wka As Excel.Application
Set wka = New Excel.Application

I don't think there is any difference....))))))))))

i used both and its working same way...and i saw the formula bar also and it was showing the real value only..i have office 2000...

0
 

Author Comment

by:Narayanaswamy
ID: 9631591
Hai
Then why =EMBED("Excel.Sheet.8","") appears in the formula bar... for me?!!


Narayanaswamy
0
 
LVL 3

Expert Comment

by:pg_india
ID: 9631748
oBookTarget.Sheets(1).Range("a1").PasteSpecial
instead of this use the
oBookTarget.Sheets(1).Range("a1").PasteSpecial xlPasteValues

I omitted that value and it was displaying in my excel also as embeded. The reason is that excel does not know what to do in default case(all paste) so it takes it as  aembedded object and just paste it.

Hope that answers your question...

Any extra points for that.))))))))))))
0
 
LVL 7

Expert Comment

by:_agj_
ID: 9632251
the difference is:

createobject() does what is called late binding. As in,  u dont specify a reference to the library etc, previously.
At runtime, it instantiates the object and goes ahead.
The problem/difficulty with this is that one cant use the auto-complete feature of vb.

Th other mtd (=new excel.application) is called early binding. In this a reference is required. And the library files must be present rite then. Its not like it accesses the library at runtime.
Here on can use the autocomplete feature.
0
 

Author Comment

by:Narayanaswamy
ID: 9632267
Ok
Thanks _agi_

How to give points to you?

Narayanaswamy
0
 
LVL 3

Expert Comment

by:pg_india
ID: 9632607
Is your problem of embedded object solved??
0
 

Author Comment

by:Narayanaswamy
ID: 9632675
I am confused whth the statements

Dim oSource As Object
Set oSource = CreateObject("Excel.Application")
and
Dim wka As Excel.Application
Set wka = New Excel.Application

Though _agi_ told that they are same and one is runtime binding and other is compile time I am not getting the proper reason....?!

 Embeed is not coming  is if i use

Dim wka As Excel.Application
Dim wkbk1 As Excel.Workbook
Dim wkbk2 As Excel.Workbook
Dim wksh1 As Excel.Worksheet
Dim wksh2 As Excel.Worksheet

Set wka = New Excel.Application
wka.Visible = True
Set wkbk1 = wka.Workbooks.Add("c:\1.xls")
Set wksh1 = wkbk1.ActiveSheet
Set wkbk2 = wka.Workbooks.Add("c:\2.xls")
Set wksh2 = wkbk2.ActiveSheet
wksh1.Range("a1:a10").Copy
wksh2.Range("a1").PasteSpecial xlPasteValues


If i use

Dim oSource As Object
Dim oBookSource As Object
Dim oTarget As Object
Dim oBookTarget As Object

Set oSource = CreateObject("Excel.Application")
Set oTarget = CreateObject("Excel.Application")

Set oBookSource = oSource.Workbooks.Open("c:\1.xls")
Set oBookTarget = oTarget.Workbooks.Add("c:\2.xls")
oSource.Visible = True
oTarget.Visible = True
oSource.Windows(oBookSource.Name).Activate
oBookSource.Sheets(1).Range("A1:a10").Copy
oTarget.Windows(oBookTarget.Name).Activate
oBookTarget.Sheets(1).Range("a1").PasteSpecial xlPasteValues
oTarget.save
oTarget.quit
oSource.quit

Embeed is coming

I used the above codeing....... and so embeed is not coming now?

How can i send the  file  that contains embeed in the formulabar...?

Can i get your mail Id
My mail Id is
nanaswamy_maths@hotmail.com

Thanks
Narayanaswamy
0
 
LVL 3

Expert Comment

by:pg_india
ID: 9632845
I have added you in hotmail messenger.
i am also getting the embed error if i am using the paste statement without the "xlPasteValues" value

My id is pg_india@hotmail.com
0
 
LVL 7

Expert Comment

by:_agj_
ID: 9632907
> Ok
> Thanks _agi_

> How to give points to you?

The intent is enuf. forget the pts. thanks :D
0
 

Author Comment

by:Narayanaswamy
ID: 9632947
Hai
When i triedthe same code once again i found one different thing.

The =Embeed is not coming in the same code now but , It says that Resume.xlw is already exists in that location.
I get confused with this....  

Can you tell me about Resume.xlw  Is it related to =Embeed problem.

where to search for these kind of errors....?

Narayanaswamy
0
 
LVL 3

Expert Comment

by:pg_india
ID: 9632964
may be because you are trying to save the file with same name..
no idea where to search for error
try msdn.microsoft.com site...

Doesn't msdn have these errors descriptions and help on these??
0
 

Author Comment

by:Narayanaswamy
ID: 9633019
Ok
I started searching for that

Narayanaswamy
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

708 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

19 Experts available now in Live!

Get 1:1 Help Now