We help IT Professionals succeed at work.

xlDialogOpenText dialog use

aikimark
aikimark used Ask the Experts™
on
I can't seem to get my VBA code to open the Text Import wizard in an Excel2003 environment.

It doesn't like any of the following:
Application.Dialogs(xlDialogOpenText).Show
Application.Dialogs(xlDialogOpenText).Show (parmSelectedFolder & "\ImportFmt3.Txt")

Open in new window


I've found old (Excel97) MS documentation stating that not all the enumerations work, but surely they've fixed that by now.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Well, Application.Dialogs(xlDialogOpen).Show is opening the file open dialog but if you want to import a text file with xlDialogOpenText you have to specify a lot more parameters like these:
:
file_name, file_origin, start_row, file_type, text_qualifier, consecutive_delim, tab, semicolon, comma, space, other, other_char, field_info
Top Expert 2014

Author

Commented:
I need the user to supply some of the information to the opentext dialog wizard.  The problem I face is that sometimes, the user wants to override the (default) text import actions of the application.
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Well, I think the issue is that you can't just launch the wizard though VBA, if you record a macro doing that, the result will be a "Workbooks.OpenText ..." piece of code.

You could present the user with a file selection dialogue like this:

filePath = Application.GetOpenFilename("CSV (Comma Delimited) (*.csv),*.csv", 1, "Select a file", , False)

Then followed by a Workbooks.OpenText ... with some default values set.

I'll try to to find you a method on launching the wizard but I doubt it is possible.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Top Expert 2014

Author

Commented:
@rorya

Yes.  I stated that I'd read the Excel97 KB article in my question.
Most Valuable Expert 2011
Top Expert 2011

Commented:
I overlooked that! I don't think they intend to fix those, but can you not just use:
application.Dialogs(xlDialogImportTextFile).Show
instead?
Top Expert 2014

Author

Commented:
@rorya

Unfortunately, there are no arguments for the xlDialogImportTextFile dialog.
http://msdn.microsoft.com/en-us/library/aa221648(office.11).aspx

In this case, I know the path and name of the file to be imported.  I want to present the user with the text import wizard without first requiring them to select the file.
Most Valuable Expert 2011
Top Expert 2011
Commented:
I don't think you can use anything built-in unless you use Opentext to load the file and then use the Dialogs(xlDialogTextToColumns) to parse the loaded text.
Top Expert 2014

Author

Commented:
That seems to be the only work-around other that reinventing the import wizard or using a third-party component.