473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

rsmccli
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
2 1828
MMcCarthy
14,534 Expert Mod 8TB
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
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

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

Similar topics

8
by: Joe | last post by:
I'm using Python 2.4 on Windows XP SP2. I'm trying to receive a command line argument that is a newline (\n) Here is the command line to use sample.py "\n" Here is a sample.py script
2
by: Herb Stevenson | last post by:
Hello all. I need to set up a batch file to work w/ the Visual Studio.NET 2003 command prompt. However, when I run the batch file it uses the standard command prompt. Is there a way to...
2
by: Darren | last post by:
Hi, I have a command button which has a macro running in it. The macro on the click event, runs to ensure that certain values in a form are valid, (e.g. the textboxes are not null) and...
2
by: eric1025 | last post by:
I have a form with a command button. When the command button is clicked, it runs a batch file via the Shell command. The batch file is run with a few variables coming from the database such as %1...
2
by: Senthil | last post by:
Hi All I need to create an Excel report and create a command button and have to run a macro on the click event that will print all the pages in the Excel workbook. I am able to create the report...
3
by: Nixeh | last post by:
Hi guys, ive been using access on and off now for about 3 years but im currently working on a database for work. Ive got the database all sorted and is currently storing 1000records. Ive created a...
9
by: larryimic | last post by:
I have created a Access production database that records good parts and bad parts to a table thru querys using macros and command buttons on a form. A report (part label) is printed each time a...
16
by: Steve | last post by:
I am working on a database that has a main menu, many sub-menus and some sub-sub-menus. They are all forms that have numerous command buttons on them to open forms and reports in the database. The...
1
by: eHaak | last post by:
A couple years ago, I built a database in MS Access 2003. I built the form using macros in some of the command buttons, and now I’m trying to eliminate the macros and just use visual basic code. ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.