vba - How to open Excel if not already open -
i'm trying find way open excel using outlook vba, if it's not open. managed find code on internet opens excel, changes , closes it, doesn't behave nicely if excel workbook open(it apply changes, no longer closes excel workbook, , leaves grey interior; also, doesn't show in explorer anymore , have close task manager). appreciate if explain of code does.
public xlapp object public xlwb object public xlsheet object sub exporttoexcel() dim enviro string dim strpath string 'get excel set enviro = cstr(environ("userprofile")) 'the path of workbook strpath = enviro & "\documents\test2.xlsx" on error resume next set xlapp = getobject(, "excel.application") if err <> 0 application.statusbar = "please wait while excel source opened ... " set xlapp = createobject("excel.application") bxstarted = true end if on error goto 0 'open workbook input data set xlwb = xlapp.workbooks.open(strpath) set xlsheet = xlwb.sheets("sheet1") ' process message record on error resume next xlwb.close 1 if bxstarted xlapp.quit end if end sub
i know xlwb
, xlsheet
objects do, , how they're declared, , understand environ
function , strpath
string do, don't undestand why need bxstarted
boolean, set xlapp = getobject
does, why application.statusbar
message doesn't displayed, difference between getobject
, createobject
and why many error tests needed. in advance.
the difference between object , create object in title, 1 open excel.application
, if there error err<>0
creates excel.application
. think you'll getting saveas message, file may not saving, open, , you're instructing save, error resume next
skipping . try saving before .close
if remove on error resume next
error wont skipped , shown.
sub explaination() dim blndidicreateexcel boolean ' please read msdn on boolean dim objtoholdexcelcreatedornot object ' please read msdn on objects create/get ' user have excel open, if try it, there error logically if not set objtoholdexcelcreatedornot = getobject(, "excel.application") ' there error if err <> 0 ' there was, need create 1 set objtoholdexcelcreatedornot = createobject("excel.application") blndidicreateexcel = true ' yes, created end if ' neccessary ' close workbook ' did create excel, if tidy if blndidicreateexcel objtoholdexcelcreatedornot.quit end sub
Comments
Post a Comment