+ Reply to Thread
Results 1 to 10 of 10

Print files with macro in Order!

  1. #1
    Registered User
    Join Date
    09-15-2020
    Location
    Lithuania
    MS-Off Ver
    2010
    Posts
    7

    Print files with macro in Order!

    Hello,

    I am trying to make a team macro in Excel:
    1st Macro gets Folder Path by selecting location of folder.
    Then in excel, there are lines, that fill in the file names (for example all PDF.'s that are in the folder)
    2nd Macro is called Print Loop. It goes trough the array of file names, and sends them to the Printer to print.
    Macro works, but only problem:
    When printer prints, it mixes the sheets. It is a total chaos! Is it possible to send some feedback to macro from printer, or to make macro work so that it would keep the order of file named and printed files ?

    I attach the Macro excel file. Everything is under "Print_Files" sheet



    RE01.jpg
    Attached Files Attached Files
    Last edited by windzz; 09-30-2020 at 03:05 PM.

  2. #2
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,223

    Re: Print files with macro in Order!

    Quote Originally Posted by windzz View Post
    When printer prints, it mixes the sheets. It is a total chaos!
    I did not look at your file. however, you mean that the ORDER of the printing is different than the order that you sent each sheet to the printer? if that is what you mean, it really doesn't make much sense to me. when you send docs to a print queue, they *should* be logged in order, and then when the printer is ready to execute, print in the order that they came into the queue in. can you take a screenshot of the actual print queue on your machine? on a win machine, it is under devices and printers in the CP. right click on the printer you are using and select "print queue".
    -Adam (please don't call me "sir"!)

  3. #3
    Registered User
    Join Date
    09-15-2020
    Location
    Lithuania
    MS-Off Ver
    2010
    Posts
    7

    Re: Print files with macro in Order!

    Thank you for reply. I think there was something wrong with the code too. As the the sheet order that were combined in one PDF was also wrong. If you could take your time to look at the attached file?..
    Maybe there is some sort of simple syntax to make printer print order check, of fixed?

    I attach the Print Loop code here that I use:

    PHP Code: 
    Declare PtrSafe Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        ByVal hwnd 
    As LongPtr_
        ByVal lpOperation 
    As String_
        ByVal lpFile 
    As String_
        ByVal lpParameters 
    As String_
        ByVal lpDirectory 
    As String_
        ByVal nShowCmd 
    As Long_
        
    As Long

    Public Sub PrintFile(ByVal strPathAndFilename As String)
     
        
    Call apiShellExecute(Application.hwnd"print"strPathAndFilenamevbNullStringvbNullString0)
     
    End Sub
     
    Sub LoopandPrintFiles
    ()
        
    Dim i As Integer
        Dim FileName
    FullFileNamePathName As String
        
        PathName 
    Left((Cells(11).Value), Len((Cells(11).Value)) - 1)
      
        
    'Debug.Print PathName
        
    Dim r As Range
    Dim rCell As Range
        For Each r In Range("B3", "B102")
            If Not r.EntireRow.Hidden Then
                For Each rCell In r.Cells
                '
    Debug.Print r.Row
                FileName 
    rCell.Value '1st number is row, 2nd is collumn (B)
                FullFileName = PathName & FileName '
    ".pdf"
                
    PrintFile (FullFileName)
                
    Next rCell
                
            End 
    If
        
    Next r
                
    End Sub 
    Last edited by windzz; 09-30-2020 at 10:13 PM.

  4. #4
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,223

    Re: Print files with macro in Order!

    I have never used the shell execute dll file in Windows in any version of Windows for that matter. But it looks to me like you're doing everything correctly because you're looping through the cells and that should essentially throw every file into the queue in order that you specified and that you capture the file names for. So there shouldn't be any problem there. I really can't see anything wrong with any of the code you posted but I find it kind of comical that you actually used PHP tags instead of code tags here on the board. So kudos to you for finding the appropriate tags to color code work that you did lol.

    I can't really offer any more help here because I've already said that it looks just fine. The only thing I would suggest to get this straightened out is to download the freeware called dll viewer. there are a few different freeware programs on the internet on various websites that serve the same purpose and if you open those programs you can select library files of any kind for the VBA language like ocx lib dll and olb files. And the dll viewer program will tell you everything that's in those files in human readable form and not the garbled junk you usually see if you open a dll in notepad. So the libraries that I just referenced stand for ActiveX files dll files object libraries and type libraries. and those are all related to the VBA language for office programs. that's about all I have to offer you so hopefully it does you some good sorry.

  5. #5
    Registered User
    Join Date
    09-15-2020
    Location
    Lithuania
    MS-Off Ver
    2010
    Posts
    7

    Re: Print files with macro in Order!

    Thank you for help. I will search more forums. Maybe someone can help.

  6. #6
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,223

    Re: Print files with macro in Order!

    check out the other excel forum:

    www.mrexcel.com

    or another place where extremely competent people can be found:

    https://www.access-programmers.co.uk/forums/

    or their sister community:

    https://www.utteraccess.com/forum/

  7. #7
    Registered User
    Join Date
    09-15-2020
    Location
    Lithuania
    MS-Off Ver
    2010
    Posts
    7

    Re: Print files with macro in Order!

    Thank you vba_php!

  8. #8
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,223

    Re: Print files with macro in Order!

    well sure. this is why I exist, partially

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,285

    Re: Print files with macro in Order!

    I have never tried to print a pdf file from excel but apparently there is others who do and have some hiccups also.
    I was just going to show the code but would be stuck for an answer if one came.
    So, unfortunately, if it does not give you what you need, I can't help you any further.
    Good Luck though.

    Post #4 here has something.
    https://www.mrexcel.com/board/thread...el-vba.908121/

    and Post #2 here
    https://www.mrexcel.com/board/thread...script.999059/

    and Post #2 here
    https://www.mrexcel.com/board/thread...g-them.973716/

  10. #10
    Registered User
    Join Date
    09-15-2020
    Location
    Lithuania
    MS-Off Ver
    2010
    Posts
    7
    Thank you, jolivanes. I will definitelly check the links

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Print PDF files in a specific order using shell
    By DJohnny in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2020, 08:54 AM
  2. Need macro to print pages in a range in reverse order
    By luajambeiro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-29-2019, 09:47 AM
  3. Rename all files in the same folder then print in order?
    By HXIO in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-10-2018, 09:20 AM
  4. [SOLVED] print pages in specific order in vba macro
    By pongmeister in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-18-2016, 12:33 PM
  5. Print macro printing sheets in wrong order
    By j.farr3ll in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-08-2011, 09:12 AM
  6. Print order for range in macro
    By JanineLeMaster in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-16-2007, 05:51 PM
  7. Print Macro Needed - Specified Order of Worksheets.
    By iwgunter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts