CODEWITHSUNDEEP
vbaexcelcopy
Paolo Caponeri
Paolo Caponeri

Reputation: 127

Copy range from workbook to another without open

I'm trying to copy a range of values from a workbook to another by using a dialog box to select the file I'd like to copy the data from.

I'd like also not to open the "copyFrom" workbook in the process. The problem lies in passing the file path in the string "filename" to the workbook variable "copyFrom".

Option Explicit

Private Sub butt_copy_Click()

    Call copy1

End Sub

Private Sub copy1()

    Dim fileName As String, copyFrom As Workbook

    fileName = Application.GetOpenFilename()

    copyFrom = filename  

    Sheet1.Range("A1:A20") = copyFrom.Sheets(Sheet1).Range("A1:A20")


End Sub

Upvotes: 0

Views: 9583

Answers (2)

APW
APW

Reputation: 369

Just a comment to add to the algorithm; It opens the file and reads from it, but does not close. This makes it "READ_ONLY" for all other users!

I suggest that at the end of the code: add this for saving original file unchanged.

Application.DisplayAlerts = False
app.Workbooks.Close savechanges:=False
Application.DisplayAlerts = True

Upvotes: 2

Siva
Siva

Reputation: 1149

Try Below. But i have some Clarification like where will you run the code from 

Application.Visible=False
Set copyFrom = Application.Workbooks.Open(fileName)

Then Try below. Worked Perfectly for me

Private Sub copy1()
 Dim app As New Excel.Application
 Dim fileName As String, copyFrom As Workbook
 app.Visible = False
 fileName = app.GetOpenFilename()

 Set copyFrom = app.Workbooks.Open(fileName)
  MsgBox copyFrom.Sheets(1).Cells(1, 1)
 'Sheet1.Range("A1:A20") = copyFrom.Sheets(Sheet1).Range("A1:A20")


End Sub

Upvotes: 1

Related Questions