Files which produced no output

[Rocio Avendano]

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

 

[Josue Barocio]

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