[newbie] better vba coding

[rmanchu]

hi

i've setup a word template (working fine), which reads data from an
excel file. my problem is that its not the fastest process. some
formfields that read data and read/write data to an excel file on
enter/exit events

please bear with me as i'm new to vba coding.

currently, i've separated all initialisation (of excel) for each code
execution event

i think the most time is wasted by the CreateObject and opening
workbook lines.

i thought of using GetObject instead of CreateObject but there's no
guarantee that excel would be running at the required time.

please give me some good ideas on how to make this more efficient.

riyaz

 

[Ed]

Probably the best advice is what has helped me the most: use the
Word.VBA.General newsgroup. You can use Google - Groups on your keywords
with groups as *Word.VBA* and get lots of answers. Sometimes I just scroll
up and down and read posts, and find answers to things that have been
bugging me for a while. If you're using both Word and Excel together, then
you can include both Word.VBA.General and Excel.Programming in the
Newsgroups field of your post, and get the best answers from both groups.
Be sure to include your program versions in any questions you ask.

In using Word to automate Excel, you run into issues with how Excel wants to
update data on open. Depending on your version, I think this can be
controlled by your options, and by setting up the options in your code. I'm
not an expert here by any means, although I've made the two applications
talk back and forth a few times.

Maybe I don't have a good grasp of what you're trying to do, but it seems
that if you're reading data from just one Excel file, then when you set your
Excel object to that file and open it, you ought to be able to access any
cell's data fairly quickly and transfer it to your chosen spot in Word.

Ed

 

[rmanchu]

thanx

will search ur suggested newsgroups.

my problem is that i'm closing excel after finishing each access. thus,
i have to go thru the routine of opening and excel.app and resource
file. what's the way to overcome this?

thanx again
riyaz

 

[Bruce Rusk]

Just create an excel application object that is passed to all the
subs/functions that use it, then destroyed after all your work with it is
done (set to Nothing). That will save the opening/closing overhead.

 

[rmanchu]

this is what i'm trying to do now.

but as a new vba coder, how to implement it is not as obvious as it may
seem ...

 

[Ed]

Take it all over to the Word.Programming newsgroup and post your code.