[VBA] Automation Error - no context, no error code, nothing.

[RockandGrohl]

Hi all,

Workflow below

1. ALL Code resides in sheet "Super Automation"
2. Information is gathered on "Ads" tab and brought into "adstemp"
3. Advert Data is opened and relevant tours gathered
4. Advert Data closed, tourlist is loaded into Automation Hub
5. Several spreadsheets are now opened (Regional Press Report, Price Panels, Advert Data again) and relevant information is extracted, sheets are then closed
6. Points are allocated in Automation Hub and suitable tours are ranked
7. This then gets placed back into Ads, Automation Hub is closed, tours are then committed to the Advert Data - opened in Read Only
8. If there are existing lines in Advert Data, they are scrubbed out and new ones are written in place
9. Advert Data saves and closes, Super Automation saves and drops down a line, the process repeats

In red is where I think this process is going pear-shaped. I am getting a popup on screen that says "Automation Error" - that's it. There's no error code or any other message. When I click ok, everything closes and re-opens with a recovered version.

What I am noticing is that it nearly always re-opens in the same place, sometime after step 5, because it has found tours to put in Automation Hub, but not ranked them yet, or if it has ranked them, it hasn't brought them back into Adstemp.

Alternatively, sometimes it will break without any error code popping up, and in this instance, tours appear to be in Automation Hub, ranked, but not brought back to the temp tab.

Here is a snippet of the code:

On Error GoTo NoSave

Sp.Copy
Range("1:1").Delete xlUp




If rop = True Then
newdir = "H:\Sales\Regional Press Selections\" & wbyr & "\Automation Files\ROP\wc " & Format(DateValue(WCD), "yyyy-mm-dd")
Else
newdir = "H:\Sales\Regional Press Selections\" & wbyr & "\Automation Files\RT\wc " & Format(DateValue(WCD), "yyyy-mm-dd")
End If


If Dir(newdir, vbDirectory) = vbNullString Then
On Error Resume Next
MkDir newdir
On Error GoTo 0
End If


'nsname = Replace(PaperName, "/", "") & " - " & PaperName !@!


nsname = Replace(PaperName, "/", "&")


Set n = ActiveWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
n.SaveAs filename:=newdir & "\" & nsname & " - Super - " & ".csv", FileFormat:=xlCSV, CreateBackup:=False
n.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = False




NoSave:
Resume Skip
Skip:
' Bring results back to adstemp
Range("E3:E" & Lastrow).Copy adstemp.Range("H12")
Range("F3:F" & Lastrow).Copy adstemp.Range("I12")
Range("K3:K" & Lastrow).Copy adstemp.Range("J12")
Range("W3:W" & Lastrow).Copy adstemp.Range("K12")
Range("V3:V" & Lastrow).Copy adstemp.Range("L12")
Range("Y3:Y" & Lastrow).Copy adstemp.Range("M12")


Application.EnableEvents = True
Application.DisplayAlerts = False


If ah.ReadOnly = False Then
ah.Close True
Else
ah.Close False
End If

In here, what happens is the results of the Automation Hub's start page (sp) are copied out and saved to the given directory, that's all closed, then back on the Automation Hub it copies the results of the automation back to adstemp.

I think it's here that something isn't going right, but I don't know what and Excel isn't being very verbose about it.


Hoping someone can help. This loop is >600 iterations and so far it's broken 14 times - not good for an automated process to replace a manual one!

 

[RockandGrohl]

Bump.

 

[Norie]

What happens if you remove/comment out all the 'On Error...' stuff?

 

[RockandGrohl]

I guess if it couldn't save the automation file to the directory specified it would break the code? I can give it a shot.

 

[RockandGrohl]

What happens if you remove/comment out all the 'On Error...' stuff?

Just to note, this didn't solve the issue, however I'm reluctant to mark it as solved, but I stripped all the macros out of the "Automation Hub" and resaved it as an xlsx, and so far it seems to have stopped the Automation Error error (I am only on iteration 112 out of 666 though!)