Excel Question

[Dolphy]

Hi All,

If I get an excel document that has 2500 rows.

But I can only use 1000 at a time.

Is there a way for me to have excel automatically split the file in
groups of a 1000?

In this example I would need three files, two with a 1000 rows and one
with 500 rows.

Rgds,
Adolph

 

[Dave Peterson]

I'm not sure what you mean by automatic, but you could run a macro like this:

Option Explicit
Sub testme()
Dim NewWks As Worksheet
Dim ActWks As Worksheet
Dim iRow As Long
Dim myStep As Long

Set ActWks = ActiveSheet
myStep = 1000
With ActWks
For iRow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row Step myStep
Set NewWks = Workbooks.Add(1).Worksheets(1)
.Rows(iRow).Resize(myStep).Copy _
Destination:=NewWks.Range("a1")
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

Dolphy

What operation are you performing with the data that limits you to 1000 rows?


Gord Dibben MS Excel MVP

 

[Dolphy]

HI Gord,

I'm running an internal work application that has the limitation.

I sometimes get documents that have 45K rows.

Rgds,
Adolph

 

[Gord Dibben]

Thanks for feedback.

I'm just a nosy old fart<g>


Gord

HI Gord,

I'm running an internal work application that has the limitation.

I sometimes get documents that have 45K rows.

Rgds,
Adolph

Gord Dibben MS Excel MVP

 

[Dolphy]

Hi Dave,

Thanks for the B Script, it worked like a charm.

I know I pushing my luck, how do I make the output file have a specific
name eg: Dolphy01, Dolphy02 and Dolphy03?

Thanks for your assistance.

Rgds,
Dolphy

 

[Dave Peterson]

Files aren't named until you save them.

Option Explicit
Sub testme()
Dim NewWks As Worksheet
Dim ActWks As Worksheet
Dim iRow As Long
Dim myStep As Long
Dim wCtr As Long

Set ActWks = ActiveSheet
wCtr = 0
myStep = 100
With ActWks
For iRow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row Step myStep
Set NewWks = Workbooks.Add(1).Worksheets(1)
.Rows(iRow).Resize(myStep).Copy _
Destination:=NewWks.Range("a1")
wCtr = wCtr + 1
NewWks.Parent.SaveAs Filename:="C:\temp\" _
& Format(wCtr, "0000") & ".xls"
NewWks.Parent.Close savechanges:=False
Next iRow
End With
End Sub

Make sure that folder exists before you start--I used C:\temp\. Change it to
what you need.

And I'd clean up that folder before starting.

But you could overwrite the files without getting a prompt by using this:

application.displayalerts = false
NewWks.Parent.SaveAs Filename:="C:\temp\" _
& Format(wCtr, "0000") & ".xls"
application.displayalerts = true

 

[b.engelbrecht]

In Excel 2003 you need not only DisplayAlerts = False, but also
AlertBeforeOverwriting = False.

Best regards,

Berend

Dave Peterson schreef:

 

[Dave Peterson]

I don't think so.

This is from xl2003's help for AlertBeforeOverwriting...

True if Microsoft Excel displays a message before overwriting nonblank cells
during a drag-and-drop editing operation. Read/write Boolean.

That's the setting you can toggle via Tools|Options|Edit tab|under the allow
cell drag and drop box.

 

[b.engelbrecht]

Well, I thought so too, but DisplayAlerts = False actually did not work
when I tried it. They seem to have silently changed its functionality.
In the Microsoft.Office.Interop.Excel reference, it is now documented
as follows:
=====
Excel Primary Interop Assembly Reference
_Application.DisplayAlerts Property

[...]

When using the SaveAs method for workbooks to overwrite an existing
file, the 'Overwrite' alert has a default of 'No', while the 'Yes'
response is selected by Excel when the DisplayAlerts property is set to
False.
====
http://msdn2.microsoft.com/en-us/li....excel._application.displayalerts(VS.80).aspx

I had the exact same (unwanted) behaviour when I tried it from VBA.
After setting the AlertBeforeOverwriting flag, it started working
properly. Removed that statement, and the dialog was back again.

Dave Peterson schreef:

 

[Dave Peterson]

I've never seen any differences with plain old VBA in xl95-xl2003.

I don't know anything about the other stuff.

Well, I thought so too, but DisplayAlerts = False actually did not work
when I tried it. They seem to have silently changed its functionality.
In the Microsoft.Office.Interop.Excel reference, it is now documented
as follows:
=====
Excel Primary Interop Assembly Reference
_Application.DisplayAlerts Property

[...]

When using the SaveAs method for workbooks to overwrite an existing
file, the 'Overwrite' alert has a default of 'No', while the 'Yes'
response is selected by Excel when the DisplayAlerts property is set to
False.
====
http://msdn2.microsoft.com/en-us/li....excel._application.displayalerts(VS.80).aspx

I had the exact same (unwanted) behaviour when I tried it from VBA.
After setting the AlertBeforeOverwriting flag, it started working
properly. Removed that statement, and the dialog was back again.

Dave Peterson schreef: