DIR not working after first workbook opened.
 

I have this snippet of code

myFile = Dir(myFolder & "*.xls")
Debug.Print myFile

Do While myFile < ""
On Error Resume Next
Application.AutomationSecurity = msoAutomationSecurityLow
Set oWB = Workbooks.Open(myFolder & myFile)
Application.AutomationSecurity = msoAutomationSecurityByUI

On Error GoTo 0
If Not oWB Is Nothing Then
'Do stuff
application.displayalerts = false
oWB.SaveAs myNewFolder & oWB.Name
Application.DisplayAlerts = True
oWB.Close

End If
myFile = Dir() '<~~~I get Run Time Error 5 here
Loop

I've done this before with no problem. What am I missing? FWIW, I've
declared all variables.

Thanks,
Barb Reinhardt

DIR not working after first workbook opened.
 

Never mind, I see the problem. I'm using DIR in another module. I'll have
to approach this another way.
Thanks,
Barb Reinhardt


"Barb Reinhardt" wrote:

I have this snippet of code

myFile = Dir(myFolder & "*.xls")
Debug.Print myFile

Do While myFile < ""
On Error Resume Next
Application.AutomationSecurity = msoAutomationSecurityLow
Set oWB = Workbooks.Open(myFolder & myFile)
Application.AutomationSecurity = msoAutomationSecurityByUI

On Error GoTo 0
If Not oWB Is Nothing Then
'Do stuff
application.displayalerts = false
oWB.SaveAs myNewFolder & oWB.Name
Application.DisplayAlerts = True
oWB.Close

End If
myFile = Dir() '<~~~I get Run Time Error 5 here
Loop

I've done this before with no problem. What am I missing? FWIW, I've
declared all variables.

Thanks,
Barb Reinhardt

DIR not working after first workbook opened.
 

I'd build an array of all the files first. Then process the files via that
array.

Barb Reinhardt wrote:

Never mind, I see the problem. I'm using DIR in another module. I'll have
to approach this another way.
Thanks,
Barb Reinhardt

"Barb Reinhardt" wrote:

I have this snippet of code

myFile = Dir(myFolder & "*.xls")
Debug.Print myFile

Do While myFile < ""
On Error Resume Next
Application.AutomationSecurity = msoAutomationSecurityLow
Set oWB = Workbooks.Open(myFolder & myFile)
Application.AutomationSecurity = msoAutomationSecurityByUI

On Error GoTo 0
If Not oWB Is Nothing Then
'Do stuff
application.displayalerts = false
oWB.SaveAs myNewFolder & oWB.Name
Application.DisplayAlerts = True
oWB.Close

End If
myFile = Dir() '<~~~I get Run Time Error 5 here
Loop

I've done this before with no problem. What am I missing? FWIW, I've
declared all variables.

Thanks,
Barb Reinhardt


--

Dave Peterson