Files which produced no output

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Files which produced no output

  1. #1
    New Member
    Join Date
    May 2003
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Files which produced no output

     
    :o On a monthly basis, I have approximately 400 reports saved in a directory and out of these, only 110 reports need to be distributed to users (via e-mail). There is a macro which has been created to open up each file for the reports that I do need.
    Here's part of the code for the first macro:

    If Len(Dir("F:\rharrell\Billing Docs\april summary files\SummaryRep001.xls")) <> 0 Then Workbooks.Open Filename:= _
    "F:\rharrell\Billing Docs\april summary files\SummaryREP001.xls"
    If Len(Dir("F:\rharrell\Billing Docs\april summary files\SummaryRep004.xls")) <> 0 Then Workbooks.Open Filename:= _
    "F:\rharrell\Billing Docs\april summary files\SummaryREP004.xls"
    If Len(Dir("F:\rharrell\Billing Docs\april summary files\SummaryRep014.xls")) <> 0 Then Workbooks.Open Filename:= _
    "F:\rharrell\Billing Docs\april summary files\SummaryREP014.xls"
    If Len(Dir("F:\rharrell\Billing Docs\april summary files\SummaryRep016.xls")) <> 0 Then Workbooks.Open Filename:= _
    "F:\rharrell\Billing Docs\april summary files\SummaryREP016.xls"
    etc.


    Now for each of the open reports, I need to format and save each one using the reports' number as file naming conventions. HOwever, there are some reports numbers which are included in the first macro which may not have had output for a given month. How do I save each report so that the filename equals the report location number for each open report, skipping reports which had no output?

    Here's part of the code I have for the macro:

    Sheets(Array("BY VENDORS", "Merged", "Sheet3")).Select
    Sheets("BY VENDORS").Activate
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.25)
    .RightMargin = Application.InchesToPoints(0.25)
    .TopMargin = Application.InchesToPoints(0.25)
    .BottomMargin = Application.InchesToPoints(0.25)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    End With
    Sheets("BY VENDORS").Select
    Columns("H:H").Select
    ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
    Columns("O:O").Select
    ActiveWindow.SelectedSheets.VPageBreaks.Add Before:=ActiveCell
    Range("A1").Select
    ActiveWorkbook.Save
    ChDir "F:\Gomez\Monthly REPs\Electronic REPs\2003\April '03 Usage"
    ActiveWorkbook.SaveAs Filename:= _
    "F:\rharrell\Billing Docs\april summary files\999-Sum.xls" _
    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    ActiveWorkbook.Close

    etc.

    Can this be done?

    Regards,

    Rocio

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Files which produced no output

      
    Hi Rocio,
    Couple of questions. Can you use the same name as the original when you save the report?
    Is the task of formatting the reports already automated? or do you format them manually?
    If the reports were prepared using a template, then there would be no need to format each report separately. Is this possible or am I missing something?

    If you can use the same name and you already have a formatting macro, then you can simply open each rport, store the name in an array, format it, then read the array to save the formatted report.

    Let me know if you think this would work and we'll take the next step.

    Regards,

    Joe

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com