excel - Macro will not run when using application.on time method -


i'm trying run macro automatically overnight company using task scheduler on windows , application.ontime method. when run macro myself works not work reason if called via application.ontime function.

quite annoying making automated process not automated. me out? see there problems in code?

the problem before was not passing sdate variables on importer sub tried change getting argument not optional error message instead. ideas?

option explicit public wb1 workbook public wb2 workbook public r range public csvpath string public sproduct worksheet public spivot worksheet public scsvsheet worksheet public saccount worksheet public sbutton worksheet public smainsheet worksheet public srawdata worksheet public sfilenames worksheet public sfrontsheet worksheet public sbankholidays worksheet public smatrades worksheet public smaoverview worksheet public smarketdata worksheet public smafxeffect worksheet public sdate string public rdate double public lastrundate date public lrow integer public lrow2 integer public arow integer public srange range public abr string public fixingname string public fixingreport string public x variant public y double public integer public k integer public p integer public ccy string public syear long public smonth long public sday long public pt pivottable public ws worksheet public wb workbook public text string public actualdate long public col long  sub main()  application.displayalerts = false application.screenupdating = false  setsheets  scsvsheet.visible = true spivot.visible = true sfilenames.visible = true sbankholidays.visible = true  if sbutton.range("manualdateyesno").value = "yes"  sdate = sbutton.range("manualdate").value rdate = sbutton.range("manualdate").value  datadeleter  while rdate <> datevalue(date) if weekday(sdate) <> 1 , weekday(sdate) <> 7 , not isnumeric(application.match(rdate, sbankholidays.columns("a:a"), 0))  importer (sdate)  end if  rdate = rdate + 1 sdate = format(dateserial(year(rdate), month(rdate), day(rdate)), "dd/mm/yyyy") loop  sbutton.range("manualdateyesno").value = "no"  else  if weekday(date) = 2 sdate = date - 3 else sdate = date - 1 end if  if weekday(date) = 1 or weekday(date) = 7 thisworkbook.close false  lrow = sbankholidays.range("a1048576").end(xlup).row  each r in sbankholidays.range("a2:a" & lrow) if weekday(date) = 2     if r + 3 = date thisworkbook.close false else     if r + 1 = date thisworkbook.close false end if next  datadeleter  importer (sdate)  end if  refreshpivots  smainsheet.columns("c:c").numberformat = "m/d/yyyy" srawdata.columns("e:e").numberformat = "m/d/yyyy" sproduct.columns("c:c").numberformat = "m/d/yyyy" saccount.columns("d:d").numberformat = "m/d/yyyy"  calculate  scsvsheet.visible = false spivot.visible = false sfilenames.visible = false sbankholidays.visible = false sfrontsheet.activate  application.screenupdating = true application.displayalerts = true  application.quit  end sub  private sub importer(byref sdate string)  'on error goto errorhandler  setsheets  syear = year(sdate) smonth = month(sdate) sday = day(sdate)  lastrundate = spivot.range("a1000000").end(xlup).value  sdate = format(dateserial(syear, smonth, sday), "yyyymmdd") rdate = datevalue(format(dateserial(syear, smonth, sday), "dd/mm/yyyy"))  lrow = sfilenames.range("a1048576").end(xlup).row  each srange in sfilenames.range("a2:a" & lrow)  abr = srange.offset(0, 1).value fixingname = srange.offset(0, 2).value  fixingreport = "p:\systemfiles\shareddocs\" & abr & "\fixing files\" & sdate & " " & fixingname & ".xls"  workbooks.open filename:=fixingreport, readonly:=true  setsheets2  smaoverview.range("d9:d43").copy  sproduct lrow = .range("a1048576").end(xlup).row + 1  .range("e" & lrow).pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=true .range("c" & lrow).value = rdate .range("b" & lrow).value = srange .activate .range("a" & lrow - 1).autofill destination:=range("a" & lrow - 1 & ":a" & lrow) end  refreshpivots  end if  next  lrow = spivot.range("a1048576").end(xlup).row  lrow = smafxeffect.columns("b:b").find("dbin", searchorder:=xlbyrows, searchdirection:=xlprevious).row + 1 lrow2 = smafxeffect.range("b65000").end(xlup).row  k = lrow  each r in smafxeffect.range("b" & lrow & ":b" & lrow2) smafxeffect.activate smafxeffect.range(cells(k, 3), cells(k, 11)).copy  saccount lrow = .range("a1048576").end(xlup).row + 1 .activate .range("e" & lrow).pastespecial paste:=xlpastevalues ccy = .range("m" & lrow).value arow = smafxeffect.columns("b:b").find(ccy, searchorder:=xlbyrows, searchdirection:=xlprevious).row .range("o" & lrow).value = smafxeffect.cells(arow, 11).value .range("d" & lrow).value = rdate .range("c" & lrow).value = r .range("q" & lrow).value = srange .activate .range("a" & lrow - 1 & ":b" & lrow - 1).autofill destination:=range("a" & lrow - 1 & ":b" & lrow) .range("p" & lrow - 1).autofill destination:=range("p" & lrow - 1 & ":p" & lrow) .range("n" & lrow - 1).autofill destination:=range("n" & lrow - 1 & ":n" & lrow) '.range("n" & lrow).value = .range("e" & lrow).value * .range("o" & lrow).value k = k + 1 end next  wb2.close false  next  each pt in spivot.pivottables     pt.pivotcache.refresh next  spivot lrow = .range("a1048576").end(xlup).row rdate = .range("a" & lrow).value lrow = .range("i1048576").end(xlup).row end  each r in spivot.range("i3:i" & lrow) srawdata .activate lrow = .range("a1048576").end(xlup).row + 1 .range("d" & lrow).value = r .range("c" & lrow).value = r.offset(0, 1).value .range("e" & lrow).value = rdate .range("a" & lrow - 1).autofill destination:=range("a" & lrow - 1 & ":a" & lrow) .range("b" & lrow - 1).autofill destination:=range("b" & lrow - 1 & ":b" & lrow) .range("f" & lrow - 1 & ":cb" & lrow - 1).autofill destination:=range("f" & lrow - 1 & ":cb" & lrow) if .range("k" & lrow).value = 0 .range("k" & lrow).entirerow.delete  if .range("j" & lrow).value = "true" text = .range("c" & lrow).text & .range("d" & lrow).text & cdbl(.range("f" & lrow).value) x = application.match(text, .columns("a:a"), 0) .range("s" & lrow).goalseek goal:=0, changingcell:=range("ak" & x) end if  end next  lrow = spivot.range("l1048576").end(xlup).row  each r in spivot.range("l3:l" & lrow) smainsheet .activate lrow = .range("a1048576").end(xlup).row + 1 .range("b" & lrow).value = r .range("c" & lrow).value = rdate .range("a" & lrow - 1).autofill destination:=range("a" & lrow - 1 & ":a" & lrow) .range("d" & lrow - 1 & ":bp" & lrow - 1).autofill destination:=range("d" & lrow - 1 & ":bp" & lrow) .range("bb" & lrow).clearcontents .range("bb" & lrow).value = .range("aw" & lrow).value end next  sbutton.range("lastrun").value = rdate  exit sub  errorhandler:  each wb in workbooks if wb.name <> thisworkbook.name wb.close false next  msgbox err.description & err.source  end sub  public sub datadeleter()  each ws in wb1.worksheets on error resume next if ws.name = "main sheet" or ws.name = "raw data" or ws.name = "product" or ws.name = "account" col = sheets(ws.name).rows("1:1").find("date", lookat:=xlwhole, searchorder:=xlbycolumns, searchdirection:=xlprevious).column x = application.match(rdate, sheets(ws.name).columns(col), 0) lrow = sheets(ws.name).range("a1048576").end(xlup).row sheets(ws.name).rows(x & ":" & lrow).entirerow.delete end if on error goto 0 next  end sub  public sub refreshpivots()  each ws in worksheets     each pt in ws.pivottables         pt.pivotcache.refresh     next next  end sub  public sub setsheets()  set wb1 = thisworkbook  set sproduct = wb1.sheets("product") set spivot = wb1.sheets("pivot") set scsvsheet = wb1.sheets("csvsheet") set saccount = wb1.sheets("account") set sbutton = wb1.sheets("button sheet") set srawdata = wb1.sheets("raw data") set smainsheet = wb1.sheets("main sheet") set sfilenames = wb1.sheets("file names") set sfrontsheet = wb1.sheets("front sheet") set sbankholidays = wb1.sheets("bank holidays")  end sub public sub setsheets2()  set wb2 = activeworkbook  set smatrades = wb2.sheets("ma trades") set smaoverview = wb2.sheets("ma overview") set smarketdata = wb2.sheets("market data") set smafxeffect = wb2.sheets("ma fx effect")  end sub

it happened upon redesigning code, hadn't changed application.ontime upon opening workbook call different macro. changed macro called , works


Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more