Consulting

Results 1 to 10 of 10

Thread: Printing Macro

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location

    Printing Macro

    Is their a simple macro that will print files from a directory? I have 20 files I change on a daily basis and would like to have a macro I can call when I am done that will print all the files. I hope this can be done.

    Thanks....

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Hi shickles,

    For clarification, do you want to print ALL files in a specific directory? And if so, how will you specify that directory? Please explain so the coder's have a good idea of what your desired results are.

  3. #3
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location
    All the files are in one directory on our network. If I could be prompted for the directory that would be great. If I need to code the directory in I could do that as well.

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Try something like this:

    [VBA]
    Option Explicit

    Private Sub PtintAllFiles()

    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FileName As String
    Dim Path As String
    Dim Prompt As String
    Dim Title As String
    Dim MyResponse As VbMsgBoxResult

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    '*** Get folder from user ***

    Prompt = "Select the folder with the files that you want to print."
    Title = "Folder Selection"
    MsgBox Prompt, vbInformation, Title
    Application.FileDialog(msoFileDialogFolderPicker).Show
    Path = CurDir

    '*** Confirm the procedure before continuing ***

    Prompt = "Are you sure that you want to print all the files in the folder:" & _
    vbCrLf & Path & " ?"
    Title = "Confirm Procedure"
    MyResponse = MsgBox(Prompt, vbQuestion + vbYesNo, Title)
    If MyResponse = vbNo Then GoTo Canceled:

    '*** Print all Excel files in the specified directory ***

    FileName = Dir(Path & "\*.xls", vbNormal)
    Do Until FileName = ""
    Workbooks.Open Path & "\" & FileName
    Set WB = ActiveWorkbook
    For Each WS In WB.Worksheets
    WS.PrintOut
    Next
    WB.Close
    FileName = Dir()
    Loop

    Canceled:

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub
    [/VBA]

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Nice one Jake! The only thing I'd change (and I know, I'm no critic) is add a false after the close in your file open/print loop ...

    [vba] WB.Close False[/vba]

    This will not prompt you for printing. I realize the enable events have been turned off, but it's good habit (imho). Sorry to barge in.

  6. #6
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    Nice Jake...
    Just one question
    [VBA]
    Application.FileDialog(msoFileDialogFolderPicker).Show
    [/VBA]

    Am I missing a reference or is this only for XP ->
    (running win2000 and Office2000 at work)

    Br
    Tommy Bak

  7. #7
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Yeah, I believe it is Excel 2002 or later only. To do this with a previous version it is much more trickier. But with API we can do it. Credit for this next bit of code goes to Chip Pearson.

    [VBA]
    Option Explicit

    Private Const BIF_RETURNONLYFSDIRS As Long = &H1
    Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
    Private Const BIF_RETURNFSANCESTORS As Long = &H8
    Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
    Private Const BIF_BROWSEFORPRINTER As Long = &H2000
    Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
    Private Const MAX_PATH As Long = 260

    Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszINSTRUCTIONS As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
    End Type

    Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
    End Type

    Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
    ByVal pidl As Long, _
    ByVal pszBuffer As String) As Long
    Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
    lpBrowseInfo As BrowseInfo) As Long

    Function BrowseFolder(Optional Caption As String = "") As String

    Dim BrowseInfo As BrowseInfo
    Dim FolderName As String
    Dim ID As Long
    Dim Res As Long

    With BrowseInfo
    .hOwner = 0
    .pidlRoot = 0
    .pszDisplayName = String$(MAX_PATH, vbNullChar)
    .lpszINSTRUCTIONS = Caption
    .ulFlags = BIF_RETURNONLYFSDIRS
    .lpfn = 0
    End With
    FolderName = String$(MAX_PATH, vbNullChar)
    ID = SHBrowseForFolderA(BrowseInfo)
    If ID Then
    Res = SHGetPathFromIDListA(ID, FolderName)
    If Res Then
    BrowseFolder = Left$(FolderName, InStr(FolderName, _
    vbNullChar) - 1)
    End If
    End If

    End Function

    Sub GetFolder()

    Dim FName As String

    FName = BrowseFolder("Select A Folder")
    If FName = "" Then
    MsgBox "You didn't select a folder"
    Else
    MsgBox "You selected: " & FName
    End If

    End Sub

    [/VBA]

    Copy/Paste all of the code into a module and run the macro Sub GetFolder()

  8. #8
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Now I suppose I should combine my original code with this API method for getting the folder:

    [VBA]
    Option Explicit

    Private Const BIF_RETURNONLYFSDIRS As Long = &H1
    Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
    Private Const BIF_RETURNFSANCESTORS As Long = &H8
    Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
    Private Const BIF_BROWSEFORPRINTER As Long = &H2000
    Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
    Private Const MAX_PATH As Long = 260

    Type BrowseInfo
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszINSTRUCTIONS As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
    End Type

    Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
    End Type

    Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
    ByVal pidl As Long, _
    ByVal pszBuffer As String) As Long
    Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
    lpBrowseInfo As BrowseInfo) As Long

    Function BrowseFolder(Optional Caption As String = "") As String

    Dim BrowseInfo As BrowseInfo
    Dim FolderName As String
    Dim ID As Long
    Dim Res As Long

    With BrowseInfo
    .hOwner = 0
    .pidlRoot = 0
    .pszDisplayName = String$(MAX_PATH, vbNullChar)
    .lpszINSTRUCTIONS = Caption
    .ulFlags = BIF_RETURNONLYFSDIRS
    .lpfn = 0
    End With
    FolderName = String$(MAX_PATH, vbNullChar)
    ID = SHBrowseForFolderA(BrowseInfo)
    If ID Then
    Res = SHGetPathFromIDListA(ID, FolderName)
    If Res Then
    BrowseFolder = Left$(FolderName, InStr(FolderName, _
    vbNullChar) - 1)
    End If
    End If

    End Function

    Private Sub PtintAllFiles()

    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FileName As String
    Dim Path As String
    Dim Prompt As String
    Dim Title As String
    Dim MyResponse As VbMsgBoxResult

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    '*** Get folder from user ***

    Prompt = "Select the folder with the files that you want to print."
    Title = "Folder Selection"
    MsgBox Prompt, vbInformation, Title
    Path = BrowseFolder("Select A Folder")

    '*** Confirm the procedure before continuing ***

    Prompt = "Are you sure that you want to print all the files in the folder:" & _
    vbCrLf & Path & " ?"
    Title = "Confirm Procedure"
    MyResponse = MsgBox(Prompt, vbQuestion + vbYesNo, Title)
    If MyResponse = vbNo Then GoTo Canceled:

    '*** Print all Excel files in the specified directory ***

    FileName = Dir(Path & "\*.xls", vbNormal)
    Do Until FileName = ""
    Workbooks.Open Path & "\" & FileName
    Set WB = ActiveWorkbook
    For Each WS In WB.Worksheets
    WS.PrintOut
    Next
    WB.Close
    FileName = Dir()
    Loop

    Canceled:

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub
    [/VBA]

  9. #9
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location
    OKay, I am lost. Which one of these would I use for Excel 2003? How do I call the macro?

  10. #10
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Shickles,

    You are very lucky to have Jacob helping you. I admire his skills. What I did to be able to call the macro was change:

    [VBA]Private Sub PtintAllFiles()
    [/VBA]

    to

    [VBA]Sub PtintAllFiles()
    [/VBA]


    then you can call it normally or assign it to a button.
    The last code that Jacob posted worked for me in Excel 2000. I also noticed that it does not print files in sub directories which is probably what you want. Another thing, I found that you should not try to print the directory that the file with the print macro is in.

    I also commented out

    [VBA] 'WS.PrintOut[/VBA]
    and put in:

    [VBA]WS.PrintPreview[/VBA]
    for testing the script. You can change it back when you get it working.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

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