About Us

FSt1

glad to help,
regards
FSt1

"Dallas" wrote:

Perfect, works great. After I tested it I added an Input Box for the number
of files to cycle and it works great. Here is the code for the sake of the
thread.

Thanks alot!
Dallas

Sub FixQIshtsInFolder2()
Dim myPath As String
Dim c As Long 'counter
Dim n As Long
c = 0 'setup
myPath = InputBox("Path?")
n = InputBox("Number of files to cycle between printer checks.")
Dim fs, f, f1, fc
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(myPath)
Set fc = f.Files
For Each f1 In fc
If LCase(Right(Trim(f1.Name), 4)) = ".xls" Then
Workbooks.Open myPath & "\" & f1.Name
Application.Run "QIshtNoMsg" 'do whatever
End If
c = c + 1 'counting
If c = n Then 'checking the counting
MsgBox "go check the printer for paper" 'AHA!
c = 0
End If
Next
MsgBox "Folder Done!"
End Sub

"FSt1" wrote:

Opps. forgot to set the counter back to zero.
if c = 100 then 'checking the counting
msgbox "go check the printer" 'AHA! end if
c=0
end if

sorry.
regards
FSt1

"FSt1" wrote:

hi
you could add a counter with msgbox.
Sub FixQIshtsInFolder()
Dim myPath As String
dim c as long 'counter
c= 0 'setup
myPath = InputBox("Path?")
Dim fs, f, f1, fc
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(myPath)
Set fc = f.Files
For Each f1 In fc
If LCase(Right(Trim(f1.Name), 4)) = ".xls" Then
Workbooks.Open myPath & "\" & f1.Name
Application.Run "QIshtNoMsg" 'do whatever
End If
c= c+1 'counting
if c = 100 then 'checking the counting
msgbox "go check the printer" 'AHA!
end if
Next
MsgBox "Folder Done!"
End Sub

regards
FSt1

"Dallas" wrote:

I have a macro that cycles through each file in a folder and makes some
changes, prints, saves and then closes one at a time. Some of the folders I
need to run this code on have a very large number of files in them and the
printer will with out a doubt run out of paper before the folder is finished.
I would like to have the macro pause after 100 cycles so I can check the
printer for paper and click OK on a message box to continue. Here is the code
I am currently using. I would also like have the macro promt to select the
right folder with a browser box rather than an input box. I tried using
msoFileDialogFolderPicker without much success.

Thanks,
Dallas

Sub FixQIshtsInFolder()
Dim myPath As String
myPath = InputBox("Path?")
Dim fs, f, f1, fc
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(myPath)
Set fc = f.Files
For Each f1 In fc
If LCase(Right(Trim(f1.Name), 4)) = ".xls" Then
Workbooks.Open myPath & "\" & f1.Name
Application.Run "QIshtNoMsg" 'do whatever
End If
Next
MsgBox "Folder Done!"
End Sub

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do you change seconds to cycles?	Eric	Excel Worksheet Functions	2	March 1st 10 10:36 PM
Stop / Pause a Macro using Pause button	scott56hannah	Excel Programming	0	June 27th 08 12:48 PM
if Error, Pause or Loop	ca1358	Excel Programming	0	November 29th 07 08:18 PM
Loop routine fails after 10 cycles......	Tom	Excel Programming	4	January 12th 06 03:45 AM
[HELP] How to use command buttons in cycles	C.F.	Excel Programming	3	June 2nd 05 01:40 PM

Menu

About Us