PrintOut to Appended File 2

[GoatieEddie]

Hi Guys,

I have a macro that does some calculations and then prints out two sheets. It then repeats this process for a whole range of scenarios. Consequently I end up clogging up the printer much to my colleagues' annoyance (very slow printer queues!) I know you can printout to a filename but how do you set it so that the filename is appended with each new 'print' so that I have single file I can send to the printer later?

many thanks,
GE

 

[VBAjedi]

Not sure if you can do exactly what you are asking, but you could copy special (values and formats) the first two sheets to a new workbook. Then for each additional set of sheets you generate, insert linebreaks below the last data in the new workbook and copy the new values & formats over below the linebreaks.

Or, you could just schedule your current code to run when the printer is in low demand. . .

VBAjedi

 

[RobBroekhuis]

You can print to file, and then concatenate the files after the fact. For example, I printed one workbook to c:\book3,
another one to c:\book3, then created a concatenated print file by:

shell "command.com /c copy c:\book3+c:\book4 books"

The concatenated file, at least on my end, prints just fine when sent to the printer.


Rob

 

[VBAjedi]

Nice, Rob! (other than the fact that you said in your example that you printed both to C:\book3)

Have a star for that approach. . .

VBAjedi

 

[RobBroekhuis]

Every once in a while my old DOS knowledge still comes in handy. Less and less, I admit...


Rob

 

[GoatieEddie]

Will give it a go! Given I will have sixty of these, I'll write a DOS batch file. Now that takes me back!

 

[GoatieEddie]

ON guys I'm being really dim here! How do I send the concatenated file to the printer. When I right click in Windows explorer, the Print option doesn't appear!

many thanks on this now urgent matter!

GE

 

[RobBroekhuis]

I figured out the port/path configuration for my network printer, and copied the file to the printer in DOS using something like

copy books \\MyPrintServer\MyPrinterName

I admit it's not pretty...


Rob

 

[MakeItSo]

Didn't mean to interrupt but haven't you guys heard of
Open "C:\myfile.txt" for Append as FreeFile
? ;-)

Of course you can copy text files together, but there is an Open-option for that.

Cheers,
ANdy

 

[GoatieEddie]

Sorry Andy - now i am being very dim! What do you mean?

 

[RobBroekhuis]

Yeah, but that kind of file handling really applies mostly to text files. Printer output files would be tricky to handle using the sequential I/O (since they may contain all kinds of characters, including the EOF character, which could throw VBA off), that's why I prefer to use the DOS approach.

Rob

 

[MakeItSo]

Thanks for the feedback Rob - didn't know that.

Don't let yourself get confused Eddie. I wasn't aware of the trickyness of print files. Better stick with Rob's approach. You're in good hands...


ANdy

 

[GoatieEddie]

Rob,

Nothing coming out of the printer - no doubt somewhere out in the ether! Bugger! Thanks for the help though.

GE

 

[GoatieEddie]

Rob,

On investigation, the copy command sends something away and I get the 1 files copied message but nothing comes out of the printer. However, if I use the DOS print command with a text file to ensure I have the right printer path, it spews out immediately. What is happening to the file from the copy command if the file from the print command is safely coming out. I know this has gone off-topic but it is bugging my curiousity!

Cheers,
GE

 

[RobBroekhuis]

In your case, to what destination are you copying the file?


Rob