DIV0
Wed Feb 07 09:26:01 CST 2007
Thanks Shauna,
I'll review ALL my refernces again and check all unloads, closes and quits.
I'd certainly prefer to keep things within Word and Excel as no-one in the
company knows Access but there are enough other people here who can get
around Excel that if something goes wierd, they can still get at their past
data.
The whole reason for me originally getting this project was the only person
who worked in Access had set up a load of little apps for different
departments and then he quit.
--
David M
WinXP - Office2003 (Italian)
"Shauna Kelly" wrote:
> Hi David
>
> >don't take for granted that Excel will remember anything one line to the
> >next
> Nor should we expect that Excel will remember anything from one line in Word
> to the next.
>
> If you run a line of code in Excel that is, say,
> ActiveCell.Font.Bold = True
> then it will work in Excel *only* because Excel is hard-wired into the VBA
> in Excel.
>
> Word knows nothing of such things. It, too, has Fonts and they have Bold
> properties. But it hasn't a clue what an ActiveCell is. It may sometimes
> guess, which is worse than your telling it what to do properly.
>
> For what it's worth, I've written apps that read from Excel using DAO, and
> apps that read and write to and from Excel using VBA where Excel was not
> visible, where the file was opened read-only unless absolutely required,
> could therefore be read by several users simultaneously, and with lots and
> lots and lots of With...End With usage. I'm not sure that's my preferred way
> of working, but it is quite robust once you get all the references sorted
> out.
>
> If your Excel code has trouble quitting Excel, then the first thing I'd
> recommend is to make sure you have properly Unloaded all forms with Unload
> MyForm, and set all module-wide object variables to Nothing.
>
> For what it's worth, if you use DAO or ADO to read a data file from Word,
> you'll need to use full referencing to their object models, just as you have
> to use full referencing to Excel's object model when running code in Word.
> For example, both DAO and ADO have a Recordset object, but Word won't know
> which kind your code is referring to unless you tell it explicitly.
>
> Hope this helps.
>
> Shauna Kelly. Microsoft MVP.
>
http://www.shaunakelly.com/word
>
>
> "#DIV/0" <DIV0@discussions.microsoft.com> wrote in message
> news:10CD6D04-BAD3-4706-A3D1-9600F77FF627@microsoft.com...
> > "Jezebel" wrote:
> >> The 'trouble getting
> >> Excel to suit' is nearly always the result of a bug in your own VBA code:
> >> Excel is very sensitive to unhandled errors, and its usual response is to
> >> sulk and refuse to quit. I've seen several instances where the problem
> >> was a
> >> statement like
> >>
> >> xlRange.Sort Key1:=Range("A1:C10")
> >>
> >> Called from Word VBA, neither Word nor Excel reports an error on the
> >> invalid
> >> Range argument; but Excel will subsequently fail to quit.
> >
> > . . . makes trouble-shooting a real bitch.
> >
> > A previous post here pointed me to 'incomplete' internal references -
> > don't
> > take for granted that Excel will remember anything one line to the next.
> > So
> > every step of code needs full reference to the object
> > (ActiveWorkbook.ActiveSheet.ActiveCell all over the place). I also removed
> > and worked around any With statements because that can also upset Excel
> > when
> > called from Word, and I tried leaving Excel visible.
> > None of that helped and I was looking at how well another form I made
> > worked
> > so well with ADO and an mdb file. It's almost certainly overkill, but if
> > it
> > works I'll go for it. I can explore the full potential of ADO at my
> > leisure
> > but at least the thing will be running without problems.
> >
> > --
> > David M
> > WinXP - Office2003 (Italian)
>
>
>