endout
11-10-2015, 06:52 AM
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.
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.