Import worksheet from another workbook

[MrWhiskey]

Hi,

I am trying to import a worksheet from another workbook into the active workbook. The imported sheet comes from a workbook who's name always changes but will be in the same path. The sheet I want to be imported is called "Market Share adj" Thanks for your help!

Please Login or Register  to view this content.

[Mumps1]

Since the source file name always changes, it might be best to allow you to choose the file. Place this macro in the active workbook and run it from there.

Please Login or Register  to view this content.

[MrWhiskey]

Thank you for your response. It works exactly as you said.

Is it possible to have it automatically choose the file that is in the folder and import it? There will always be only 1 macro enabled workbook in that file but the name can change. This is also an intermediate step in a macro I am trying to build so I would like it to all be automated.

[Mumps1]

What is the full path of the folder containing the source file?

[MrWhiskey]

The folder will always be called 48m Macro and it is on a shared network. The path could change depending on what projects are being worked on or if a new project starts. The original code is able to open the file I want but I don't know how to copy the sheet. I am trying to reengineer this macro and build a new macro at the bottom of it.

Please Login or Register  to view this content.

[CK76]

Since you have Excel 2016. I'd recommend using PowerQuery/Get&Transform. It's pretty easy to select only .xlsm in the folder.

Basic process:
1. Data tab -> "New Query" -> "From File" -> "From Folder"
2. Navigate to and pick the folder you need.
3. This will create preview table with file attributes. Hit Edit.
4. Select [Extension] column and right click "Transform"->"UPPERCASE". This is to accommodate for cases where files are manually renamed and extension has difference case.
5. On the same column, filter for ".XLSM"
6. Click on Binary field and drill down.
7. This will list Tables and Sheets (named range as well I believe), Filter for the specific sheet name.
8. Remove all but the [Data] column.
9. Expand and change data type on expanded column as needed.
10. Perform any additional transformation needed and load to sheet.

This process is dynamic as long as there is only one file in the folder with ".XLSM" and sheet name does not change. You can just refresh the table and any changes on specified sheet will be brought back in.

[Mumps1]

In your Post #3 you asked:

Is it possible to have it automatically choose the file that is in the folder and import it?

Since you originally said that the source file name can change and now you say that the folder path can also change, then I can't see how it is possible to have the macro open the file automatically. As the old saying goes, it's like trying to hit a moving target. When you say:

I don't know how to copy the sheet

the macro I suggested does exactly that. I don't know if I'm missing something.