Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

How to use PasteSpecial in vb between two excel files?

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
Narayanaswamy
Asked:
Narayanaswamy
  • 14
  • 11
  • 5
1 Solution
 
pg_indiaCommented:
try using the clipboard object of VB and the copy and paste it across ur excel sheets
0
 
_agj_Commented:
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
 
_agj_Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
_agj_Commented:
send in ur code plz.
0
 
NarayanaswamyAuthor Commented:
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
 
NarayanaswamyAuthor Commented:
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
 
pg_indiaCommented:
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
 
NarayanaswamyAuthor Commented:
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
 
NarayanaswamyAuthor Commented:
Hai
can you tell me..
In VB how to use the window() function?

Narayanaswamy

0
 
NarayanaswamyAuthor Commented:
sorry windows() function

Narayanaswamy
0
 
NarayanaswamyAuthor Commented:
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
 
pg_indiaCommented:
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
 
NarayanaswamyAuthor Commented:
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
 
pg_indiaCommented:
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
 
NarayanaswamyAuthor Commented:
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
 
pg_indiaCommented:
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
 
pg_indiaCommented:
is that the error message which you are saying related to embed??
0
 
NarayanaswamyAuthor Commented:
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
 
pg_indiaCommented:
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
 
NarayanaswamyAuthor Commented:
Hai
Then why =EMBED("Excel.Sheet.8","") appears in the formula bar... for me?!!


Narayanaswamy
0
 
pg_indiaCommented:
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
 
_agj_Commented:
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
 
NarayanaswamyAuthor Commented:
Ok
Thanks _agi_

How to give points to you?

Narayanaswamy
0
 
pg_indiaCommented:
Is your problem of embedded object solved??
0
 
NarayanaswamyAuthor Commented:
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
 
pg_indiaCommented:
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
 
_agj_Commented:
> Ok
> Thanks _agi_

> How to give points to you?

The intent is enuf. forget the pts. thanks :D
0
 
NarayanaswamyAuthor Commented:
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
 
pg_indiaCommented:
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
 
NarayanaswamyAuthor Commented:
Ok
I started searching for that

Narayanaswamy
0

Featured Post

Hire Technology Freelancers with Gigs

Work with 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.

  • 14
  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now