By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,222 Members | 1,610 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,222 IT Pros & Developers. It's quick & easy.

AC2002: Command button batch printing of .xls file (with macros)

rsmccli
P: 52
Hello. Using Access 2002. I have set up a command button on a form that will print off a list of hyperlinked documents that resides in one of our tables. I have been using code borrowed from Graham R Seach's site which identifies the file type, opens the document in its correct program, and prints it:
Expand|Select|Wrap|Line Numbers
  1. Public Const SW_HIDE = 0
  2. Public Const SW_MINIMIZE = 6
  3. Public Const SW_RESTORE = 9
  4. Public Const SW_SHOW = 5
  5. Public Const SW_SHOWMAXIMIZED = 3
  6. Public Const SW_SHOWMINIMIZED = 2
  7. Public Const SW_SHOWMINNOACTIVE = 7
  8. Public Const SW_SHOWNA = 8
  9. Public Const SW_SHOWNOACTIVATE = 4
  10. Public Const SW_SHOWNORMAL = 1
  11.  
  12. Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
  13.     (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
  14.     ByVal lpParameters As String, ByVal lpDirectory As String, _
  15.     ByVal nShowCmd As Long) As Long
  16.  
  17. Public Sub ExecuteFile(sFileName As String, sAction As String)
  18.     Dim vReturn As Long
  19.     'sAction can be either "Open" or "Print".
  20.  
  21.     If ShellExecute(Access.hWndAccessApp, sAction, sFileName, vbNullString, "", SW_SHOWNORMAL) < 33 Then
  22.         DoCmd.Beep
  23.         MsgBox "File not found."
  24.     End If
  25. End Sub
... along with this code on my command button:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintAllDocs_Click()
  2. On Error GoTo ErrorHandler
  3.  
  4.   Dim db As DAO.Database
  5.   Dim rs As DAO.Recordset
  6.   Dim strDocumentPath As String
  7.  
  8.   Set db = CurrentDb
  9.   Set rs = db.OpenRecordset("qryScopeHyperlinks")
  10.  
  11.  
  12.   With rs
  13.     rs.MoveFirst
  14.     Do Until rs.EOF
  15.       If rs.Fields("lngScopeIDLink") = WhatRecord(Forms.frmScope.CurrentRecord) Then
  16.        strDocumentPath = Mid(rs.Fields("hyplink"), 1, (InStr(rs.Fields("hypLink"), "#") - 1))
  17.       Call ExecuteFile(strDocumentPath, "Print")
  18.         Else
  19.         End If
  20.     rs.MoveNext
  21.     Loop
  22.     End With
  23.  
  24.       rs.Close
  25.       Set rs = Nothing  ......
This works well for almost all file types I have encountered (including normal .xls files). The only problem I am running into is Exel files that have embedded macros. When these are run manually, a prompt asking whether to enable or disable macros comes up.

So basically what I would like to do is have my code somehow acknowledge the popup or bypass it, then have excel print the file in its entirety (including all tabs). I was thinking maybe to put
Expand|Select|Wrap|Line Numbers
  1.  If Right(sFileName, 4) = ".xls" Then
  2.  
followed by some code to manually open/print all, but I am somewhat at a loss at how to do this. I think you may have to go into the registry and find the print command associated with excel, but not really sure past that. I would appreciate any ideas. Thanks.

rsmccli
Feb 4 '08 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
The only way to do this is to lower the security settings in Excel. This can be dangerous though if you are opening files from unknown sources.

You can go to Tools - Macros - Security and set the security level to low.
Feb 5 '08 #2

rsmccli
P: 52
The only way to do this is to lower the security settings in Excel. This can be dangerous though if you are opening files from unknown sources.

You can go to Tools - Macros - Security and set the security level to low.
Roger. Our global security settings on Excel prevent users from changing their security settings from "Medium", but thank you for your answer.
Feb 5 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.