Rocio Avendano
New Member
- Joined
- May 27, 2003
- Messages
- 14
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
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