Group and split into seperate excel workbooks 3

[Bilberry]

Hi Friends,
I have a huge spreadsheet (~20.000 rows each time). It looks like as follows:

Column A        B       C   D   E               F       G       H               I               J
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	05:56:17	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	08:29:27	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	10:33:55	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	11:51:54	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	12:49:45	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	13:53:35	3,14
Test records	595	595 A	11-XXX-12	3522	A	4-11-2013	14:57:59	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	05:24:56	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	07:44:06	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	08:37:08	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	10:00:48	3,14
Test records	595	595 A	11-XXX-12	3522	A	5-11-2013	11:59:54	3,14
Test records	595	595 A	11-XXX-12	3522	A	6-11-2013	05:40:48	3,14
Test recordss	595	595 A	11-XXX-13	3523	A	6-11-2013	08:39:37	3,14
Test recordss	595	595 A	11-XXX-13	3523	A	6-11-2013	10:06:36	3,14
Test recordss	595	595 A	11-XXX-13	3523	A	7-11-2013	05:25:56	3,14
Test recordss	595	595 A	11-XXX-13	3523	A	7-11-2013	07:47:20	3,14

I need to group by column E and split these records into seperate workbooks. In this example i want to get two workbooks with the names:
11-XXX-12.xls
11-XXX-13.xls

and containing only the records which belongs to column E.

Anybody idea how to setup this?
A star for the golden solution!

 

[SkipVought]

No problem.

BTW what part of the world are you in? North Texas here.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Europe - Netherlands -

Below the recording to get the unique values:

Sub Macro2()
'
' Macro2 Macro
'

'
    Columns("D:D").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$A$19189").RemoveDuplicates Columns:=1, Header:= _
        xlYes
    Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
        False
End Sub

 

[SkipVought]

Make sure your list sheet is named List

Sub Macro2()
'
' Macro2 Macro
'

'
    Columns("D:D").Copy
    
    With Sheets("List")
         .cells(1,1).PasteSpecial xlpasteall

         .Range("$A$1").currentregion.RemoveDuplicates Columns:=1, Header:=  xlYes

         Application.DisplayAlerts = False
         .Range("$A$1").currentregion.CreateNames Top:=True, Left:=False,Bottom:=False, Right:=False
          Application.DisplayAlerts = true
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Hi Skip,
You've forgotten the End With statement -

I will check the code again and put some buttons and user inputs to change the directory and file names.

You are really the best. I know you more than 10 years now here, and your support is always appreciated!

Thanks a lot Skip!

 

[SkipVought]

Check out Application.GetOpenFileName() in VBA Help.

I hope I have given you sone tools for constructing an application.

Good luck and post back anytime.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

BTW, the Ozgrid link you posted had some excellent code for DISCOVERING what ListObjects and QUERYTABLES are on a sheet. You might want to copy that to your PERSONAL.XLSB workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

"nov 2013.xls" The filename will change every month, how can I make it generic?

Current month: [tt]
MsgBox Format(Date, "mmm yyyy") & ".xls"[/tt]

Previous month - Nov 2013.xls:[tt]
MsgBox Format(DateAdd("m", -1, Date), "mmm yyyy") & ".xls"[/tt]

You may use [tt]LCase()[/tt] if you want the Month to start with the lower case letter.

Have fun.

---- Andy

 

[Bilberry]

Skip, Andy,
If I try to import the same Excel file with another name and deleted some rows then i get a Run time error 1004, General ODBC error.

.Refresh BackgroundQuery:=False

What can be the cause?

 

[SkipVought]

Did you assign the appropriate sPath & sDB?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

Hi Skip,
Yes I did. I have copied the same source file copied it and renamed the file. I have also deleted some rows. Within the macro I've changed the sDB, the path is the same. Very strange. Do you have more idea's?

 

[SkipVought]

Did you delete any COLUMNS in you source file?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Sorry, I must have posted in error

    With ActiveSheet.ListObjects[highlight](1)[/highlight].QueryTable
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Just an observation:

It seems that your source workbook/source table is referenced by an range named [highlight]Qr[/highlight]. Tiy want to be certain that whenever rows are added or removed from the table, that the range name reference is reassigned to reflect the entire table.

Under most conditions, I'd use the sheet tab name followed by the $ symbol, which precludes a range name adjustment.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Bilberry]

If i add a $ sign before the tab name, the same issue occurs. (

 

[SkipVought]

sheet tab name [highlight]followed by the $ symbol[/highlight]

Please post the current code that you are using.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

If i add a $ sign before the tab name
The $ sign should be AFTER the tab name !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bilberry]

Sub ImportData()
    Dim sConn As String, sSQL As String
   
   
    sPath = Range("L1").Value ' c:\13
    
    sDB = Range("L2").Value ' nov2 2013.xls
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT"
    sSQL = sSQL & "  Qr.Name1"
    sSQL = sSQL & ", Qr.BCode"
    sSQL = sSQL & ", Qr.tcode"
    sSQL = sSQL & ", Qr.Kent"
    sSQL = sSQL & ", Qr.Trsp"
    sSQL = sSQL & ", Qr.`E-Lbl`"
    sSQL = sSQL & ", Qr.Dtm"
    sSQL = sSQL & ", Qr.Time1"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.Qr"   

With ActiveSheet.ListObjects(1).QueryTable
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

I have put the sign after the tabname (source$) within my workbook...

 

[SkipVought]

The BIG question is, does [highlight]Qr[/highlight] reflect the Source Workbook Table RANGE, because when I run your code, with my path & file name in L1 & L2, IT RUNS and returns the data that I have in range [highlight]Qr[/highlight]!



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PureSlate]

For what it's worth, I think this is a built-in function of the ASAP utility add-on, so long as there are fewer than 65,536 rows.

http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=267&utilities=20&lang=en_us

 

[Bilberry]

Hi Skip,
What i have done is:
- tested with source1 as above. Everything works perfectly
- Copied en renamed source1 and deleted some rows

It is not the same range, because there are not 20.0000 record but 10.0000. Is that the problem??