• Printing out range of documents (2000)

    • This topic has 34 replies, 4 voices, and was last updated 19 years ago.
    Author
    Topic
    #432081

    Is is possible to print out a range of documents, without actually opening the documents, as this is very time consuming.

    What l would like to do is to open the range of documents in the screen shot without actually opening the documents themselves.

    Viewing 0 reply threads
    Author
    Replies
    • #1012865

      > What l would like to do is to open the range of documents in the screen shot without actually opening the documents themselves

      Huh? How can you open documents without opening them?

      If you want to print them, you could use VBA code. Put the following code at the beginning of a module:

      Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
      (ByVal hwnd As Long, _
      ByVal lpOperation As String, _
      ByVal lpFile As String, _
      ByVal lpParameters As String, _
      ByVal lpDirectory As String, _
      ByVal nShowCmd As Long) As Long

      Private Const SW_SHOWNORMAL = 1

      Public Sub PrintOneFile(fn As String)
      Dim lngResult As Long
      lngResult = ShellExecute(0&, "Print", fn, 0&, 0&, SW_SHOWNORMAL)
      If lngResult <= 32 Then
      MsgBox "Something went wrong.", vbExclamation
      End If
      End Sub

      Sub PrintFiles()
      Dim lngRow As Long
      For lngRow = 3 To 13
      PrintOneFile Range("C" & lngRow)
      Next lngRow
      End Sub

      The macro to run is PrintFiles. Change the bounds 3 and 13 and the column “C” to what you need.

      • #1012873

        Can you explain in a bit more details, as to what you mean?

        i.e. how do l access the module and where the coding goes in the module?

        • #1012874

          From Excel, select Tools | Macro | Visual Basic Editor.
          Then select Insert | Module to create a new module.
          Copy the code I posted, and paste it into the module.
          Change the PrintFiles macro to match your situation. For example, if the file names are in rows 10 through 50, change the line

          For lngRow = 3 To 13

          to

          For lngRow = 10 To 50

          And if the file names are in column M, change the line

          Range(“C” & lngRow)

          to

          Range(“M” & lngRow)

          When done, switch back to Excel. You can run the PrintFiles macro as follows:
          Select Tools | Macro | Macros…
          Click on PrintFiles in the list of macros.
          Click Run.

          You can also assign the macro to a command button from the Forms toolbar, etc.

          • #1013091

            I have tried this and get an error, see attachment.

            • #1013094

              The code has been messed up by your browser. It should look like the screenshot below.

            • #1013096

              I have resolved this l am now getting the following error.
              see attachment.

            • #1013099

              That probably means that there is no file with the specified path/name.

            • #1013100

              Can you tell me where in the file l edit the path name , as l cannot see where to edit this in the coding.

            • #1013102

              Justin,

              This isn’t going to work. With all due respect, I think you should take courses in Excel and Excel VBA before continuing.

            • #1013105

              Is there any way you can tell me where the path name goes in this coding.
              I think l should be able to work it out from there.
              As this macro would really really help me a lot.

              I agree l need to take a course in VBA.

            • #1013107

              The path name is not in the coding, it is in the spreadsheet (eg like in your first screenshot), the code just looks in your worksheet.

              Either the pathname and file is incorrect, or you have the code looking in the wrong location.

              Steve

            • #1013108

              Thanks for that, I am still strugging slightly but feel it is something very simple, but cannot see what it is.
              Can you tell me where l could possibly look?

            • #1013109

              Justin

              Take the hint buddy, Hans has stated in his messages to you that the cell has the hyperlink reference to your document. The code loops through your list in the spreadsheet and then prints it off…. The code works, I have played with it and it works…the problem is your end…it appears your references to the documents are wrong…….The admin guys here are fantastic but they are not clairvoyants and magicians

            • #1013112

              Jerry,

              I cannot work out where the references to the document are?
              The module appears to be in the correct spreadsheet, l am a little confused as to where l need to look.
              Would a screenshot be of assistance.

            • #1013113

              NO…Get the references to your documents correct in your excel worksheet first and stop relying on people in the Lounge to write all your code for you, it will be the only way you are going to learn

            • #1013126

              In your first screenshot they are the 3d column.

              These need to be correct. In the code that loops thru the rows, the columns and rows need to be setup to read these cells. In the example:

                  For lngRow = 3 To 13
                      PrintOneFile Range("C" & lngRow)
                  Next lngRow

              It will look in C3:C13. the column and the rows must be changed to match where the filenames in the sheet are.

              Steve

            • #1013128

              Can you give me an example of what you mean? This would be much appreciated.

            • #1013131

              Create a file on YOUR PC, call jj1234.doc and place it on your c:drive so tha its path is C:jj1234.doc

              In your excel file create a hyperlink to that document and run the code, now see if it works

            • #1013132

              Still no luck,the message “Something is wrong” still appears.
              Any ideas as to what l am doing wrong?

            • #1013133

              Right simplest thing to do is strip out any sensitive data and send the whole file to the Lounge for us to look at (or go into my control panel and send it to the advertised email address, if you do PM me so that I can open that mail box)

              I will have a look at the code and what you have done…we are working in the dark and cannot work with screen dumps

            • #1013135

              Any help you can provide will be much appreciated.

              I have zipped up the excel spreadsheet l am using removing any confidential data.

            • #1013144

              Right, I think I have found the problem. Thi sis the code.

              Private Declare Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” _
              (ByVal hwnd As Long, _
              ByVal lpOperation As String, _
              ByVal lpFile As String, _
              ByVal lpParameters As String, _
              ByVal lpDirectory As String, _
              ByVal nShowCmd As Long) As Long

              Private Const SW_SHOWNORMAL = 1

              Public Sub PrintOneFile(fn As String)
              Dim lngResult As Long
              lngResult = ShellExecute(0&, “print”, fn, 0&, 0&, SW_SHOWNORMAL)

              End Sub

              Sub PrintFiles()
              Dim lngRow As Long
              For lngRow = 2 To 3
              PrintOneFile Range(“I” & lngRow)
              Next lngRow
              End Sub

              Place this in a module as Hans explained. You were looking for documents in Column C and not I where they were. Your hyperlink TEXT should read the EXACT location of the document ie c:jj1234.txt I have set the range to print hyperlinks in I2 and I3 at the moment but you will have to change this yourself.

              whisperThis DOES work

            • #1013151

              I have tried to put in the correct pathname, but for some reason my coding is invalid, this is the coding:

              For lngRow = G:ProgCoordMemSeaKing.xlsJ:759 To G:ProgCoordMemSeaKing.xlsJ:759

              Can you tell me what l am doing wrong with the above path name? That would be much appreciated.

            • #1013154

              dizzy….I need to lie down in a darkened room

              I started to write a great long winded response to you and then realised that you are a complete wind up merchant……

              Just copy my code into your workbook and put G:ProgCoordMemSeaKing.xls in cell I2 and I3 and run the code……

              This is the last time I am going to correspond with you on this matter as I personally think you are taking the censored…JUST follow the instructions or go off and have some VBA classes.

            • #1013155

              The pathnames go into the cells I2 and I3 not into the code. the code reads the paths from the cells in the sheet.

              Steve

            • #1013235

              Thanks for clayfying that for me.

              I fully understand this, It is going to be a very long winded process to change all these file names.
              This will make it even longer what l am trying to acheive.
              If you see the attached screenshot you will see the pathname of one of the files.

            • #1013242

              I don’t understand.

              If the filenames are not in the cell, where are they going to be located for the code to get?

              Steve

            • #1013289

              Sorry , l am a bit confused. The file names are located in the cell, they are located on the network drive.

              But the sounds of it what l am trying to acheive is not possible?

            • #1013293

              I promised myself not to respond to you over this matter but I have given in as this thread has been answered. I have attached a modified excel workbook with the code. This DOES work and if it does not work your end there is nothing more we can do for you.

              The hyperlink in the cell is addressed to the one you gave us……so if it does not work adapt it to a file that is on your G Drive

            • #1013296

              I tested it with the a drive letter (C, G, Etc) and also network designations (moxau1path, etc) and both worked.

              I would check your path and file names

              Steve

            • #1013362

              I have tried to to let drives c and it works but it does work on a network drive when l use the path name

              leopardgenhums….

              Can you tell me what l am doing wrong?

            • #1013383

              I don’t know. It seems that you do not have the correct drive names.

              Steve

            • #1013385

              Can this be resolved in any way? Maybe l could change the path names some how?

            • #1013423

              As I said, I don’t know what the issue is. When I tested it with drive letters and network paths, I did not have any problems.

              If it works with drive letters and not network paths, I suggest, editing them all (you could use find/replace) to change all of them.
              Steve

    Viewing 0 reply threads
    Reply To: Printing out range of documents (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: