【转帖】excel question

yang686526

excel question
i have a macro that accesses an excel sheet for part numbers then copies those drawings from the pdm vault. the program works fine except when i am editing the excel sheet and i run my macro. i think because excel is in "edit mode" my macro hangs and finally gives me a message to go to excel and fix my problem. just clicking in another cell fixes my problem.
is there a code that i can use to check to see if excel is in edit mode and create my own error message, or better yet, just take excel out of edit mode?
thanks
dan miel
why comes excel in edit mode?
macro is starting excel, opening sheet, reading cells, doing something with values and closing excel?
hi ivana
the macro does not open excel it just grabs whatever is opened. this macro is written because purchasing would send us list of drawings needed in excel sheets and i was to lazy to type everything into pdmworks to get the drawings, so i wrote this to grab whatever excel is opened and get the files from the vault. i found that when i need to get even one file, it is faster to start an excel file, type the part/s and click the macro button rather than doing searches in the vault. therefore the problem, if i forget to press "enter" or click in another column before i start the macro it thinks i'm still editing the cell.
i have tried using sendkey to send an enter to excel and tried changing the active cell to another cell but if i haven't pressed enter before i start the macro it still hangs.
the code below is similar to my connection and getting the first cell. i'm using an excel 11 reference. if you edit the first cell and run the macro without pressing enter it should hang for a little bit and then tell you that the sever is busy.
dan miel
sub main()
dim xldoc as excel.application
dim filename as string
set xldoc = getobject(, "excel.application")
filename = xldoc.sheets(1).cells(1, 1)
debug.print filename
end sub
and if you do the same trick with word?
sub main()
dim objword as word.application
dim objdoc as word.document
dim s as variant
dim s1() as variant
on error resume next
set part = swapp.activedoc
set objword = getobject(, "word.application")
set objdoc = objword.activedocument
s = objdoc.content.text
s1 = split(s, chr(13))
for each s in s1
debug.print trim(s)
next s
end sub
i tried "resume next" the other day and it still hangs.
dan
no, i mean if you use microsoft word - each row one drawing code - try it with my code above.
for excel edit mode is no solution within vba. you can only do something in vb.
resume next would'nt help. i had to clean it. it is used only if you want resuse word session and if you don't have one, then create new one.
edited: 02/28/2009 at 02:30 pm by ivana kolin
ivana
the code works nicely. i have not used word before and didn't recognize what your code was intended to do. my macro writes back to the excel sheet what it finds, drw, assm, part and missing or obsolete parts so i will need to change how it reports those things, but i like the way that word does not hang up.
thanks
dan miel
quick