Macro recently quit working

[jimjk]

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)"

 

[JLGWhiz]

I do not recognize the 1 as a valid part of the syntax.

 

[Tim Williams]

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

 

[Jacob Skaria]

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

 

[jimjk]

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!

 

[jimjk]

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

 

[jimjk]

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