Open Many Excel work books, (And more.....)

[Teebag180]

Hi Everyone.

I'm looking for some help if possible please.

I currently work for a university and the tutors are using excel spreadsheets to input attendance. Each week I need to create a copy of that weeks attendance and then hide the week I just copied so it can't be seen by students (to create a copy of the attendance - at the moment - I print each week/page as a PDF)

At the moment I only have about 20 Workbooks to deal with so it's not a major job however in a few months time the amount of workbooks will increase to around 200 across 4 courses

So ....to make this job something I can actually do with that many workbooks

I'm trying to find a Macro that will


open the workbook (either all worbooks in a specified folder, or some sort of option where I can select the folder that has the workbooks)
Print a specific page ie. Page1 (preferably as a PDF - BUT I can set the Default printer to PDF so as long as I can print a specific page I think that might help)
Hide a set area of columns (Ie Columns E - R )- This will change per week for each weeks attendance (So I will manually change the Code and the columns that need hiding)
Save the workbook
Close

(Then repeat this same process for each workbook in the folder)

I honestly don't know a great deal about macros so haven't tried anything yet - mainly because - the problem is - I'm not sure where to look.

If anyone can provide any help of where to get a specific macro that will do the above (or as close as possible) this would be greatly apprecated.

Even better would be something I can start with - but I'm not expecting you to do all the work so any guidance please.

Hopefully I've provided enough information but if there's anything I need to add/clarify please let me know.

Thanks in advance

[Alf]

Perhaps a macro like this?

This macro lets the user select a folder, it then loops through all files in this folder and if the file type is xlsx it will open this file and activate Sheet1 and save the used range as a pdf file to a folder i.e. “C:\Temp”

Changes that should be made depending where the data you are located i.e. Sheet1 or a sheet with a specific name. If workbook contains only 1 sheet this will be the active sheet when workbook is opened.

You may probably need to change the range setting and as I have no access to a printer you should record a macro when selecting a range and print that as a pdf document.

Post this macro and then it could be added to this macro.

Option Explicit

Sub ExtrData()
Dim MyFolder As String  'Store the folder selected by the user
Dim sFile As String  'The name of the file where data is selected for printing
Dim wk As Workbook
Dim pFile As String

On Error Resume Next
Application.ScreenUpdating = False

'Display the folder picker dialog box for user selection of directory
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Please select a folder"
    .Show
    .AllowMultiSelect = False
    If .SelectedItems.Count = 0 Then
        MsgBox "You did not select a folder"
        Exit Sub
    End If
    MyFolder = .SelectedItems(1) & "\"
End With

'Dir finds the files in the selected folder, to specify a specific filetype replace xlsx with proper file ending
sFile = Dir(MyFolder & "*.xlsx")

If sFile = "" Then
    MsgBox "No files matching set criteria found"
   Exit Sub
End If

Do While sFile <> ""
    Set wk = Workbooks.Open(MyFolder & sFile)
    Sheets("Sheet1").Activate
  
    pFile = Mid(sFile, 1, Len(sFile) - 4)
    ActiveSheet.UsedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Temp\" & pFile & ".Pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  
     wk.Close SaveChanges:=False
     
sFile = Dir()

 Loop
 
 
Application.ScreenUpdating = True

End Sub

Alf

[Teebag180]

WOW

Thank you for your quick response

This was a GREAT start.

I ran a couple of macros for - printing the page and - then hiding thecolumns I wanted to hide

then replaced the bit with 'saving to a specific folder' with those macros (thanks - that is great idea by the way - saving to a folder - but the folder I save the PDF's to would change every week so this way I can pick the folder I when saving)

The only thing I have to do is (try to remember) to set my default printer to PDF.

so far it works really well (i've only tested with 4 workbooks mind you so I'm going to try and make several copies of the workbook to test it out with larger numbers)

Should I post the final code for others? in case anyone ever has the same problem/query????

[Teebag180]

Update - just tried 20 workbooks

And works perfectly

[Alf]

Thank for feedback and rep

Should I post the final code for others? in case anyone ever has the same problem/query????

Yes please because I would also like to see the "print pdf part" as I don't have access to a printer.

If this solves your problem don't forget to mark this thread "Solved"

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

[Teebag180]

Hi

On my computer - in devices and printer I can select a PDF creater as the default printer - so I don't know how it would work for those that don't have this option. I guess if anyone didn't have that it might print out to a normal printer? Works well for me this way as I have a set 'print area' that is on every page.

here is the final code

Option Explicit

Sub ExtrData()
Dim MyFolder As String  'Store the folder selected by the user
Dim sFile As String  'The name of the file where data is selected for printing
Dim wk As Workbook
Dim pFile As String

On Error Resume Next
Application.ScreenUpdating = False

'Display the folder picker dialog box for user selection of directory
With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Please select a folder"
    .Show
    .AllowMultiSelect = False
    If .SelectedItems.Count = 0 Then
        MsgBox "You did not select a folder"
        Exit Sub
    End If
    MyFolder = .SelectedItems(1) & "\"
End With

'Dir finds the files in the selected folder, to specify a specific filetype replace xlsx with proper file ending
sFile = Dir(MyFolder & "*.xlsx")

If sFile = "" Then
    MsgBox "No files matching set criteria found"
   Exit Sub
End If

Do While sFile <> ""
    Set wk = Workbooks.Open(MyFolder & sFile)
    Sheets("Register").Activate
  
pFile = Mid(sFile, 1, Len(sFile) - 4)
    
   
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
        :=True, IgnorePrintAreas:=False
        
Columns("E:R").Select
Range("E3").Activate
 Selection.EntireColumn.Hidden = True
  
     wk.Close SaveChanges:=True
     
sFile = Dir()

 Loop
 
 
Application.ScreenUpdating = True

End Sub

Thanks again

[Alf]

You are welcome and thanks for the "print pdf part".

Good to know and a thing one could suggest with a warning that it's not sure it would work on every printer.

Alf