Making "sheets" in Excel from filePro?

[Mike Schwartz]

(top posted for John)

     I handle the same problem by creating a single flat spreadsheet using
filePro, then running an Excel macro that splits the flat spreadsheet into
tabbed worksheets. Admittedly this is a manual step that I have to do, but
the users only have to run the macro on a few monthly spreadsheets, so I've
never tried to automate it any further.

     There are some samples of these kinds of macros in the Excel forum on
Microsoft's web site.  Here is a sample macro that splits a sheet based on
the contents of the first column of the original flat spreadsheet:

Sub ExportSheetsFromDatabase()
'Based on the value in the first column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range

Set myArea = Range("A1").CurrentRegion.Columns(1).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add
mySht.Name = myCell.Value
With myCell.CurrentRegion
    .AutoFilter Field:=1, Criteria1:=myCell.Value
    .SpecialCells(xlCellTypeVisible).Copy _
        mySht.Range("A1")
    mySht.Cells.EntireColumn.AutoFit
    .AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub   

> -----Original Message-----
> From: filepro-list-bounces+mschw=athenet.net at lists.celestial.com
> [mailto:filepro-list-bounces+mschw=athenet.net at lists.celestial.com] On
> Behalf Of John Esak
> Sent: Friday, March 23, 2007 4:24 AM
> To: Filepro-List at Lists. Celestial. Com
> Subject: Making "sheets" in Excel from filePro?
> 
> Hello,
> 
> I haven't looked at this yet... because I don't really know where to look.
> I
> suppose I could try and  analyze a simple Excel spreadsheet... but maybe
> one
> of you knows the answer already. We make lots of spreadsheets with
> filePro.
> Just run out an export on every record as the report is going along... and
> you have an Excel spreadhseet... no problem.  But, what if I want/need to
> put data onto more than one tab in a signle spreadsheet. I'm not sure what
> they are called, but at the bottom of the spreadsheet, there are tabs and
> clicking them give you another spreadsheet. They aren't called pages I
> don't
> think... hell, maybe they are, or maybe they are just "tabs". Whatever
> they
> are, they can be labeled and it seems like you can have as many of them as
> you want. Well I want 3 or 4 of them. Does anyone know how to get them...
> from a filePro export?
> 
> In other words I'm dumping row after row of filePro records onto a
> spreadsheet... then, I want to switch to a new one of these "tab/pages"
> and
> start dumping from the top row again. I also want to title these tabs as I
> go.  Any ideas?
> 
> Thanks,
> 
> John
> 
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> http://mailman.celestial.com/mailman/listinfo/filepro-list