i adding workbook workbooks programmatically thru macro.
when close newly created workbook manually,then try run code facing issue "automation error" or "workbook_ activation failed" .
please how can solve issue.
my code below:
const durationperiod = 90 dim trendsfile workbook dim trendssheet worksheet dim worksheetopen boolean option explicit sub sastrend() ' routine looks @ organization in highlight cell , creates 90 day user , usage trend graph dim customername string dim duration integer dim weeklysheetname string dim startdate date dim enddate date dim dayofweek integer on error goto errorhandling customername = activesheet.range("a" & (activecell.row)).value ' calculate first week of duration period. usage trends sheets on saturdays ' , updated on sunday night / monday morning startdate = date - durationperiod dayofweek = weekday(startdate) startdate = startdate + (vbsaturday - dayofweek) ' want report recent saturday. taking current date -1 ' make sure recent saturday assuming run m-f enddate = date - 1 dim usagetracking worksheet dim datafound boolean datafound = false ' reference usage tracking sheet able switch focus set usagetracking = worksheets("usage tracking") while startdate < (enddate) dim weeklyusers double dim weeklyminutes double dim rownumber integer ' walk weekly sheets , gather usage data graphing ' make sure usage tracking active worksheet usagetracking.activate ' buld sheet name weeklysheetname = "sas-" + format(startdate, "mm") + format(startdate, "dd") ' number of users specified week weeklyusers = application.worksheetfunction.countif(worksheets(weeklysheetname).range("b:b"), customername) ' total minutes specified week weeklyminutes = application.worksheetfunction.sumif(worksheets(weeklysheetname).range("b:b"), customername, worksheets(weeklysheetname).range("d:d")) ' check make sure have non-zero data before creating graph / worksheet if (weeklyusers > 0 or weeklyminutes > 0) , datafound = false ' create new workbook hold trend sheets if trendsfile nothing set trendsfile = workbooks.add() trendsfile.activate set trendssheet = trendsfile.activesheet else ' add new sheet trends workbook trendsfile.activate set trendssheet = sheets.add end if datafound = true rownumber = 1 trendssheet.name = left(customername, 10) + " " + format(date, "mmdd") trendssheet.cells(rownumber, 1) = "week ending" trendssheet.cells(rownumber, 2) = "users" trendssheet.cells(rownumber, 3) = "minutes" rownumber = rownumber + 1 end if ' if sheet has been created, have @ least 1 non-zero value add data if datafound = true trendssheet.cells(rownumber, 1) = startdate trendssheet.cells(rownumber, 2) = weeklyusers trendssheet.cells(rownumber, 3) = weeklyminutes rownumber = rownumber + 1 end if startdate = startdate + 7 wend ' if have data, create chart if datafound = true ' make sure trends sheet active chart insertion trendssheet.activate dim chtchart chartobject dim chartname string dim endrange string ' define end of range chart endrange = "c" & cstr(rownumber - 1) ' add chart current sheet set chtchart = activesheet.chartobjects.add(left:=200, top:=200, width:=900, height:=400) chtchart.activate activechart.charttype = xllinestacked activechart.setsourcedata source:=trendssheet.range("a1", endrange) activechart.hastitle = true activechart.charttitle.text = customername activechart.applylayout (5) else msgbox ("no usage data found customer " + customername) end if ' set trendssheet = nothing ' set trendsfile = nothing exit sub errorhandling: msgbox (err.description) end sub i thinking issue trendsfile instance active though closed manually.
when debugged see, hitting else part though trendsfile closed.
how can make sure opened correctly
set objects referencing old workbook nothing after end of macro:
set trendssheet = nothing set trendsfile = nothing 
Comments
Post a Comment