VBscript command to open a excel and then perform paste function

Question

All,

I have created a macro, which is working fine when both source and destination excel's is open. I would like to get a  VBscript command to open a excel and perform paste function.

please find below code to copy from info.xls and paste on company.xls. In which line, i should append a VBscript to open a company.xls if it is closed and then perform paste function...

Sub Macro1()

'

' Macro1 Macro

' Macro1

'

'

Range("A2:C7").Select

Selection.Copy

Windows("Company.xls").Activate

Range("A2:C7").Select

ActiveSheet.Paste

Application.CutCopyMode = False

ActiveWorkbook.Save

ActiveWorkbook.Save

Windows("Info.xls").Activate

ActiveWorkbook.Save

End Sub

Answer 1

See http://answers.microsoft.com/en-us/office/forum/office_2007-excel/copying-from-an-excel-to-other-excel-with-multiple/a197aef1-17e3-4540-9cc6-a77b764d8727

Answer 2

Hi,

Thanks for your help...

would you please help with two more requirements...

1. In the first workbook(info.xls), i have columns like a,b,c,d,e,f in worksheets(1) and in second workbook(Company.xls) , i have columns a,b,c,d,e,f,g,h,i,j.k  Now i have to copy+paste values from info.xls to company.xls in below pattern:

a to a (a ->a), b ->b, c -> g, d -> i, e -> j, f -> k

2. instead of hard code path (C:\MyFiles\Company.xls) , is there possible to set a general path like (%\Company.xls).
3. Also, i can ask a user to provide destination workbook path(company.xls) in column and can i use that value within the macro code to open the destination .xls and then perform a paste operations.

thanks in advance,

arun

Answer 3

Try this:

Sub Macro1()

    Dim wbkS As Workbook

    Dim wbkT As Workbook

    Set wbkS = ActiveWorkbook

    On Error Resume Next

    Set wbkT = Workbooks("Company.xls")

    On Error GoTo 0

    If wbkT Is Nothing Then

        ' Modify path as needed

        Set wbkT = Workbooks.Open("C:\MyFiles\Company.xls")

    End If

    wbkS.Worksheets(1).Range("A2:C7").Copy _

        Destination:=wbkT.Worksheets(1).Range("A2")

    Application.CutCopyMode = False

    wbkT.Save

    wbkS.Activate

End Sub

The code works with the first worksheet in both workbooks. You can change this if needed.