Files which produced no output :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Files which produced no output
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Rocio Avendano
Board Regular


Joined: 27 May 2003
Posts: 14

Flag: Usa

Status: Offline

 Reply with quote  

Files which produced no output

icon_eek.gif 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

Post Wed May 28, 2003 5:53 am 
 View user's profile Send private message

Josue Barocio
Board Master


Joined: 09 Mar 2002
Posts: 101

Flag: Mexico

Status: Offline

 Reply with quote  

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

Post Wed May 28, 2003 11:22 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.