I'm confused on whether/how much of the following 3 macros will need revision to work in both 32- and 64-bit Excel 2010 & later. If revision is necessary, please suggest conditional wording to accomplish this!(These macros provide for users to save evaluation results on the desktop in PDF, XPS or WORD, send to an installed printer's queue... or - if all else fails - take screen shots[!])
Judy
Code
Option Explicit
Public FunctionGetDesktop() As String
GetDesktop =CreateObject("WScript.Shell").SpecialFolders("Desktop")& Application.PathSeparator
End Function
--------------------
Sub PrintThis()
IfRange("G16") = 2 Then
Application.Run("C.xlsm!ElecCopy")
ElseIfRange("G16") = 3 Then
On Error Resume Next
Sheets("PRINT THIS!").Select
ActiveWindow.SelectedSheets.PrintOutFrom:=1, To:=2, Copies:=1, Collate:=True, IgnorePrintAreas:=False
If Err = 0 Then
Sheets("VERIFY TASK").Visible= True
Sheets("CLAIM CHECK").Visible= False
Sheets("PRINT THIS!").Visible= False
ActiveWorkbook.Saved = True
Application.Quit
Else: MsgBox "No printer installed.Click OK, take screen shots then click end/exit"
End If
End If
End Sub
---------------------------------------
Sub ElecCopy()
Dim Msg, Style, Response
Dim FName As Variant
Dim DTAddress As String
Sheets("Verify Task").Visible =False
Sheets("Calibration").Visible =False
DTAddress = GetDesktop
ChDir DTAddress
FName =Application.GetSaveAsFilename(FileFilter:="PDF files, *.pdf",Title:="Export to PDF")
On Error Resume Next
If FName <> False Then
ActiveWorkbook.ExportAsFixedFormatType:=xlTypePDF, Filename:=FName _
, Quality:=xlQualityStandard,IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
If Err = 0 Then
Application.ScreenUpdating = False
Sheets("Verify Task").Visible= True
Sheets("Claim Check").Visible= False
Sheets("Print This!").Visible= False
ActiveWorkbook.Saved = True
Application.Quit
ElseIf Err <> 0 Then
DTAddress = GetDesktop
ChDir DTAddress
FName =Application.GetSaveAsFilename(FileFilter:="XPS files, *.xps",Title:="Export to XPS")
On Error Resume Next
If FName <> False Then
ActiveWorkbook.ExportAsFixedFormatType:=xlTypeXPS, Filename:=FName, Quality:=xlQualityStandard,IncludeDocProperties:=True, IgnorePrintAreas :=False, OpenAfterPublish:=True
If Err = 0 Then
Application.ScreenUpdating = False
Sheets("Verify Task").Visible= True
Sheets("Claim Check").Visible= False
Sheets("Print This!").Visible= False
ActiveWorkbook.Saved = True
Application.Quit
ElseIf Err <> 0 Then
Msg = "ERROR! Neither a PDF nor an XPSdocument can be created." & vbNewLine & "Do you want to saveresults as a Word document?"
Style = vbYesNo + vbCritical +vbDefaultButton1
Response = MsgBox(Msg, Style)
If Response = vbYes Then
Application.Run"C.xlsm!SavDoc"
ElseIf Response = vbNo Then
Msg = "Click OK to exitthen select 'Send to Printer' as the save method"
Style = vbOKOnly
Range("G16").Value =1
End If
End If
End If
End If
End If
End Sub
-------------------------------------------
Sub SavDoc()
' SavDoc Macro
'
Dim wdApp As Object
Dim wdDoc As Object
Application.ScreenUpdating= False
Range("Print_Area").Select
Selection.CopyPictureAppearance:=xlScreen, Format:=xlPicture
ActiveSheet.ProtectPassword:="spike"
On Error Resume Next
Set wdApp = GetObject(,"Word.Application")
If Err <> 0 Then Set wdApp =CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add
wdApp.Visible = True
wdDoc.ActiveWindow.Selection.Paste
Sheets("Claim Check").Activate
Range("Print_Area").Select
Selection.CopyPicture Appearance:=xlScreen,Format:=xlPicture
ActiveWindow.WindowState = xlMinimized
wdDoc.ActiveWindow.Selection.Paste
wdDoc.ActiveWindow.LargeScroll Up:=6
wdDoc.WindowState = xlMaximized
Application.ScreenUpdating = True
Set wdDoc = Nothing
Set wdApp = Nothing
wdApp.Quit
ActiveWorkbook.Saved = True
Application.Quit
End Sub
Display More