mercredi 29 avril 2015

copying data from another workbook

i am trying to replace the current data in my file with the data in any another selected file which have same attributes. i want to replace the data from A1:Q in the current file from any other selected file. I tried writing the code but its showing errors .

Sub newdata()

Set appxl = CreateObject("Excel.application")
Dim myfile As Window
Dim currentSheet As Worksheet
Dim lastRow As Double
Dim sourceFileName As String

'Open Source File.xlsx
With appxl

    vFile = Application.GetOpenFilename(Title:="Select File To Be Opened")
    If vFile = False Then Exit Sub  'if the user didn't select a file, exit sub
   ' Set myfile = Workbooks.Open(vFile)

    .Visible = False
End With

'Get first sheet data
Set myfile = appxl.Windows(vFile)
Set currentSheet = myfile.Sheets(1)

'Past the table in my current Excel file
lastRow = currentSheet.Range("A1").End(xlDown).Row
Sheets("Data retrieval").Range("A1:E" & lastRow) = currentSheet.Range("A1:Q" & lastRow).Value

'Close Source File.xlsx

End Sub

Aucun commentaire:

Enregistrer un commentaire