PDA

View Full Version : [SOLVED] Importing Worksheets from different Workbook



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.

mancubus
11-11-2015, 08:12 AM
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
11-11-2015, 08:52 AM
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
11-11-2015, 01:17 PM
post your workbook via Go Advanced / Manage Attachments.

replace confidential / sensitive data, if any...

endout
11-12-2015, 06:47 AM
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
11-12-2015, 07:08 AM
you can upload multiple workbooks...

endout
11-16-2015, 02:16 AM
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
11-16-2015, 07:26 AM
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
11-16-2015, 08:35 AM
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
11-16-2015, 08:58 AM
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
11-17-2015, 05:39 AM
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
11-17-2015, 06:26 AM
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
11-18-2015, 08:08 AM
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.