Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro recently quit working
I have a macro which Has been in use for several months. Recently it failed
so I started up the editor and step through the program manually. it worked like it always did. Tried running in auto the same problem occured. I then added a breakpoint. It stopped at breakpoint and I hit F5 again it completed without a hitch. I added some do loops with a Beep and I am hearing the Beeps before some previous steps are finished. The command before the loop is "fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More Stat files To Combine/process", , True)" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro recently quit working
I do not recognize the 1 as a valid part of the syntax.
"jimjk" wrote in message ... I have a macro which Has been in use for several months. Recently it failed so I started up the editor and step through the program manually. it worked like it always did. Tried running in auto the same problem occured. I then added a breakpoint. It stopped at breakpoint and I hit F5 again it completed without a hitch. I added some do loops with a Beep and I am hearing the Beeps before some previous steps are finished. The command before the loop is "fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More Stat files To Combine/process", , True)" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro recently quit working
In what way is it failing: does it raise an error or just not do what you
expect it to ? You should post a more complete set of code: it's not possible from your description to say what the problem might be. Tim "jimjk" wrote in message ... I have a macro which Has been in use for several months. Recently it failed so I started up the editor and step through the program manually. it worked like it always did. Tried running in auto the same problem occured. I then added a breakpoint. It stopped at breakpoint and I hit F5 again it completed without a hitch. I added some do loops with a Beep and I am hearing the Beeps before some previous steps are finished. The command before the loop is "fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More Stat files To Combine/process", , True)" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro recently quit working
Try...........
Dim intTemp As Integer Dim vartemp As Variant vartemp = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", _ FilterIndex:=1, Title:="<Your Title", MultiSelect:=True) If IsArray(vartemp) Then For intTemp = 1 To UBound(vartemp) Debug.Print vartemp(intTemp) Next End If If this post helps click Yes --------------- Jacob Skaria "jimjk" wrote: I have a macro which Has been in use for several months. Recently it failed so I started up the editor and step through the program manually. it worked like it always did. Tried running in auto the same problem occured. I then added a breakpoint. It stopped at breakpoint and I hit F5 again it completed without a hitch. I added some do loops with a Beep and I am hearing the Beeps before some previous steps are finished. The command before the loop is "fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More Stat files To Combine/process", , True)" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro recently quit working
Thanks for replying:
Based on help files: FileFilter This string passed in the FileFilter argument consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€”text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". and then you add the filter index FilterIndex Optional Variant Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. The issue on this is why it quit working when it work for several months in Office 2007 and it works if I single step (F8) through the program or even if I add a break at if isempty and the hit the f5 key to continue it works fine and finishes the program. at one point I added the following code For I = 1 To 30 ' Loop 30 times. 'Beep ' Sound a tone. Next I just after the getopenfilename command and I heard the beep prior to responding to getopen gui. Sub CombineMultFiles() ' CombMultFiles() loads selected text files from disk and calls ' Comb_1 to add the Data (2) worksheet from each file individually ' ' ' Application.ScreenUpdating = False 'Global initialization HeadLine = "FirstOne" Checkfilenum = 0 ' Check to see if values were set by add data routine ' and the program called from there If IsEmpty(TotalRows) Then '<<<<<<<<<< added break here TotalRows = 0 NDresults = 1 End If ' Standard routine for requesting files Dim fn As Variant, f As Integer ' change according to need .xls vs .txt as weel as communication information ' See append data below for use on Excel files fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More data files To Combine/process", , True) ' added Beep code here to check for running sequence I heard the beep prior ' to responding to the getopenfilename command. If TypeName(fn) = "Boolean" Then Exit Sub For f = 1 To UBound(fn) Workbooks.OpenText Filename:=fn(f) _ , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _ :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:= _ False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1) ' Start of routines that vary for individual collection ThisLine = Range("A6").Value ' file is opened but never ran this line Call Comb_1 Next f Again thank you! "JLGWhiz" wrote: I do not recognize the 1 as a valid part of the syntax. "jimjk" wrote in message ... I have a macro which Has been in use for several months. Recently it failed so I started up the editor and step through the program manually. it worked like it always did. Tried running in auto the same problem occured. I then added a breakpoint. It stopped at breakpoint and I hit F5 again it completed without a hitch. I added some do loops with a Beep and I am hearing the Beeps before some previous steps are finished. The command before the loop is "fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More Stat files To Combine/process", , True)" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro recently quit working
Thank you for replying!
Since I am new at posting I am not certain if I should reply in General or individually but here goes. When I put breakpoint at the if statement and continued hitting F8 it worked fine it also completed normally if I hit F5 but failed without the breakpoint. When I added this beep code For I = 1 To 30 ' Loop 3 times. Beep ' Sound a tone. Next I right after 2nd End if I heard Beep in auto mode prior to completion of getfilename gui. the complete subroutine is: Public HeadLine As String Public ThisLine As String Public Checkfilenum Public TotalRows Public NDresults Sub CombineMultFiles() ' CombMultFiles() loads selected text files from disk and calls ' Comb_1 to add the Data (2) worksheet from each file individually ' ' ' Application.ScreenUpdating = False 'Global initialization HeadLine = "FirstOne" Checkfilenum = 0 ' Check to see if values were set by add data routine ' and the program called from there If IsEmpty(TotalRows) Then ' <<<<breakpoint for f5 continue of f8 step TotalRows = 0 NDresults = 1 End If ' Standard routine for requesting file names Dim fn As Variant, f As Integer fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More Stat files To Combine/process", , True) If TypeName(fn) = "Boolean" Then Exit SubIf TypeName(fn) = "Boolean" Then Exit Sub End if '<<<<beep code was tried here For f = 1 To UBound(fn) Workbooks.OpenText Filename:=fn(f) _ , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _ :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:= _ False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1) ' ********** file did open *********** ThisLine = Range("A6").Value ' added breakpoint here but it never got here. Application.DisplayAlerts = False Call Comb_1 Next f Workbooks("dresults.xls").Activate newfilename = "combinedfiles" + Str(NDresults) + ".xls" ActiveWorkbook.SaveAs Filename:=newfilename, FileFormat:=xlNormal Application.ScreenUpdating = True If Checkfilenum 0 Then errmes = "There was a total of " + Str(Checkfilenum) + " files that failed." MsgBox (errmes) End If If NDresults 1 Then errmes = "There was a total of " + Str(NDresults) + " combinedfiles created from this data." MsgBox (errmes) End If For I = 1 To 30 ' Loop 3 times. Beep ' Sound a tone. Next I End Sub "Tim Williams" wrote: In what way is it failing: does it raise an error or just not do what you expect it to ? You should post a more complete set of code: it's not possible from your description to say what the problem might be. Tim "jimjk" wrote in message ... I have a macro which Has been in use for several months. Recently it failed so I started up the editor and step through the program manually. it worked like it always did. Tried running in auto the same problem occured. I then added a breakpoint. It stopped at breakpoint and I hit F5 again it completed without a hitch. I added some do loops with a Beep and I am hearing the Beeps before some previous steps are finished. The command before the loop is "fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More Stat files To Combine/process", , True)" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro recently quit working
Thank you for trying again the issue is whyit works with f5 or f8 with breakpoint but not in auto? Since this code is what i have been using (excpt for debug portions) it reacted the same way when I add it to my file. this portion worked but then failed just like mine did in auto after the 2nd open. Public HeadLine As String Public ThisLine As String Public Checkfilenum Public TotalRows Public NDresults Sub CombineMultFiles() ' CombMultFiles() loads selected text files from disk and calls ' Comb_1 to add the Data (2) worksheet from each file individually ' ' ' Application.ScreenUpdating = False 'Global initialization HeadLine = "FirstOne" Checkfilenum = 0 ' Check to see if values were set by add data routine ' and the program called from there If IsEmpty(TotalRows) Then ' <<<<breakpoint for f5 continue of f8 step TotalRows = 0 NDresults = 1 End If ' Standard routine for requesting file names Dim fn As Variant, f As Integer fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More Stat files To Combine/process", , True) If TypeName(fn) = "Boolean" Then Exit SubIf TypeName(fn) = "Boolean" Then Exit Sub End if '<<<<beep code was tried here For f = 1 To UBound(fn) Workbooks.OpenText Filename:=fn(f) _ , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _ :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:= _ False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1) ' ********** file did open *********** ThisLine = Range("A6").Value ' added breakpoint here but it never got here. Application.DisplayAlerts = False Call Comb_1 Next f Workbooks("dresults.xls").Activate newfilename = "combinedfiles" + Str(NDresults) + ".xls" ActiveWorkbook.SaveAs Filename:=newfilename, FileFormat:=xlNormal Application.ScreenUpdating = True If Checkfilenum 0 Then errmes = "There was a total of " + Str(Checkfilenum) + " files that failed." MsgBox (errmes) End If If NDresults 1 Then errmes = "There was a total of " + Str(NDresults) + " combinedfiles created from this data." MsgBox (errmes) End If For I = 1 To 30 ' Loop 3 times. Beep ' Sound a tone. Next I End Sub "Jacob Skaria" wrote: Try........... Dim intTemp As Integer Dim vartemp As Variant vartemp = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", _ FilterIndex:=1, Title:="<Your Title", MultiSelect:=True) If IsArray(vartemp) Then For intTemp = 1 To UBound(vartemp) Debug.Print vartemp(intTemp) Next End If If this post helps click Yes --------------- Jacob Skaria "jimjk" wrote: I have a macro which Has been in use for several months. Recently it failed so I started up the editor and step through the program manually. it worked like it always did. Tried running in auto the same problem occured. I then added a breakpoint. It stopped at breakpoint and I hit F5 again it completed without a hitch. I added some do loops with a Beep and I am hearing the Beeps before some previous steps are finished. The command before the loop is "fn = Application.GetOpenFilename("Text Files (*.txt),*.txt", 1, "Select One Or More Stat files To Combine/process", , True)" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
functions quit working | Excel Worksheet Functions | |||
Working with most recently used colors | Excel Programming | |||
Hyperlinks quit working | Excel Discussion (Misc queries) | |||
calculations quit working | Excel Discussion (Misc queries) | |||
Solver Macro quit working | Excel Programming |