Excel built-in dialogs documentation 2

[woja]

The supplied documentation (and that on MSDN) for the .Show method of the Dialog object provides some information on the arguments that can be passed.

This list is very terse, simply listing names for the arguments. Some of these are obvious; some confusing but fathomable; the rest are just inscrutable.

I could spend hours experimenting to discern their meaning, but I can't imagine that I'm the first to experience this problem.

Can anyone help?

Thanks

Roger J Coult
Allied Laboratory Services Limited
Grimsby, UK

 

[xlbo]

which app ???

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[woja]

1. The forum is VBA
2. The subject is "Excel built-in dialogs documentation"

I hope these clues help.



Roger J Coult
Allied Laboratory Services Limited
Grimsby, UK

 

[xlbo]

I'm obviously going blind - mea culpa ;-)
Apologies but I can't help other than with some specific examples that I know. Misread the title and was just prompting in case anyone else could answer



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[dyarwood]

What are you wanting to know about them? There is a list of Dialog box arguments in the help files. Is there any specific one's you want to know about?

 

[woja]

Geoff
Thanks anyway.
Apologies for the sarcasm.

dyarwood
The problem is that all there is a list and the names aren't exactly helpful (sometimes).

My focus is xlDialogOpen and xlDialogOpenText.

I've done a bit of playing and found out some things (e.g, that the "format" argument (Arg4 for xlDialogOpen) has to be a number between 1 and 4. But what does it mean?

If you have any information, it would help.

Thanks




Roger J Coult
Allied Laboratory Services Limited
Grimsby, UK

 

[dyarwood]

Are you using the dialog method like this?

dlgAnswer = Application.Dialogs(xlDialogOpen).Show

If not how are you using it?

 

[woja]

Yes.

Maybe I should explain:

I want the use to select a .CSV file for opening in Excel and then I want to do some work on the data.

All I originally wanted to do was to re-use the standard file open dialog to get a file name. The rest is easy. But it has all become rather a pain. I've spent far too long on this and will (possibly) have to build a UserForm to do the same thing if it doesn't do what I want.

I just want to know what types are used for the arguments and what they mean (this is not in the documentation) and then all will be fine and dandy (and hunky-dory).



Roger J Coult
Allied Laboratory Services Limited
Grimsby, UK

 

[dyarwood]

Could you use the Import External Data under the tools tab. If you record the code it will look like this

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;filename", Destination:=Range("A1&quot)
.Name = "des"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

I had four columns the first was set as Text format (2) and the others were set as General (1). There were two others (think date was one of them). It was a comma delimited txt file.

 

[woja]

That's fine (and will - probably - use that method) for the actual import but the bit I really want to to is for the user to select the file name (they may have a number of CSV files to process).

But, thanks anyway.

I appreciate your efforts.

Roger J Coult
Allied Laboratory Services Limited
Grimsby, UK

 

[dyarwood]

Could you use an input box for the filename. Then use the string from that in the above code. Sorry I can't be of more help. What happens when the format is 1,2,3 or 4?

dyarwood

 

[xlbo]

woja - have a look at the Workbook.Open documentation - here is the help file ref - arguments in the dialog box should compare to the arguments in the OPEN method:

Open Method
See Also Applies To Example Specifics
Open method as it applies to the Workbooks object.

Opens a workbook.

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad, OpenConflictDocument)

expression Required. An expression that returns the Workbooks object.

FileName Required String. The file name of the workbook to be opened.

UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references

If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 2, Microsoft Excel generates charts from the graphs attached to the file. If the argument is 0, no charts are created.

ReadOnly Optional Variant. True to open the workbook in read-only mode.

Format Optional Variant. If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used.

Value Delimiter
1 Tabs
2 Commas
3 Spaces
4 Semicolons
5 Nothing
6 Custom character (see the Delimiter argument)

Password Optional Variant. A string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password.

WriteResPassword Optional Variant. A string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password.

IgnoreReadOnlyRecommended Optional Variant. True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).

Origin Optional Variant. If the file is a text file, this argument indicates where it originated (so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly). Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.

Delimiter Optional Variant. If the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use "," for commas, use ";" for semicolons, or use a custom character. Only the first character of the string is used.

Editable Optional Variant. If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it’s a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option doesn't apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, True to open the specified template for editing. False to open a new workbook based on the specified template. The default value is False.

Notify Optional Variant. If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.

Converter Optional Variant. The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter doesn’t recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.

AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False.

Local Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

CorruptLoad Optional Variant. Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The Default behavior if no value is specified is usually normal but may be safe load or data recovery, if Excel has already attempted to open the file. The first attempt is normal. If Excel stops operating while opening the file the second attempt is safe load. If Excel again stops operating the next attempt is data recovery.

OpenConflictDocument Optional Variant. True to open the local conflict document. Default is False.


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[woja]

Geoff
Thanks a bunch

Talk about missing the blindingly obvious: I should have thought of that. Makes a lot of sense.

Thanks for all the help peeps.

If you're ever in my neck of the woods, there's a pint waiting on the bar of my local...



Roger J Coult
Allied Laboratory Services Limited
Grimsby, UK

 

[dyarwood]

Nice one xlbo. Worthy of a star i would say

 

[xlbo]

Grimsby may be a bit far to go for a pint but then again.......

Glad I could help anyway - 'specially considering my missing the blindingly obvious with my 1st post ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[woja]

Just to let you know that after a day-and-a-half of trying to sort this idea out. I find that the function I want is in Excel. This was pure chance. I was aware of the method but (wrongly) assumed what it did from its name.

GetOpenFilename()

From the name I assumed it returned the name of the currently open file (the current workbook).

Hovever the name should be interpreted as "get the name of a file for opening" rather than "get the name of an open file". Thanks Microsoft.

By the eay, there's also

GetSaveAsFilename()

, which makes the naming strategy clearer.

It's part of the

Application

object.

Ho, hum....





Roger J Coult
Allied Laboratory Services Limited
Grimsby, UK