PDA

View Full Version : Solved: Import by tab name



ndendrinos
07-15-2007, 08:14 AM
http://vbaexpress.com/forum/showthread.php?t=13714
Very similar to above I need help in Command Button1 of UserForm1
I need the code to import by the specific sheet name entered in the text box.
(as the code stands now it imports just the first sheet of each file stored in folder "Vault")

many thanks

lucas
07-15-2007, 08:42 AM
So you want to import a specific sheet(entire sheet) from a closed workbook into your open workbook?

How are you finding this workbook? Is it in the same path as your open workbook? Do you wish to browse to it?......questions


(as the code stands now it imports just the first sheet of each file stored in folder "Vault")

What code are you referring to....no code is posted at the link you give.....

ndendrinos
07-15-2007, 08:59 AM
hello Lucas. Not sure why the original (RAR) attachment does not show in my post but I attach here a winzip version of it ... still not working ... give me a few minutes pls. Not sure how to fix this ... I convert the folder that contains 2 files to a zip one. The zip file created looks fine but when I try to attach it I see it as a RAR.
Not sure why I removed RAR from my computer ... I will have to work on this longer

lucas
07-15-2007, 09:13 AM
You can post twice if necessary and attach one excel file to each(not zipped)....I have a little time so no worry.

ndendrinos
07-15-2007, 09:23 AM
cleaned the registry and had to reboot to get it going but now it works sorry for the delay and thank you

lucas
07-15-2007, 11:27 AM
Hi Nick,
Sorry for the delay. I may have gotten off base as I used a combobox instead of a textbox....easy to change if it's a problem. You could also have it easily delete all sheets at the end if desired except the ones you wish to keep. Let me know if this works for you.

ndendrinos
07-15-2007, 12:07 PM
Thank you for your reply Lucas.
The combobox is OK too.
Importing all sheets from the workbooks and then deleting in order to keep one date is also OK
My comcern is that based on 200 working days a year and based on many workbooks that's a lot of importing and deleting to do.
In my mind the macro would run , match the date in the text box to the tabs in each workbook within the folder "Vault" and import just these. (If when going through the tabs the desired date does not exist then the code would go on to the next workbook)
I'm sure this is what you have in mind also , so any solution you come up with is welcome.
Another route is to have code in each of the workbooks in "vault" send the data to Access" trouble is I know how to import a unique record from Accesss to Excel but not a group of records with the same date .
I guess I could do what you suggest and import the whole table and then filter it.... bu then again I'm doing the same as importing directly from all the workbooks as in my attachment.
So let us stay with this existing scenario .
Thanks again

Addendum: just noticed you've included and attachment with your last reply & need time to look at it.

ndendrinos
07-15-2007, 12:13 PM
Lucas, the sample you provide is perfect and yes I need to delete all the new tabs created.
Need to add code to delete all sheets between Sheet1 and sheet ("master")
Great work with thanks

ndendrinos
07-15-2007, 12:20 PM
somthing different that this perhaps:
Sheets(Array("Jan2", "Jan2 (2)", "Jan2 (3)")).Select
ActiveWindow.SelectedSheets.Delete
but rather :
If sheet NOT Sheet1 OR Master then delete sheets

ndendrinos
07-15-2007, 12:56 PM
Also needs an "error handler" in case there is no sheet in the work book that match the selection in the combo.
Other than that it run like clockwork

mdmackillop
07-15-2007, 01:04 PM
Hi Steve,
Seems to me this will transfer only 3 cells from each sheet.

Also, for other situations, copy and paste or pastespecial will get the formulae and formats.

'Data range in worksheet - starts from second row
'as first rows are the header rows in all worksheets
Set rng = sht.Range("A2:C2")

'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value

ndendrinos
07-15-2007, 01:20 PM
Hello mdmackillop and thank you for your observation. The range A2:C2 is intentional but could I not expand the range down the road?
As to :

Also, for other situations, copy and paste or pastespecial will get the formulae and formats.
Not sure I understand.
The code pastes "values" only or at least I think it does .
In the affirmative this also is desirable.
Please correct me if wrong on either counts.
As always learning from you

mdmackillop
07-15-2007, 01:47 PM
There is nothing "wrong" with Steve's code, but some small inconsistencies. These posts may well be viewed by others with out your specific requirements.
If the range to be copied is set, then simplify things using the range dimensions in Resize, rather than calculated values.
The code does indeed copy Values. I was just pointing out that this may not always be desirable.

lucas
07-15-2007, 01:59 PM
Hi Steve,
Seems to me this will transfer only 3 cells from each sheet.

Hi Malcolm,
As Nick pointed out...that was all the info he wanted merged to the master sheet...

Will try to help him delete the extra sheets if I have a minute that are left after the merge...

ndendrinos
07-15-2007, 02:04 PM
All clear now with thanks
I think you mean to use something like or similar to this expression (R1,3) instead.
I'm still restling with the error handler needed and the deletion of the woksheets created by the first macro.
When I solve it I will mark the post "solved"
Thank you all

ndendrinos
07-15-2007, 02:06 PM
Hello Steve and thank you for revisiting.
I have to leave now but will be back hat in hand to check.

lucas
07-15-2007, 02:14 PM
This will delete all the sheets except sheet1 and Master when you click the button on the second userform.....didn't get to the error handler yet...:dunno

ndendrinos
07-15-2007, 04:57 PM
Hello Steve.
Yes that works great in deleting the sheets. Sorry I took time to answer this but I'm busy reading you and Norie at the lounge ...:devil2:

lucas
07-15-2007, 06:02 PM
Rascal...you just had to stir it up...:hide:

daniel_d_n_r
07-16-2007, 01:58 AM
Sub File_Sheet_Script()

Dim w_s As Worksheet

For Each w_s In ThisWorkbook.Worksheets
s = w_s.Name
UserForm1.ListBox1.AddItem (s)

Next

UserForm1.Show

End Sub

........

might help, then all you do is use the listbox control to use "selected" as
the sheetname to open/delete,,ect

use filesystem object to create a file object if you need to source files other than the one you have open..

make it a global so it works in all workbooks

ndendrinos
07-17-2007, 04:18 PM
Hello Daniel,
Sorry I missed your reply and I admit I do not understand it.
What is missing from Lucas's attachment is an Error Handler
Irrespective of this if you get a chance can you maybe post an example of
your suggestion ? I can use all the help I can get.
Thank you.

ndendrinos
07-26-2007, 05:13 PM
Still need an error handler in Lucas's code.
When I run the code AND if there is no Sheet named for example Jan2 in one of the WBs in folder Vault I get an error code 9 (Subscript out of range)
How can one add to the code the condition :
If there is no sheet in any given WB that matches the selection in the combobox to then go to the next WB?
Thank you



Option Explicit
Private Sub ComboBox1_Change()

End Sub

Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "Jan1"
.AddItem "Jan2"
.AddItem "Jan3"
End With
End Sub
Private Sub CommandButton1_Click()


Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False

Path = "C:\Documents and Settings\nick dendrinos\Desktop\Vault" 'Change as needed
'Path = "F:\Temp\Extract to your desktop\Vault" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""

Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
Set WS = Wkb.Sheets(Me.ComboBox1.Value)
WS.Activate
Wkb.ActiveSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
'Wkb.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Unload Me
UserForm2.Show

End Sub

Charlize
07-31-2007, 12:04 AM
Not tested but something like this : Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False

Path = "C:\Documents and Settings\nick dendrinos\Desktop\Vault" 'Change as needed
'Path = "F:\Temp\Extract to your desktop\Vault" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
'*** Added coding for checking if sheetname exists
'check each worksheets in your workbook
For Each WS In Wkb.Worksheets.Count
'if worksheetname = the combobox value then copy
'else do nothing
If WS.Name = Wkb.Sheets(Me.combobox1.Value) Then
Set WS = Wkb.Sheets(Me.combobox1.Value)
WS.Activate
Wkb.ActiveSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
'Wkb.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
'close the workbook we opened without any notification
Wkb.Close False
'*** End of added/modifying coding
'process next workbook if different than ""
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Unload Me
UserForm2.Show
End Sub

ndendrinos
07-31-2007, 12:37 PM
Thank you Charlize,
I get a compile error : For Each may only iterate over a collection object or an array.
The word : .Count is highlighted.
For Each WS In Wkb.Worksheets.Count

Charlize
07-31-2007, 03:49 PM
Remove the .Count so it becomes For Each WS In Wkb.Worksheets

rory
07-31-2007, 04:25 PM
Slight tweak:
Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False

Path = "C:\Documents and Settings\nick dendrinos\Desktop\Vault" 'Change as needed
'Path = "F:\Temp\Extract to your desktop\Vault" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
'*** Added coding for checking if sheetname exists
'check each worksheets in your workbook
For Each WS In Wkb.Worksheets
'if worksheetname = the combobox value then copy
'else do nothing
If WS.Name = Wkb.Sheets(Me.combobox1.Value) Then
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Exit For
'Wkb.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
'close the workbook we opened without any notification
Wkb.Close False
'*** End of added/modifying coding
'process next workbook if different than ""
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Unload Me
UserForm2.Show
End Sub


Regards,
Rory

ndendrinos
08-01-2007, 04:27 AM
Charlize, Rory.
Thank you both for your help. Tried both your last suggestions and got the same
Runtime Error 438
Object doesn't support this property or method
and this line is now highlighted:

If WS.Name = Wkb.Sheets(Me.ComboBox1.Value) Then

Charlize
08-01-2007, 04:41 AM
If WS.Name = Wkb.Sheets(Me.combobox1.Value) Then must beIf WS.Name = Me.combobox1.Value Then

ndendrinos
08-01-2007, 05:27 AM
Yes Charlize that did it & I thank you very much for your help.
Also a big thank you to all that participated .