Solved

Can VBA be used to identify filename in-use by another application?

Posted on 2011-02-28
14
242 Views
Last Modified: 2012-05-11
I'm using Access 2007 to import an .XML file into Excel, modifing the format, then saving the file as input to a table in Access. Currently, this has to be done due to .XML attributes do not automatically import into Access tables. (ie. <Employee  ID="John"> : ID="John" is not imported into Access.) I will attempt this after I solve the question below.

The file being imported is generated by the user in a non-Microsoft program and saved using standard windows FileDialog. The file may not have been opened by this program, but instead created through it's use. The file must be saved in a specific location prior to use by Access.

Can VBA be use to identify the filename in use, Opened or Saved, by this executable with the user's input through FileDialog?

The method I currently have only identifies the filename if opened (not saved) via Windows Explorer.
0
Comment
Question by:mhonzell
  • 9
  • 5
14 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<I'm using Access 2007 to import an .XML file into Excel, modifing the format, then saving the file as input to a table in Access. Currently, this has to be done due to .XML attributes do not automatically import into Access tables. (ie. <Employee  ID="John"> : ID="John" is not imported into Access.) I will attempt this after I solve the question below.>>

  There are other methods of importing, such as using the xml document object or opening the file directly and parsing it.

<<Can VBA be use to identify the filename in use, Opened or Saved, by this executable with the user's input through FileDialog?>>

  You can use the Dir() command to check for the existance of a file, but I'm not sure that's what you want.

<<The method I currently have only identifies the filename if opened (not saved) via Windows Explorer. >>

  Not sure what you mean by this...

JimD.
0
 

Author Comment

by:mhonzell
Comment Utility
Unfortunately, no.
The file generated, or opened is only known to the user, so I cannot search for the file's existence until I know the name.
0
 

Author Comment

by:mhonzell
Comment Utility
In regards to your second comment:
If I open a file (not executable) by double-clicking, or a command-line, the process can be echoed. But, in my case, the file will always be opened or saved via the FileDialog window.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<The file generated, or opened is only known to the user, so I cannot search for the file's existence until I know the name. >>

<<If I open a file (not executable) by double-clicking, or a command-line, the process can be echoed. But, in my case, the file will always be opened or saved via the FileDialog window. >>

  Your only option then is to monitor a directory for new files every xx seconds.  If these specific files are the only type in there, then you will need to do nothing else and can simply process them.

  If not, you'd need to open each new file for read and determine if it's one you want by the contents.

JimD.
0
 

Author Comment

by:mhonzell
Comment Utility
I was really hoping that was not the case.

Kind of defeats a one button solution for the user.
Now, I'll have to have them search for the file they created and select it. This will work, but seems inelegant after the user has already performed this process to either open or save the file.

Hmm... Maybe I can get the vendor of my utility (it's a specialized program) to limit the filename to a fixed size and include it as a passable parameter to Access. Then, if the file cannot be found (changed, or hasn't saved the file yet) then a search by the user or error message would be warranted. They haven't been too willing to do much in this area.

Thanks!
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

  Maybe if you explained the process that the user goes through and how your app interacts with that as that is still not clear to me, I might be able to offer something else.

JimD.

 
0
 

Author Comment

by:mhonzell
Comment Utility
User opens an application that allows interface to several databases and via a graphical interface develops a flow chart that relates items in these databases.

When the file is saved via FileDialog, the flow chart is converted to .XML information and saved at a location saved by the user.

At this time, or a later time, the user with this file open, assigns a unique ID to the file and then selects an option to "certify" the flow chart. This process is conducted in another database by comparing the child node values with entries in the database and setting flags,

The application "passes" only one unique piece of information: the ID assigned to the file and contained in the file. But, to get this I need the filename since the vendor did not make the "Certify" button actually pass any information. (It can only open the database.)

Another answer to this is to allow the user to start the database, search for the file to certify and then complete the process without re-opening the prior application. (This is a fairly straightforward answer.) But, I was trying to make this more of a single step that could be completed before exiting the first application making it appear to be part of the same program.

Did this provide enough detail?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<Did this provide enough detail? >>

 Close enough. What's not clear is how your app gets called (or if it even does) by the other application.  I only see two choices:

1. Scanning the directory
2. Hooking the windows save dialog.

  #2 is probably doable, but most likely complicated and very os dependent, making maintaining the thing a problem.  Plus not many would enjoy having an app mucking around in windows at that level.

  #1 is easy to do from VBA with the DIR() function.  I think it's the approach I'd use.

  If you can call your Access app from the other app, you could still make it transparent to the user and a "one step" operation.

JimD.
0
 

Author Comment

by:mhonzell
Comment Utility
Unfortunately, how the database is called appears to be little more than a shortcut that must be pre-configured prior to running the application. This aspect is still in development and I'm trying to get it more configurable, but the vendor does not want any responsibility for the external database/app.

I agree #2 is too messy since this network functions with Win2000 - Win7 and Unix. (Even though the file in question will always be opened or saved in Windows <something>.

I guess I don't see how Dir() is going to help if I don't know the filename.
Example: The file was created two weeks ago, but opened today for "certification." Several files have been created in the two week period since the file in question was created.

One direction I have considered:
  1) Bring the window to focus
  2) Quietly force a save of the file via the menu bar commands.
            This isn't so quiet if the file has not been previously saved and must close the certification window first.
  3) Then search for the most recent file in the specific directory.

It just seemed to me that if the app already knows the filename to complete the Save function, it must be accessible.

Thanks again.
I'm close to throwing in the towel.

0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<I guess I don't see how Dir() is going to help if I don't know the filename.
Example: The file was created two weeks ago, but opened today for "certification." Several files have been created in the two week period since the file in question was created. >>

  You'd loop through the files in the dir, read each and determine if it should be processed.  And maybe that's the part I don't understand; I am assuming by the contents of the file you will know which is the right file.

JimD.
0
 

Accepted Solution

by:
mhonzell earned 0 total points
Comment Utility
If I could compare internal file information to the file information in use by the open application, this would work. Currently, I cannot.

I need to work on the vendor a little more.

While there was not a definitive answer provided, I'm not sure you can provide one at this point. As such, I'm accepting your Dir() answer in hopes that the vendor will supply the one piece needed from their application.
0
 

Author Comment

by:mhonzell
Comment Utility
After showing the vendor this entire "conversation", the vendor has included the unique ID to the command-line. Problem solved.
0
 

Author Comment

by:mhonzell
Comment Utility
Thank you, JDettman
0
 

Author Closing Comment

by:mhonzell
Comment Utility
Dir() will not solve the entire problem, but provided a technique the vendor was willing to work with by providing one additional piece of information when the external application is called.

1) An internal search of files will have to occur to find the file with the passed unique ID.
2) Non-existence will require a prompt to save the file first.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now