TMP file not found error when creating multiple workbooks in a loop

[rjclark]

Hi all. I'm new so please show some grace and my apologies for any failure to follow forum conduct...

I have done my best to research this and can find the topic, but no solution:
I have a macro that loops through some code that:
updates fields on a sheet
opens a new book
copies the sheet into the new book
saves and closes the new book
opens a new email in outlook
attached the new book

This code will loop a few times (not a consistent number), then usually errors with:

File not found" 'C:\Users\me\AppData\Local\Temp\VB***x.tmp'

There is also an unnamed book created (BookX) and a blank sheet inserted in the front but it's empty and named Sheet4
When I debug, it's always stopped on the same line and if I simply continue the macro it will finish - or eventually finish if it errors again and I continue clicking play.
I added a line to wait a couple seconds because it seemed like a timing problem, but it still will error.
I'm in testing right now, so the data is the same each time I run it but, as mentioned above, it doesn't fail at the same time.
As an example, I just tried to run it 3 times and it error in the 2nd loop, 11th loop, and 3rd loop.

The entire code is below and the line that it stops on is red.
Thanks in advance for any support!

Option Explicit
Sub BuildAudits()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Get last row of data on TempSwaps
    Dim LastRowAuditAssignment As Long
        With Sheets("AuditAssignment")
            LastRowAuditAssignment = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim wk As String, yr As String
    Dim FName As String, FPath As String
    Dim owb As Workbook


    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
    End With
Dim i As Integer
Dim CurrLoc As String
Dim CurrDate As String
Dim CurrFileDate As String
Dim CurrScorer As String
Dim PrevScore As String
Dim CurrFileName As String
Dim SendTo As String


For i = 4 To LastRowAuditAssignment
With Sheets("AuditAssignment").Range("A:Z")
CurrLoc = .Cells(i, 2)
CurrDate = Format(Now(), "mm/dd/yy")
CurrFileDate = Format(Now(), "mmddyy")
CurrScorer = .Cells(i, 1)
PrevScore = .Cells(i, 7)
CurrFileName = "5S Audit-" & CurrScorer & "-" & CurrFileDate & ".xlsx"
SendTo = .Cells(i, 6)
End With
        With Sheets("AuditAssignmentHistory")
            .Range("A2").EntireRow.Insert
            .Cells(2, 1) = CurrFileName
            .Cells(2, 2) = CurrDate
            .Cells(2, 3) = CurrScorer
            .Cells(2, 4) = CurrLoc
        End With
With Sheets("Production 5S")
    .Range("val_FileNameRef") = ""
    .Range("val_Location") = ""
    .Range("val_Date") = ""
    .Range("val_ScoredBy") = ""
    .Range("val_PrevScore") = ""
    .Range("val_FileNameRef") = CurrFileName
    .Range("val_Location") = CurrLoc
    .Range("val_Date") = CurrDate
    .Range("val_ScoredBy") = CurrScorer
    .Range("val_PrevScore") = PrevScore
    
    Dim j As Integer
    For j = 1 To 25
    .Range("val_Wk" & Format(j, "00")) = j
    .Range("val_Score" & Format(j, "00")) = j
    Next j
    
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Delete and re-add TempSwaps sheet
Application.DisplayAlerts = False
Sheets("TempSwaps").Delete
Application.DisplayAlerts = True
Dim sheet As Worksheet
Set sheet = Sheets.Add
sheet.Name = "TempSwaps"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Unfilter Sheet
If Sheets("AuditAssignmentHistory").AutoFilterMode Then Sheets("AuditAssignmentHistory").AutoFilter.ShowAllData
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Filter by area and copy
Sheets("AuditAssignmentHistory").Range("$A$1:$BC$" & Sheets("RefData").Range("val_HistoryCount") + 1).AutoFilter Field:=4, _
Criteria1:=CurrLoc
Sheets("AuditAssignmentHistory").Range("B1:AD" & Sheets("RefData").Range("val_HistoryCount")).Copy
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Paste in TempSwaps sheet
With Sheets("TempSwaps").Range("A1")
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Get last row of data on TempSwaps
    Dim LastRowTempSwaps As Long
        With Sheets("TempSwaps")
            LastRowTempSwaps = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Sort by newest date first
    ActiveWorkbook.Worksheets("TempSwaps").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TempSwaps").Sort.SortFields.Add Key:=Range( _
        "A2:A" & LastRowTempSwaps), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("TempSwaps")
        .Sort.SetRange Range("A1:AC" & LastRowTempSwaps)
        .Sort.Header = xlYes
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Load weeks and audit scores in sheet
Dim m As Integer
Dim RefColumn As Integer
Dim ThisScore As Integer
ThisScore = 0
    For m = 1 To 25
    RefColumn = 4 + m
    ThisScore = Sheets("TempSwaps").Cells(2, RefColumn)
        With Sheets("Production 5S")
        .Range("val_Score" & Format(m, "00")) = ThisScore
            Dim k As Integer
            Dim ThisScoreCount As Integer
            ThisScoreCount = 0
            For k = 2 To LastRowTempSwaps
                If Sheets("TempSwaps").Cells(k, RefColumn) = ThisScore Then
                ThisScoreCount = ThisScoreCount + 1
                Else
                k = LastRowTempSwaps + 1
                End If
            Next k
        .Range("val_Wk" & Format(m, "00")) = ThisScoreCount
        End With
    Next m
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Copy Out Audit Sheet'''''
Application.DisplayAlerts = False
Dim NewBook         As Workbook
FPath = "C:\Users\rclark\Desktop\PCS 5S Audits\Open Audits"
FName = CurrFileName
Set NewBook = Workbooks.Add
ThisWorkbook.Sheets("Production 5S").Copy Before:=NewBook.Sheets(1) 'code stops at this line on error
NewBook.SaveAs Filename:=FPath & "\" & FName
NewBook.Close False
'Workbooks(FName).Close
'''''Wait a second to avoid "File Not Found"
'Application.Wait Now + #12:00:05 AM#
Application.DisplayAlerts = True
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''Send Email to Auditor'''''
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
    With OutMail
    .To = SendTo
    .CC = Sheets("RefData").Range("val_CC")
    .BCC = ""
    .Subject = "5S Audit for " & CurrScorer & " week of " & CurrDate
    .Body = "Please see attached for your 5S audit assigment for the week of " & CurrDate & vbNewLine & _
    "Your assigned location is: " & CurrLoc & "." & vbNewLine & _
    "Please perform the audit, enter the results into the provided Excel sheet and email back to me before the end of the week" & vbNewLine & _
    "Thank You."
    .Attachments.Add (FPath & "\" & FName)
    If Sheets("AuditAssignment").Range("val_EmailCheck") = "Review First" Then
    .Display
    Else
    .Send   'or use .Display
    End If
    End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Next i
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

[SamT]

Try adding

Do Events

Right after that line



"Do Events" tells the code to let Windows handle everything that just happened before continuing. It's not a real bad idea to use Do Events whenever the code does important things.

A cursory scan of your code sees ~8 places where Do Events could be appropriate.

[rjclark]

Thank you for the reply. I added the DoEvents after the code line but I'm having the same problem.

[rjclark]

I added DoEvents just before every "next" in each loop and the first time it ran all. I thought all was well, but it was just a bit of luck as the 2nd and 3rd times I tried I received the error.

[SamT]

EnableEvents = False negates DoEvents.

Some ideas and Hints:

• Always declare Row and Column counters as Long. Integers only go as high as 32K
• Declare fixed Sheet Names and Paths as module level Constants. If/When they change, it makes it easy to adjust the code if they're all in one place
  
  • Ex: Const shtAAHist As string = "AuditAssignmentHistory"
  
  
• Refactor the code into many Boolean Functions that return a "False" if they fail, each of your Comment mark separator lines is a good place to start.
• Set/Reset Application Properties, (Enable Events, ScreenUpdating, etc) in each suggested Function only when needed for that function.
• Use DoEvents in the main sub after each such Function Call returns "True".

I suspect the Send Mail is the problem bottleneck in your project.

Convert the Send Mail part of the code to a Class module Object, they are persistant and run in a separate thread. Add a Boolean "ready" Property to the Class. The Function that passes parameters to the Class should delay code execution until the parameters have been passed when the Class is "Ready." Instantiate the Class at the beginning of the Main sub and Kill it at the end of the main sub.

The only way I can think of to make the code appear to run very fast is to have the SendMail Class accept all inputs, store them in an array, and start processing them after the Main Code has run its course. Then the Class would have to self-terminate when it was done. For that, I would build the SendMail Class into Outlook itself so that it would not be affected if Excel closed.

Outlook is beyond my current state of knowledge, but if I can learn it, so can you.

Bet Angel and Excelhas a lot of info on Classes and making Excel work with Classes in other Applications. I still have all my attachments to that thread if any have fallen off.

[rjclark]

SamT thank you again for the tips and support.
I completely removed the email creation from the macro and the result is identical. Same error at the same line, same random behavior. I'm convinced that something is falling behind as it is executing and the macro is trying to take it to the next step before something else is ready. I say this because, at no time should I have an open book (Bookx) with a blank sheet added and no content. The macro is inserting a specific sheet into the new file so I should at the very least have an open book with my sheet inserted and named.

Set NewBook = Workbooks.Add
ThisWorkbook.Sheets("Production 5S").Copy Before:=NewBook.Sheets(1)

But it's like it's inserting a new sheet in the new book and then getting lost.

Also as mentioned before, the fix has always been time. If I wait, click debug and then play, it continues without a hitch until it does the exact same thing again at another point in the loop.

It feels like it creates a book but has figured out where and what it is before it tries to do something with it.

[GTO]

Greetings rjclark and welcome to VBAX,

Untested but as we suspect a timing issue of sorts, in a junk copyof your workbook, try:

'// Change...				//
  Set NewBook = Workbooks.Add
  ThisWorkbook.Sheets("Production 5S").Copy Before:=NewBook.Sheets(1) 'code stops at this line on error
  NewBook.SaveAs Filename:=FPath & "\" & FName
  NewBook.Close False
	
'// ...To:				//
  Set NewBook = Workbooks.Add
  NewBook.SaveAs Filename:=FPath & "\" & FName
  ThisWorkbook.Sheets("Production 5S").Copy Before:=NewBook.Sheets(1) 'code stops at this line on error
  NewBook.SaveAs Filename:=FPath & "\" & FName
  NewBook.Close False

Hope that helps,

Mark

[rclark]

Thank you GTO. I thought and tried the very same this morning but it did not change my result.

[GTO]

My apologies - the .SaveAs after the .Copy should of course be a .Save

That said and just to confirm, if you include the .SaveAs before copying, it is still erring on the .Copy line?

If yes, can you attach a copy (with fake but same type data substituted for any private/sensitive data) of your workbook? You would need to make the attachment in .xls format for me to be able to take a look. (Due to a combination of being busy and laziness on my part, I only have Excel 2003 available at the moment)

Mark

[SamT]

I am in the process of Refactoring your code. I will continue as long as I am interested.

The attached is as far as I've gotten. The Code style is based on keeping code that involves a sheet in that sheet's code page. Note that I have assigned and use Sheet CodeNames in the Code.

I am having trouble understanding exactly what is going on with TempSwaps and am taking a break at that point in your code. I have made the TemSwaps sheet permanent and do not delete and remake it.

What I have att compiles, but obviously I cannot test it since I have no data and don't know what and where your named Ranges are.

[rjclark]

Hi GTO. You are correct. I moved the SaveAs to before the copy and a Save to after. I still get the error at the same place. I think I've attached the file, though I'm struggling to be confident in that.
Thank you again for your amazing support!

5S Audit Schedule-Sample.xls

[rjclark]

SamT. The TempSwap is a bad habit I've formed to throw data on the sheet named "TempSwap", do something to it and then pick it back up for something else. the reason I delete it and add it back is that it seemed to run more quickly than clearing the data. It's lazy and there are probably a million ways to do it better but my goal is the destination moreso than the journey on some of these things. I will try this out and see how it fairs. Thank you so much!

[rjclark]

SamT, thank you. I reviewed the sheet but sadly have no idea what to do with it! I see that several bits of code have been added to each sheet, but I don't know what they do or why they are on the sheets. I'm also not sure how to put my sheets with data in the WB or if I'm supposed to copy them over. Can you you describe the purpose of the code on each sheet? I've made zero progress in remedying this issue. Do you have any other ideas?

[rjclark]

This is consuming me! I'm actually watching the folder for the Tmp file and immediately AFTER the VBA error, the file it's "missing" pops up in the folder! All I have to do is click play and it continues. I've put in an Application.Wait line and that seems to help it, but it still will error and takes too long to run.

[SamT]

RJ, I see that the Production Sheet has many merged cells. Merged cells are an anathema to VBA and make it very difficult to work with Ranges, especially Named Ranges that include Merged Cells.

Example using val_FileNameRef, which refers to Production 5S'!$B$2, a Merge that includes Range("B2:F2")

X = Range(val_FileNameRef).Cells(1)

IF IRC, .Cells(1) is right, But it might be .Range("A1") instead. There may even be more to it than that : Se below

You can eliminate all Merged Cells on the sheet and use some creative Cell Formatting of Horizontal Alignment, (Center Across Selection,) and White vertical borders to keep the same appearance.

I note that some Named Ranges use the same Name on two sheets, one as a Global Name, and the other as a Sheet name Example val_Date
On the Audit Assignment it is a Global Name, but on the Production sheet it is a Sheet Specific Name

'Using Merged Cells
AuditDate = Range(val_Date).Cells(1)
ProductionDate = Range('Production 5S'!val_Date).Cells(1)

Even though I always design Excel Projects to only use Named Ranges in Excel Formulas, (Never in VBA,) I always use a Sheet Reference prefix. Example: pro_Date, and aud_Date.

[SamT]

I have attached a book with many sheets deleted, this broke all the formulas that referenced those sheets

The purpose of this exercise was to introduce you to a better (IMO) way to design your Excel Projects.

In the VBA editor, AKA the VBE. First use Ctrl+R to insure the Project Explorer is open, Then use F4 for the Properties Window.

Study the Sheet Names and Sheet CodeNames in the Explorer and Properties against the Actual Sheet Tab Names.

Note that all Code uses the Sheet CodeNames. No "Sheets("TabName")"s used in Code

All Sheets use Public Functions to return Ranges for any code in the project to use. These Functions also work in the Sheet's Code. This means no "Sheets("TabName").Range("RangeName")". Instead, I use "SheetCodeName.FunctionName" To return a Range.

All Code that operates on a sheet is in a Sheet's CodePage as a Public Sub. This means that I can use identical Subs, and Functions, with identical Names on different sheets. It also keeps all Module code very neat and simple. All these type subs can be called with "SheetCodeName.SubName".

I have also included two Developmental Testing subs for your perusal on one of the sheet's Code Pages (AKA, Code Panes)

[rjclark]

Thank you for the response SamT. I have done these things but there is no change. What I have found is that if I do not insert the sheet into the new book everything works perfectly. To further this, I created a blank sheet and tried to insert it instead of the one I was inserting in case there was a problem with it. The routine fails in the exact same way with the blank sheet. I'm certain that the problem lies with inserting the sheet or the way I'm doing it. Are there alternatives?

[SamT]

Language matters

To Insert (a sheet) into a Workbook means to create a brand new blank Sheet in that Workbook and that is NOT what you are trying to do.

inserting the sheet or the way I'm doing it. Are there alternatives?

Yes, the Copy and/or Move Methods



Personally, I see so much wrong with the Project that it is fruitless IMO to spend so much time and effort on one little line of code.

[rjclark]

I have nothing but respect for your knowledge and expertise, but regardless of the rest of the project is, I can't seem to find any code that can add a book, name it, copy a sheet into it, save it, and close it all inside a loop without errors. If I could just get this code I would happily work backwards. I can't seem to get this to happen even if I take everything else out of the macro. I stripped everything down to this simple loop in a separate book and it still errors in the exact same way. There is something fundamentally wrong that has nothing to do with the rest of my code or data. If you put this in a module, set the paths respectively and run it it fails (for me at least):

Sub TestLoopSample()Dim i As Integer
For i = 1 To 20
Application.DisplayAlerts = False
Dim NewBook         As Workbook
FPath = "Define"
FName = "Test" & i & ".xlsx"
Set NewBook = Workbooks.Add
NewBook.SaveAs Filename:=FPath & "\" & FName
ThisWorkbook.Sheets("CopyMe").Copy Before:=NewBook.Sheets(1)
NewBook.Close False
Application.DisplayAlerts = True
Next i
End Sub

[rjclark]

It's my computer! I just ran this on another computer and my original code worked with no issues. Does anyone have a recommendation as to where I should should start troubleshooting for conflicts? The computer I ran it on has the same version of Excel with the same reference libraries selected. I'm not sure what could be making it behave differently.