Endless looping through workbooks

[Giordano Bruno]

I have some code that loops through all workbooks in a folder. I have added a line of code that writes some text in each workbook and changed the line
"Savechanges = False" to "Savechanges = True". This seems to create a new file and after the code has looped through all workbooks, it starts again with the first workbook and continues indefinitely. Does anyone know a simple solution to this problem?

 

[PhilS2520]

Hello Giordano Bruno,

This seems to me a logical error in the looping mechanism.

If you would post the code, we can help better. But try and figure it out by setting break points and stepping through the code one line at a time. F9 and F8 Function keys.

 

[Giordano Bruno]

Hi Phil,

I've already located the problem line, "Savechanges = True". When set to false, the code loops through each file once. When set to True a new file is created and the same file is opened again. The loop is infinite.

Here is the code:

Sub EndlessLoop()
Dim sSourceFileName As String, WkChk As String
Dim sMasterFileName As String
Dim sFolder As String: sFolder = ActiveWorkbook.Path & ""
Dim sFileSpec As String: sFileSpec = sFolder & "*.*"
sSourceFileName = Dir(sFileSpec)
sMasterFileName = ActiveWorkbook.Name
Application.DisplayAlerts = False
WkChk = shtMaster.Range("WkChk").Value
Application.ScreenUpdating = False
'>Loop through files to locate current workbooks
Do While Len(sSourceFileName) > 0
If Left(sSourceFileName, 10) = WkChk Then
Workbooks.Open (sFolder & "" & sSourceFileName), UpdateLinks:=False
Workbooks(sSourceFileName).Sheets("4WLAH").Range("F8").Value = "Uploaded"
Application.DisplayAlerts = False
Workbooks(sSourceFileName).Close savechanges:=True
Application.DisplayAlerts = True
End If
sSourceFileName = Dir
Loop
End Sub

Sorry, but I couldn't set code tags.

 

[daverunt]

Hi,

I am not seeing that issue. It iterates through all the files in my selected folder then exits.

This is missing a backslash but that stops my code from running so that may be a typo in the post.

Dim sFolder As String: sFolder = ActiveWorkbook.Path &[COLOR="#FF0000"] "\"[/COLOR]

When you say a 'new file' is created is that literal?
If that were the case, I would expect a SaveAs box to appear each time, because the original files would have to be read-only.

 

[Giordano Bruno]

Many thanks for testing it daverunt. I have no idea what's happening. I found some code on the net that does something similar, but when I tried it out it looped for ever. When I wrote that it is creating a new file, that is only my interpretation of the situation to try to explain why it doesn't stop when it runs out of files. I'm using Office 365. Don't know if that has anything to do with it. I should test it on another computer perhaps.

 

[Fluff]

This line appears twice in your code

   Application.DisplayAlerts = False

Try removing both of them, what happens?

PS code tags are the # icon in the reply window

 

[RoryA]

If all else fails, I suggest you make an array of file names using Dir, then loop through that array processing each one.

 

[Giordano Bruno]

Thanks for your suggestions Fluff. Removing those lines doesn't improve the situation. Can you be a bit more specific about the code tags. I'm in the reply window right now, but for the life of me I can't see the # icon. I'm in "Advanced" and can see the "Post Icons", but although I've looked everywhere, there is no #.

Thanks RoryA. I've done that and the problem is solved. I had done a 'smoke and mirrors' job by identifying the name of the first opened file and terminating the loop when it came up again, but I was relying on VBA always giving the saved files a higher index than the unopened files, and that made me uncomfortable. Your suggestion is much safer. Thanks again.

 

[daverunt]

Press reply on bottom right.
There's a toolbar menu in the reply window and the # tag is last on the right.

 

[Giordano Bruno]

Hi daverunt. Thanks for that. I have pressed Reply and on the right I have "Reply", "Reply With Quote" and an Icon which says "Multi-Quote This Message" when I hover the 'hand' over it. At the bottom of the reply window I have "Post Quick Reply", "Go Advanced" and "Cancel". These are all outside the reply window. Nowhere can I see a "Toolbar Menu" inside or outside the reply window. I know how it drives people nuts when I post without code tags, but it also drives me nuts that the instructions for doing so don't work for me. Maybe there is a setting somewhere that will turn this feature on and off.