Importing Worksheets from different Workbook

[endout]

Hi,

I'm currently trying to assemble a master workbook which may be used to create reports from different source. Curently there are serveral different excel files, each which only one spcial purpose. I'd like to import the data from these worbooks and work with them in the master workbook.
The FileImport should work using a UserForm with which the User may select a file using the FileDialog and a worksheet within that file from a ComboBox. The selected workshet should then be imported with the same name and prefix "Import" (i.e Import_data)
While establishing the UserForm, selecting a file and parsing it's worksheets in the combobox works fine, I can't get my program to actually import the data.
There are two options for the fileImport: Firstly I could copy the whole worksheet or just the actual data within it.

My Code so far is this:
Useform

Private Sub opendialog_Click()
Call GetFile
End Sub
Private Sub CommandButton1_Click()
Call ImportFile
End Sub

Module FileImport

Sub GetFile()Dim SelectedFile As String
Dim wbSource As Workbook
Dim ws As Worksheet


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False 
.Title = "Select File" 
.InitialFileName = "removed" 


    If .Show Then
    SelectedFile = .SelectedItems(1)
    Set wbSource = Application.Workbooks.Open(SelectedFile)
    For Each ws In wbSource.sheets
    Import_Dialog.listSheet.AddItem ws.Name
    Next
    
    Import_Dialog.filename.Text = SelectedFile
    End If
End With
End Sub



Sub ImportFile(SelectedFile As String, ws As Worksheet)
Dim impsheet As Worksheet, pastsheet As Worksheet, wbPaste As Workbook, lr As Long, rng As Range
Set wbPaste = ThisWorkbook
Workbooks.Open (SelectedFile)
Set impsheet = sheets(ws)
Set pastsheet = sheets("Import_" & ws)
lr = impsheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = impsheet.Range("A2:A" & lr)
rng.EntireRow.Copy wbPaste.pastsheet.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

I do get an error "argument not optional" in the ImportFile Sub, which is also carried to the UserForm. Where exactly is my code wrong or how do you suggest i rewrite it?

Furthermore, is it possible to open the file in the background, copy the data and close it again? Currently the file stays open even after it should have been clodes by the code. This might be useful for larger files so that i won't have to open it again to import another sheet but for smaller files it would be cleaner if the file would be closed automatically after the data Import.

[mancubus]

welcome to the forum.


i will not go into detail

try:
1

Sub ImportFile(SelectedFile As String, ws As String)

2

Workbooks.Open SelectedFile
Windows(SelectedFile).Visible = False
'or
'ActiveWindow.Visible = False 
'...
'...
'...
Workbooks(SelectedFile).Close False

[endout]

Thanks for the reply.
I've changed the type of ws to String but I still get the error "argument not optional" when pressing the import-button on the UserForm which schould trigger the ImportFile-Sub.

[mancubus]

post your workbook via Go Advanced / Manage Attachments.

replace confidential / sensitive data, if any...

[endout]

That would be pretty much an empty workbook. I have one workbook with the code and a worksheet with an UserForm in it. The actual data is stored in different workbooks with different worksheets. These worksheets should be imported into the master-workbook.
EDIT: empty because there is no data in the masterworkbook and i can't upload the data files, since they are full of confidential data

[mancubus]

you can upload multiple workbooks...

[endout]

I've made two other files so that you might see what I'm trying to do.
I've got my master workbook called test in which the data from "data.xlsx" should be imported using the UserForm1. The macro should check for the first empty row/column and copy all the data within to test.xlsm/data_source.
The id worksheet will be used later to unify the data from the imported worksheets to be displayed in the masterworkbook.

[mancubus]

take time to properly design worksheets and userforms.

see attached workbook.

i just tried to resolve your problem with

(codes in module Import)

Sub GetFile()
  
    Dim FileToOpen As String
    
    FileToOpen = Application.GetOpenFilename _
        (Title:="Please choose a file to open", _
        FileFilter:="Excel Files *.xls* (*.xls*),")
    
    If FileToOpen = "False" Then
        MsgBox "No file selected.", vbExclamation, "Quitting..."
        Exit Sub
    Else
        Workbooks.Open FileToOpen
    End If
    
    With ActiveWorkbook
        For Each ws In .Worksheets
            UserForm1.ComboBox1.AddItem ws.Name
        Next
        .Close False
    End With
    
    UserForm1.TextBox1.Text = FileToOpen

End Sub



Sub ImportFile(FileToOpen As String, wsName As String)

    Workbooks.Open FileToOpen
    With ActiveWorkbook
        .Worksheets(wsName).Copy After:=ThisWorkbook.Worksheets("Input")
        .Close False
    End With

End Sub

codes in userform1's code module

Private Sub CommandButton1_Click()
    GetFile
End Sub

Private Sub CommandButton2_Click()
    ImportFile TextBox1.Text, ComboBox1.Value
    Unload Me
End Sub

[endout]

Thanks a lot. To be honest, I just put these two files together so that you could visualize what I'm trying to do. Since the actual workbooks contain sensitive data, I couldn't upload either (plus they're too big).
At first i got an runtime error 1004 when using your code, which made me realize that I've been working with .xls for my masterworkbook while the other files have been and will be xlsx, which might aso have been a problem.
So far your code works great, now I'll just have to rename the copied worksheet and maybe delete a few rows to fit in the template. And there might be some more issues in the future, but this probably wont be the correct thread for it.
Thanks again

[mancubus]

ah, i see, ok...

when you copy a sheet (or add a new sheet) it becomes active sheet.

also you may wish to delete a worksheet with the same name to be imported.

Sub ImportFile(FileToOpen As String, wsName As String)
     
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets(wsName).Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
     
    Workbooks.Open FileToOpen
    With ActiveWorkbook
        .Worksheets(wsName).Copy After:=ThisWorkbook.Worksheets("Input")
        .Close False
    End With
    
    ActiveSheet.Name = "NewName"
     
End Sub

[endout]

Thanks. I've created another textbox on the UserForm which has the value of the ComboBox1 + "_data". If anyone would like to give the sheet a different name then they could simply edit it there.
Would it also b possible to prompt the user before deleting the old worksheet if another worksheet with the desired name should already exist? So:
check if new worksheet name already exist
prompt the user asking if that sheet should be deleted
else copy new worksheet and change name

[mancubus]

welcome.

you may need to modify prompt, other code in related subs, etc.

Sub ImportFile(FileToOpen As String, wsName As String)
     
    Dim ws As Worksheet
    Dim confirmation As VbMsgBoxResult
    
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(wsName)
    On Error GoTo 0
    
    If Not ws Is Nothing Then
        confirmation = MsgBox("Do you want to delete existing worksheet?", vbExclamation + vbYesNo, "Warning")
        If confirmation = vbYes Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    End If
    
    Workbooks.Open FileToOpen
    With ActiveWorkbook
        .Worksheets(wsName).Copy After:=ThisWorkbook.Worksheets("Input")
        .Close False
    End With
     
    ActiveSheet.Name = "NewName"
     
End Sub

ps: as is well known, if the answer is No even the worksheet Input exists, Input sheet will be copied to ThisWorkbook as Input (2)

[endout]

That works as far as I've tested. I've already noticed, that the worksheet will be copied anyway with the (%number%)-suffix, but that should not be a problem.
I do however sometimes get the error that i can't give a worksheet a new which is used by vba (1004). In that case the worksheet will sitll be copied but it won't get the name I've assigned it in the textbex on the UserForm. instead it keeps the original name it had in the source workbook.