0

All

I have a database which exports to a word document (via a report saved as an excel document). Essentially access exports the report, opens it and word, and then parses the data into a word table. There are a handful of reasons it's done this way, rather than just exporting the report directly to word, and it all works fine.

Until...

We have worksite / filesite integration, and so if you are not connected to the local network, worksite prompts you during word startup for the relevant document server, or ask if you want to operate offline, or operate locally. The problem I have is that because all of the word document creation isn't visible (and I'd prefer for it not to be), there's no way of answering this prompt.

We can resolve this by just saying "if you're not connected to the network, you need to open an instance of word before you try exporting." But that's annoying, and also people just forget, with the result that the export just hangs (there's no error to handle), and you have to manually end the word (and excel) processes though task manager.

Is there a way to e.g.,

  • Run a timer in parallel, which is set going just before the create.object("Word Application") line, and when it gets to a certain point (say 20 seconds) and the code hasn't advanced, then it just assumes something's gone wrong and makes word visible?
  • Or to detect the presence of the filesite dialog?
  • Or, ideally, just provide the input for the dialog programatically (i.e. "click" the Local button)(the user is prompted for an export location, which is always local)?
mactreb
  • 33
  • 3
  • 1
    Obviously, with WinAPI functions like `FindWindowEx` you can detect these kinds of dialog boxes, and usually with `SendMessage` you can click them or handle them (e.g. click specific buttons). However, I can't write the code for you without knowing a lot of specifics about the dialog (class names, nesting, etc.). – Erik A Sep 08 '18 at 08:38
  • Thanks. Is there a way to identify the dialog? (i.e., if I create the situation in which it happens, is there a way to 'dump' the information about every open window, such that I can work out how to reference it) – mactreb Sep 08 '18 at 08:55
  • You can use Spy++. I personally have some code lying around that does something similar to that tool – Erik A Sep 08 '18 at 09:08
  • Right, so I've got the FindWindow/ FindWindowEx / SendMessage bit working. Is there a way to "watch" for the windows? At the moment I'm using error handling to call a sub which responds to the prompts (i.e., when you get an error 429, then call the sub, and then just go back to where you were). But that has a significant downside of taking an absolute age, because it's not until access has created to work object, and then waited to try and add the document for a while before giving up that the error is triggered. – mactreb Sep 08 '18 at 18:08
  • Nope, not from VBA as far as I know. But periodically searching if a specific window exists should take only milliseconds, so you can do it often. Doing it asynchronously from VBA is tricky, but can be done using vbscript – Erik A Sep 08 '18 at 18:19
  • There may not be any need for you to execute Win APIs to locate modal dialog prompts etc. Can you please confirm where the VBA code is executing from? Your Access db? Also, how is the Word instance being started (code snippet will help) – fivetoniner Sep 09 '18 at 08:15

0 Answers0