How do I add a "browse for file" dialog box to the transferText command?

Question

I'm creating a macro in MS Access that imports a CSV file into a table. I'm using the TransferText action to import the CSV string, and I want to allow the user to browse for the file that contains the CSV string. How do I show the "browse" dialog box to enable the user to choose the file?

Answer 1

See API: Call the standard Windows File Open/Save dialog box. However this requires VBA code.

Answer 2

Answer can be found at http://www.access-programmers.co.uk/forums/showthread.php?p=917371#post917371.

Answer 3

An alternative (which I would say is second choice to the API call recommended by @Tony Toews and @draice) would be to use the Application.FileDialog object. This has been part of the Office automation library for as long as VBA has been in Access, but in recent versions of Access (starting with either A2002 or A2003, I don't know which), the top-level Access Application has provided a wrapper for this object. Beware, though, that without a reference to the Office automation library, the ENUM values that show up in Intellisense can't be used without a reference (a helpful error message informs you of this and offers to create the reference). In short, if you use it's best to use it as you would any automation object with late binding, with the exception that you don't have to initialize the top-level object with Application.CreateObject, as it's already there for you to use.

EDIT:

@draice asks:

I don't understand the following statements that you wrote: "the top-level Access Application has provided a wrapper for this object" "it's best to use it as you would any automation object with late binding"

1. Tony's API code will work in every version of Windows, and because MS believes in backward compatibility, they will never break this API call in future versions of Windows.

2. the FileDialog object is not easy to use in VBA unless you add the reference to the Office Automation library. It is better to minimize the number of references in your Access database, because all sorts of things can mess them up and cause your app to break (any missing reference will prevent all VBA code from running). In order to avoid problems from missing references, we use late binding so that the code you write is not dependent on the outside libary.

3. Microsoft might remove this object from future versions of Access. The FileSearch object is an analogous situation, in that it was introduced in A95/97 as part of the Office Automation library, and then a wrapper around it was created in A2000, but Microsoft removed it in A2007 (and provided no alternative at all). MS could choose to remove the FileDialog object in future versions of Access and then your code would break. But the API call is never going to break.