MsgBox Print

[ra_weiss]

Oh, me-oh-my, what have I screwed up this time?

Dim MyFile As String


MsgResult = MsgBox("BOOM! You're done. Print Copies?", vbYesNo, "")
If MsgResult = vbYes Then
MyFile = Dir("C:\Documents and Settings\*i*.xls")
Do While MyFile <> ""
    Worksheet.PrintOut Filename:=MyFile ActivePrinter:="Canon MX320 series Printer"
    MyFile = Dir()
Loop
Else: End If

Why is "Worksheet.PrintOut Filename:=MyFile ActivePrinter:="Canon MX320 series Printer" rejected? Thanks.

 

[Norie]

Couldn't you just print the active workbook twice?

All that appears to be changing is the name after all.

By the way Worksheets on it's own doesn't really mean anything - it needs a workbook reference.

 

[ra_weiss]

Nah, it's more complicated than that.

The reason that code looks so dumb is because, I have two routines running within my code. The first 'call' cannot be saved once finished otherwise the second 'call' will pick up bad data from that first call's saved file. You see, the second call seeks cell data from about 10 workbooks in a particular directory. I can't have that first call's saved file being part of that data.

So the only way I could have both calculated, saved and closed (but not printed!!), was to write that "ActiveWorkbook.SaveAs" twice at the end (so one closes then the other gains focus... I guess). It's a mickey mouse solution but it works.

 

[ra_weiss]

Anywho...

So, Norie, I have two xls files that coincidentally enough, are the only one's in the directory that meet *i*.xls criteria. Would you put that in a print, vbYesNo msgbox for me?

pulease

 

[Norie]

I really don't follow that.

What to you mean by closing 'calls'?

If something in your code is causing data to be picked up from the wrong workbook, surely if you close that workbook it would prevent it?

Also, you might be able to change the code to make sure everyhing is referenced correctly.

 

[ra_weiss]

The second 'call' (Invoice) will pick up bad data if the first 'call' (converters) is saved before the 2nd's finished running.

Sub FinalClose()
Dim MyFile As String


'Open Convert Form
    Application.Workbooks.Open ("C:\Documents and Settings\batch\Converter Form.xls")
'UnProtect
    ActiveSheet.Unprotect Password:="1"
    Call Converter
    ActiveSheet.Protect Password:="1"

'Open Weekly Invoice
    Application.Workbooks.Open ("C:\Documents and Settings\batch\Weekly Invoice.xls")
    ActiveSheet.Unprotect Password:="1"
    Call Invoice

'Save Weekly Totals in C:\Documents and Settings\batch\Worksheets\
    ActiveWorkbook.SaveAs "C:\Documents and Settings\batch\Worksheets\" & Format(Now(), "mm-dd-yyyy") & " Invoice ", FileFormat:=56
        ActiveWorkbook.Close savechanges:=True
    ActiveWorkbook.SaveAs "C:\Documents and Settings\batch\Worksheets\" & Format(Now(), "mm-dd-yyyy") & " Equipment Sheet ", FileFormat:=56
        ActiveWorkbook.Close savechanges:=True

Let MyFile = Dir("C:\Documents and Settings\batch\Worksheets\*i*.xls")

MsgResult = MsgBox("BOOM! You're done. Print Copies?", vbYesNo)
If MsgResult = vbYes Then
    Do While MyFile <> ""
    Worksheets.PrintOut , MyFile, ActivePrinter:="Canon MX320 series Printer"
    MyFile = Dir()
Loop
Else: End If

'Save and close workbooks, quit Excel
    Application.Quit
    ThisWorkbook.Close savechanges:=True
    ThisWorkbook.Close savechanges:=True

End Sub

Edit: The first call cannot be saved until "Call Invoice" has finished.

 

[Norie]

I'm quite confused - I posted code that will print all the workbooks in a directory.

It even included your message box.

The only thing that might be a problem with that would be if one of the workbooks in the directory was open.

 

[ra_weiss]

I realize that but if it didn't work. I thought the reason it didn't work is because you weren't quite sure what I was doing.

Your code didn't come back with any errors, nor any prints :D

 

[ra_weiss]

Actually, it does come back with an error. The error is that at least one of the sheets doesn't exist, but if I open the respective directory with the error message up, the file is there. Must be a timing issue, yes?

 

[Norie]

I can't see how that would/could happen with the code I posted.

wbOpen.Worksheets refers to all the worksheets in the workbook.

Did you change the code I posted at all?

 

[ra_weiss]

The only code I'm changing is the directory structure, for privacy concerns. But I just double-checked and it's proper.