Making "sheets" in Excel from filePro?
John Esak
john at valar.com
Fri Mar 23 07:02:52 PDT 2007
This is good, Mike, much appreciated... but I knew I could do this with a
step by the user of some kind... I want/need to do it in one step and email
it all done.
John
> -----Original Message-----
> From: filepro-list-bounces+john=valar.com at lists.celestial.com
> [mailto:filepro-list-bounces+john=valar.com at lists.celestial.com]On
> Behalf Of Mike Schwartz
> Sent: Friday, March 23, 2007 9:53 AM
> To: 'Filepro-List at Lists. Celestial. Com'
> Subject: RE: Making "sheets" in Excel from filePro?
>
>
> (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
>
>
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> http://mailman.celestial.com/mailman/listinfo/filepro-list
More information about the Filepro-list
mailing list