-1

I'm trying to get a sheet from a Workbook which is a subfolder and import it in my current Workbook, where I write my macro. The path and file would look like below

strPath = Application.ActiveWorkbook.Path & "\Subfolder"
strFile = "myExcel.xlsx"

with a workbook called myWorkbook. How do I import it and do I need an empty sheet in my current workbook?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Robin
  • 303
  • 1
  • 4
  • 16
  • 2
    What have you tried? What problems are you encountering? What about this question is anything other than a request for some volunteer to write your code for you? Note: "Write my code for me" isn't a _specific programming problem_, see [this help page](http:\\stackoverflow.com\help\how-to-ask) for some helpful guideliens on writing a question for SO. – Aiken Mar 20 '15 at 08:21
  • 1
    My comment would have been the same if you were a 100k+ rep user who'd been using SO for years. Posts are judged by their content, not their author. You can edit your question using the link below the question body if you have some code and a specific problem with it that we can help you solve. – Aiken Mar 20 '15 at 08:28

1 Answers1

0

You'll need to open the source workbook, then copy the sheet and then close the workbook. You don't need to have an empty sheet in your current workbook (but you must have at least 1 sheet). Something like below will work:

Dim strPath As String, strFile As String    

strPath = Thisworkbook.Path & "\Subfolder\" ' take note of the extra "\"
strFile = "myExcel.xlsx"

Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = Workbooks.Open(strPath & strFile)

wb.Sheets("SheetName").Copy Thisworkbook.Sheets(1) ' copies before the first sheet
wb.Close False ' close without saving
Application.ScreenUpdating = True
L42
  • 19,427
  • 11
  • 44
  • 68
  • Thanks so far! But the line `wb.Sheets("SheetName").Copy Thisworkbook.Sheets(1)` is opening another macro. Can you explain the line, exspecially for what the 1 is? How is it possible to copy it as last sheet? – Robin Mar 20 '15 at 10:38
  • @RobinDrake What do you mean opening another macro? Btw, to copy it after the last cell see this [post](https://stackoverflow.com/questions/20697706/how-to-add-a-named-sheet-at-the-end-of-all-excel-sheets/20697790#20697790). Copying and Adding sheet have the same after and before argument. So code is the same. – L42 Mar 21 '15 at 05:29