Copy sheet content from selected file to the current one

Question

I need to open a "select file" dialog then copy a sheet (always same name, same position) from the selected file into the second sheet in the current Excel file.

So far the I can choose and open the file. That is where it stops. Nothing is copied and nothing is in my clipboard.

I searched and found a few not working solutions.

Here is the code:

Public Sub Import()

Dim VarDateiPfad As Variant
Dim Source As Workbook
Dim FilterDestination As Workbook

'You choose the starting Folder for the "Select File" Dialog
ChDrive ("X:\") 'hier Laufwerk angeben
ChDir ("X:\X....") ' hier exakten Pfad angeben

'Starts the dialog and saves the link to the file
VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)

Workbooks.Open Filename:=VarDateiPfad, ReadOnly:=False

'Here seems to be where the problems occur
ActiveWorkbook.Sheets("X").UsedRange.Copy
ThisWorkbook.Sheets("Import").PasteSpecial xlValues
ActiveWorkbook.Close

This here was my last Version before fixing the issue:

Public Sub Import()

Dim VarDateiPfad As String
Dim FilterSource As Workbook
Dim FilterDestination As Workbook

Set FilterDestination = ActiveWorkbook

'Legt Standard Verzeichnis des "Datei öffnen" Dialogs fest.
ChDrive ("X") 'hier Laufwerk angeben
ChDir ("X") ' hier exakten Pfad angeben

'Startet "Import" Dialog und legt ausgewählte Datei in "VarDateiPfad" ab
VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1, "X")

Set FilterSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)

    FilterSource.Worksheets("X").UsedRange.Copy
        FilterDestination.Worksheets("Import").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Source.Close

Pᴇʜ · Answer 1 · 2018-11-12T10:08:47.503

Avoid using ActiveWorkbook. Instead set the opened workbook to a variable:

Dim WbToImport As Workbook 
Set WbToImport = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)

And then use the variable to access the workbook directly.

WbToImport.Worksheets("X").UsedRange.Copy
ThisWorkbook.Worksheets("Import").Range("A1").PasteSpecial xlPasteValues
WbToImport.Close

With this technique it is much more reliable that the correct workbook is used to copy from, because ActiveWorkbook can be any workbook that is active at the moment. But that's not necessarily the workbook that you just opened.

Example:

Option Explicit

Public Sub Import()    
    Dim FilterDestination As Workbook  
    Set FilterDestination = ThisWorkbook

    'Legt Standard Verzeichnis des "Datei öffnen" Dialogs fest.
    ChDrive ("X") 'hier Laufwerk angeben
    ChDir ("X") ' hier exakten Pfad angeben

    'Startet "Import" Dialog und legt ausgewählte Datei in "VarDateiPfad" ab
    Dim VarDateiPfad As String
    VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1, "X")

    Dim FilterSource As Workbook
    Set FilterSource = Workbooks.Open(Filename:=VarDateiPfad, ReadOnly:=False)

    FilterSource.Worksheets("X").UsedRange.Copy
    FilterDestination.Worksheets("Import").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    FilterSource.Close

score 1 · Answer 2 · answered Nov 12 '18 at 08:04

1

You are almost there.

Public Sub Import()

    Dim VarDateiPfad As Variant
    Dim Source As Workbook
    Dim FilterDestination As Workbook

    Set Source = ActiveWorkbook

    'You choose the starting Folder for the "Select File" Dialog
    ChDrive ("X:\") 'hier Laufwerk angeben
    ChDir ("X:\X....") ' hier exakten Pfad angeben

    'Starts the dialog and saves the link to the file
    VarDateiPfad = Application.GetOpenFilename("Exceldateien,*.xls*", 1)

    Workbooks.Open Filename:=VarDateiPfad, ReadOnly:=False

    'Here seems to be where the problems occur

    Sheets("X").Cells.Copy

    Source.Activate
    Sheets("Import").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False



End Sub

answered Nov 12 '18 at 08:04

Michal Rosa

2,459
2
15
28

Note: It's a bad practice to use `.Activate` and you don't need it if you apply [this technique](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also see my answer to avoid using `ActiveWorkbook` and `.Activate` which is **much** more reliable and faster. Also the OP already used `ThisWorkbook` in a good way. – Pᴇʜ Nov 12 '18 at 08:06
1

You are absolutely right. It is a poor technique and it's scoffed at. On the other hand it's quick and practical,easier to understand for beginners. Cheers, – Michal Rosa Nov 12 '18 at 08:10
But this way beginners learn to do it a bad way. Better to teach the good way to beginners otherwise it will be hard to change their behavior. – Pᴇʜ Nov 12 '18 at 08:13
Thank you both a lot, i added a Post with the current state of the drama ^^ – G.M Nov 12 '18 at 09:13

score 1 · Accepted Answer · edited Nov 12 '18 at 10:08

1

Would leave a comment but didn't have enough rep to do so, so i will leave a reply

FilterSource.Worksheets("X").UsedRange.Copy
FilterDestination.Worksheets("Import").Range("A1").PasteSpecial

you have copied a select range but didn't tell the macro where to paste, so this seems to be the issue

edited Nov 12 '18 at 10:08

Pᴇʜ

56,719
10
49
73

answered Nov 12 '18 at 10:02

Osman Wong

170
9

Copy sheet content from selected file to the current one

3 Answers3