Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA to copy range from closed workbook to active workbook

Posted on 2016-07-21
2
Medium Priority
?
104 Views
Last Modified: 2016-07-27
Looking to use VBA to copy range columns A:W from a closed workbook in worksheet "Worksheet1" to my active workbook, worksheet "Worksheet1" range  columns A:W

The source workbook has a different name each time it is looked for, as it is renamed after the last update.
0
Comment
Question by:DougDodge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41723194
You may try something like this....
The code will prompt you to select the source file.

Sub CopyFromAnotherWorkbook()
Dim swb As Workbook, wb As Workbook
Dim sws As Worksheet, ws As Worksheet

Dim SelectedFile As String

Application.ScreenUpdating = False
Set swb = ThisWorkbook
Set sws = swb.Sheets("Sheet1")
sws.Range("A:W").ClearContents

With Application.FileDialog(msoFileDialogFilePicker)
   .Title = "Select A Folder!"
   .ButtonName = "Confirm"
   .Filters.Clear
   .Filters.Add "Select an Excel File to open", "*.xlsx"
   If .Show = -1 Then
      SelectedFile = .SelectedItems(1)
   Else
      MsgBox "You didn't select a file.", vbExclamation, "File Not Selected!"
      Exit Sub
   End If
End With
Workbooks.Open SelectedFile
Set wb = ActiveWorkbook
Set ws = wb.Sheets(1)
ws.Range("A:W").Copy sws.Range("A1")
wb.Close False
Application.ScreenUpdating = True
MsgBox "Data has been successfully copied.", vbInformation, "Done!"
End Sub

Open in new window

0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41723442
If you don't want to open the workbook there's some code eamples using ADO here
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

671 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