Macro won't continue after it runs another macro that saves as

[Joe Patrick]

Hi!

I have mutiple spreadsheets, each with its own Autpen macro that imports text, formats, saves, saves as webpage, close active workbook:

Sub Autpen()
'import text
'format
ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="U:\TPCentral_Too\ADA_Requests\ADA_Requests.htm" _
, FileFormat:=xlHtml, ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

I'm trying to have one spreadsheet with an Autpen macro that does this:

Sub Autpen() 'update all
Workbooks.Open(Filename:="U:\TPCentral_Too\ADA_Requests\ADA_Requests2.xls"). _
RunAutoMacros Which:=xlAutoOpen
Workbooks.Open(Filename:="U:\TPCentral_Too\ADA_Requests\ADA_Requests1.xls"). _
RunAutoMacros Which:=xlAutoOpen
End Sub

The problem is that the macro stops cold after the first AutoOpen. The window with the macro is still open, so it's not that.

Is there a solution for this?

Thank you in advance for any help!

 

[Joe Patrick]

With some help from Bertie I figured this one out. Thanx Bertie!!

-Changed Autpen to ThisWorkbook.AutoUpdate
-Removed this line from AutoUpdate: ActiveWorkbook.Close

Sub UpdateTPC2()
Set objExcel = CreateObject("Excel.Application")
Set wbExcel = objExcel.Workbooks.Open("U:\TPCentral_Too\ADA_Requests\ADA_Requests.xls")
With objExcel
.Application.Run ("ThisWorkbook.AutoUpdate")
End With
wbExcel.Close SaveChanges:=False
Set objExcel = CreateObject("Excel.Application")
Set wbExcel = objExcel.Workbooks.Open("U:\TPCentral_Too\ADA_Requests\ADA_Requests2.xls")
With objExcel
.Application.Run ("ThisWorkbook.AutoUpdate")
End With
wbExcel.Close SaveChanges:=False
MsgBox "TPC2 has been updated"
ActiveWorkbook.Close
End Sub

 

[Joe Patrick]

correction:

Sub UpdateTPC2()
Set objExcel = CreateObject("Excel.Application")
Set wbExcel = objExcel.Workbooks.Open("U:\TPCentral_Too\ADA_Requests\ADA_Requests.xls")
With objExcel
.Application.Run ("ThisWorkbook.AutoUpdate")
End With
wbExcel.Close SaveChanges:=False
Set objExcel = CreateObject("Excel.Application")
Set wbExcel = objExcel.Workbooks.Open("U:\TPCentral_Too\ADA_Requests\ADA_Requests2.xls")
With objExcel
.Application.Run ("ThisWorkbook.AutoUpdate")
End With
wbExcel.Close SaveChanges:=False
Set wbExcel = Nothing
Set objExcel = Nothing
MsgBox "TPC2 has been updated"
ActiveWorkbook.Close
End Sub