By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 775 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

Word/Excel disappearing with VBA doc automation code

P: 8
Hi everyone,

I have a weird problem with some Word/Excel automation code that I run from Access (not sure if I should therefore post this in the Access forum..? Thought I'd try here first)

Basically, the code either opens a document (if it exists) or creates it from a template if it doesn't. Docs can be Word or Excel docs.

The weird part is that the code was working earlier tonight. But now it's not, not properly, anyway. Now, what happens is I can't see Word or Excel when the docs are opened/created -- Task Mgr tells me the relevant app is running, but there's no sign of it onscreen or in taskbar. At all! I think the code is creating the docs.. at least, most of the time... arrrghh!

So here's some code:

Firstly, I have a global function that creates the Word or Excel object:

Expand|Select|Wrap|Line Numbers
  1. Public Function getOfficeApp(appType As String) As Object
  2.     Set getOfficeApp = GetObject(, appType & ".application")
  3.     If Err = 429 Then
  4.         Set getOfficeApp = IIf(appType = "Word", New Word.Application, New Excel.Application)
  5.         Err = 0
  6.     End If
  7. End Function

Then, I open existing docs with the following (in a sub in another module):

Expand|Select|Wrap|Line Numbers
  1.         Select Case ext
  3.             Case "doc", "docx"
  4.                 Set app = getOfficeApp("Word")
  5.                 Set appDoc = app.Documents(KVMJobDir & doc)
  6.                 If Err = 0 Then
  7.                     checkIfDocOpenSaveFirst "word", app, appDoc
  8.                 End If
  9.                 'On Error GoTo KVMGetDoc_ErrorHandler
  11.                 ' open the doc for editing
  12.                 Set appDoc = app.Documents.Open(KVMJobDir & doc)
  13.                 appDoc.Activate
  14.                 app.ActiveDocument.Activate
  17.             Case "xls"
  18.                 Set app = getOfficeApp("Excel")
  19.                 Set appDoc = app.Workbooks(KVMJobDir & doc)
  20.                 If Not appDoc Is Nothing Then
  21.                     checkIfDocOpenSaveFirst "excel", app, appDoc
  22.                 End If
  23.                 On Error GoTo KVMGetDoc_ErrorHandler
  25.                 ' open doc for editing
  26.                 MsgBox "opening excel wb: " & KVMJobDir & KVMDocName
  27.                 app.Workbooks.Open (KVMJobDir & doc)
  28.                 app.Visible = True
  29.                 app.UserControl = True
  31.         End Select
and I create new docs when needed with:

Expand|Select|Wrap|Line Numbers
  1.         Select Case ext
  3.             Case "doc", "docx"
  4.                 Set app = getOfficeApp("Word")
  5.                 Set appDoc = app.Documents.Add(DocPath)
  6.                 appDoc.SaveAs doc
  7.                 appDoc.Activate
  9.             Case "xls"
  10.                 Set app = getOfficeApp("Excel")
  11.                 Set appDoc = app.Workbooks.Add(DocPath)
  12.                 appDoc.SaveAs doc
  13.                 app.Visible = True
  14.                 app.UserControl = True
  16.         End Select

At the end of this sub I close the objects:

Expand|Select|Wrap|Line Numbers
  1.         Set app = Nothing
  2.         Set appDoc = Nothing
(The function checkIfDocOpenSaveFirst() saves a doc as something else if an instance of it is already open, then closes that instance. Not relevant here tho, am testing atm only on unopened docs.)

I've just tested again trying to create a Word doc -- the doc was created fine (can see it in the directory, & there's the extra "~$xx.doc" instance of it also there, showing that it's apparently currently open in Word) -- but I cannot see Word itself! The app is showing in TaskMgr, but not on the taskbar.

Excel had started doing strange things before it too completely disappeared now -- it wasn't rendering properly for a bit, only the app frame would render, with the document area not fully rendering (showing app beneath it), like it was only half opening.

I hope this makes sense, and if anyone can point me to where I'm going so wrong, I'd hugely appreciate the help! (And the relief -- am worried now that I have "lost" Word & Excel!)

Many thanks,
Apr 12 '07 #1
Share this Question
Share on Google+
1 Reply

P: 8

Can anyone tell me if the code looks ok? The way I'm creating the Word/Excel application objects?

Thanks again,
Apr 13 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.