Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
printing sheets in different files
this is perfect...
Some remarks though Files are opened only when entering a password (DBI in my case) can this be implermented too?? In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. Last but not least. Can I print workbooks in reverse order so no manual sorting is needed after the printing process? Thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Try this for the folder C:\Data Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) mybook.PrintOut mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... Hi, I have about 20 files containing te word "November" Each file has an avarage of 15 sheets. I want to print this all out... Is there a command to make the computer pick all files and print all sheets automatically without having to open the all individually??? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
printing sheets in different files
Hi Jean-Paul
Files are opened only when entering a password (DBI in my case) See the Tips on this page http://www.rondebruin.nl/copy3.htm In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. How many sheets have each workbook? Is this the same in every workbook? Last but not least. Can I print workbooks in reverse order so no manual Sort the files in the folder first -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... this is perfect... Some remarks though Files are opened only when entering a password (DBI in my case) can this be implermented too?? In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. Last but not least. Can I print workbooks in reverse order so no manual sorting is needed after the printing process? Thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Try this for the folder C:\Data Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) mybook.PrintOut mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... Hi, I have about 20 files containing te word "November" Each file has an avarage of 15 sheets. I want to print this all out... Is there a command to make the computer pick all files and print all sheets automatically without having to open the all individually??? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
printing sheets in different files
wokbooks can contain any number of sheets
With sorting I mean: in a workbook sheets are sorted alphabetically (tabs contain names)... so when printed it would be nice getting the sheets in reverse order. CU and thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Files are opened only when entering a password (DBI in my case) See the Tips on this page http://www.rondebruin.nl/copy3.htm In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. How many sheets have each workbook? Is this the same in every workbook? Last but not least. Can I print workbooks in reverse order so no manual Sort the files in the folder first -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... this is perfect... Some remarks though Files are opened only when entering a password (DBI in my case) can this be implermented too?? In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. Last but not least. Can I print workbooks in reverse order so no manual sorting is needed after the printing process? Thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Try this for the folder C:\Data Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) mybook.PrintOut mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... Hi, I have about 20 files containing te word "November" Each file has an avarage of 15 sheets. I want to print this all out... Is there a command to make the computer pick all files and print all sheets automatically without having to open the all individually??? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
printing sheets in different files
Try this
Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String Dim a As Integer SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) For a = mybook.Sheets.Count To 2 Step -1 mybook.Sheets(a).PrintPreview Next mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... wokbooks can contain any number of sheets With sorting I mean: in a workbook sheets are sorted alphabetically (tabs contain names)... so when printed it would be nice getting the sheets in reverse order. CU and thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Files are opened only when entering a password (DBI in my case) See the Tips on this page http://www.rondebruin.nl/copy3.htm In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. How many sheets have each workbook? Is this the same in every workbook? Last but not least. Can I print workbooks in reverse order so no manual Sort the files in the folder first -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... this is perfect... Some remarks though Files are opened only when entering a password (DBI in my case) can this be implermented too?? In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. Last but not least. Can I print workbooks in reverse order so no manual sorting is needed after the printing process? Thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Try this for the folder C:\Data Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) mybook.PrintOut mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... Hi, I have about 20 files containing te word "November" Each file has an avarage of 15 sheets. I want to print this all out... Is there a command to make the computer pick all files and print all sheets automatically without having to open the all individually??? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
printing sheets in different files
Change PrintPreview to PrintOut
-- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String Dim a As Integer SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) For a = mybook.Sheets.Count To 2 Step -1 mybook.Sheets(a).PrintPreview Next mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... wokbooks can contain any number of sheets With sorting I mean: in a workbook sheets are sorted alphabetically (tabs contain names)... so when printed it would be nice getting the sheets in reverse order. CU and thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Files are opened only when entering a password (DBI in my case) See the Tips on this page http://www.rondebruin.nl/copy3.htm In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. How many sheets have each workbook? Is this the same in every workbook? Last but not least. Can I print workbooks in reverse order so no manual Sort the files in the folder first -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... this is perfect... Some remarks though Files are opened only when entering a password (DBI in my case) can this be implermented too?? In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. Last but not least. Can I print workbooks in reverse order so no manual sorting is needed after the printing process? Thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Try this for the folder C:\Data Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) mybook.PrintOut mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... Hi, I have about 20 files containing te word "November" Each file has an avarage of 15 sheets. I want to print this all out... Is there a command to make the computer pick all files and print all sheets automatically without having to open the all individually??? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
printing sheets in different files
Perfect..
Just the password-thing remains to be completely perfect JP "Ron de Bruin" schreef in bericht ... Try this Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String Dim a As Integer SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) For a = mybook.Sheets.Count To 2 Step -1 mybook.Sheets(a).PrintPreview Next mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... wokbooks can contain any number of sheets With sorting I mean: in a workbook sheets are sorted alphabetically (tabs contain names)... so when printed it would be nice getting the sheets in reverse order. CU and thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Files are opened only when entering a password (DBI in my case) See the Tips on this page http://www.rondebruin.nl/copy3.htm In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. How many sheets have each workbook? Is this the same in every workbook? Last but not least. Can I print workbooks in reverse order so no manual Sort the files in the folder first -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... this is perfect... Some remarks though Files are opened only when entering a password (DBI in my case) can this be implermented too?? In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. Last but not least. Can I print workbooks in reverse order so no manual sorting is needed after the printing process? Thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Try this for the folder C:\Data Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) mybook.PrintOut mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... Hi, I have about 20 files containing te word "November" Each file has an avarage of 15 sheets. I want to print this all out... Is there a command to make the computer pick all files and print all sheets automatically without having to open the all individually??? Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
printing sheets in different files
Look in the link I posted Jean-Paul
Set mybook = Workbooks.Open(FNames, Password:="ron", WriteResPassword:="ron", UpdateLinks:=0) -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... Perfect.. Just the password-thing remains to be completely perfect JP "Ron de Bruin" schreef in bericht ... Try this Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String Dim a As Integer SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) For a = mybook.Sheets.Count To 2 Step -1 mybook.Sheets(a).PrintPreview Next mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... wokbooks can contain any number of sheets With sorting I mean: in a workbook sheets are sorted alphabetically (tabs contain names)... so when printed it would be nice getting the sheets in reverse order. CU and thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Files are opened only when entering a password (DBI in my case) See the Tips on this page http://www.rondebruin.nl/copy3.htm In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. How many sheets have each workbook? Is this the same in every workbook? Last but not least. Can I print workbooks in reverse order so no manual Sort the files in the folder first -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... this is perfect... Some remarks though Files are opened only when entering a password (DBI in my case) can this be implermented too?? In every workbook there is a kind of template (always sheet1). I don't need to print this sheet... can I ommit this. Last but not least. Can I print workbooks in reverse order so no manual sorting is needed after the printing process? Thanks JP "Ron de Bruin" schreef in bericht ... Hi Jean-Paul Try this for the folder C:\Data Sub TestFile1() Dim basebook As Workbook Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String SaveDriveDir = CurDir MyPath = "C:\Data" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" If FNames Like "*November*" Then Set mybook = Workbooks.Open(FNames) mybook.PrintOut mybook.Close False End If FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Jean-Paul De Winter" wrote in message ... Hi, I have about 20 files containing te word "November" Each file has an avarage of 15 sheets. I want to print this all out... Is there a command to make the computer pick all files and print all sheets automatically without having to open the all individually??? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Sheets | Excel Discussion (Misc queries) | |||
Printing XL Sheets name | Excel Discussion (Misc queries) | |||
PRINTING two sheets on ONE? | Excel Discussion (Misc queries) | |||
Printing sheets. | Excel Worksheet Functions | |||
Allocate Files to Sheets and Build a Master Sheet which Summarises All Sheets | Excel Programming |