VBA Help Please

Entai · 19 Nov 2016 at 14:57

I have a small piece of code

Sub FormatIT()
Dim defpath As String
defpath = Options.DefaultFilePath(wdDocumentsPath)
Options.DefaultFilePath(wdDocumentsPath) = "Insert My Directory Path Here"
With Dialogs(wdDialogInsertFile)
.Name = "*.txt"
.Show
End With
Options.DefaultFilePath(wdDocumentsPath) = defpath

The issue is that when the dialog box opens, in the directory path entered, it opens only looking for word documents, and I need it to open looking for all file types so that .txt files will be shown.

Any Ideas please ?

I know its dead easy to click on the drop down box and change to all file types, but was hoping the VBA code could do that part as well.

dazzerd · 20 Nov 2016 at 19:30

Hmm could be helpful if you posted you whole script but what this seems to be is a file picker script and to be fair I have never seen it done this way:

I tend to use this or a derivative of it:

Public Function FilePicker() As String

Dim fDialog As FileDialog, result As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

'Optional: FileDialog properties
fDialog.AllowMultiSelect = False
fDialog.Title = "Select a file"
fDialog.InitialFileName = "C:\"
'Optional: Add filters
fDialog.Filters.Clear
fDialog.Filters.Add "Excel files", "*.xlsx"
fDialog.Filters.Add "All files", "*.*"

'Show the dialog. -1 means success!
If fDialog.Show = -1 Then
FilePicker = fDialog.SelectedItems(1)
End If

End Function

you can then add your available types in .filters. You may have a similar method available to your object but as I say I've never seen it before and I've seen a hell of a lot vba code! You need to add a reference to microsoft scripting runtime for it to work by the way.

Entai · 21 Nov 2016 at 20:56

Hi thanks for the info.

Yes its a file picker, the rest of the script then works on the file picked to reformat it into a neater word file than the original plain txt file.

The script works fine it opens a dialog box in the designated directory, but as I say all I need is for the script to open the dialog box in the selected directory BUT WITH "All Files (*.*)" selected in the drop down menu, rather than only "All Word Documents" like it does currently.

The full script (not that I think it helps any) is;
(and yes I know parts of it can be tidied and done in more efficient ways, but it works so that is all I really acre about.)

Sub FormatIT()

' Open dialog to load the start sheet
With Dialogs(wdDialogInsertFile)
Dim defpath As String
'Get the present default filepath for documents
defpath = Options.DefaultFilePath(wdDocumentsPath)
'Change it to current meet reports directory
Options.DefaultFilePath(wdDocumentsPath) = "C:\SPORTSYS\SSMeet\Meets\ICMeet16\reports"
With Dialogs(wdDialogInsertFile)
.Name = "*.txt"
.Show
End With
'Change the default filepath for documents back to the original default
Options.DefaultFilePath(wdDocumentsPath) = defpath
End With

' Set columns and style
ActiveDocument.PageSetup.TextColumns.SetCount NumColumns:=1
ActiveDocument.Range(Start:=0).Style = "HeatStartList"

Selection.HomeKey Unit:=wdStory
Selection.MoveDown Unit:=wdLine, Count:=4, Extend:=wdExtend
Selection.Delete

icount = ActiveDocument.Paragraphs().Count
mcount = icount
lastpara = ""

'do for all paragraphs
For pcount = 1 To icount
' get the heat data
para = ActiveDocument.Paragraphs(pcount)

'0-9 fix - SDS 13/02/2010
If Left(para, 1) = "0" Then
GoTo carryon
End If

If Val(para) > 0 Then
carryon:
newpara = heat & Chr$(9) & "L "
If newpara = lastpara Then
newpara = Chr$(9) & "L "
Else
lastpara = newpara
End If
If Right(para, 3) = vbTab & vbCrLf Then para = Trim(Left(para, Len(para) - 3)) & vbCrLf
ActiveDocument.Paragraphs(pcount).Range = newpara & para
Else

' look for Event
para = ActiveDocument.Paragraphs(pcount)
If Left(para, 5) = "Event" Then
ActiveDocument.Paragraphs(pcount).Range.Style = "BoldTitles"
' Do not insert Cr before Event Title
' If pcount <> 1 Then
' ActiveDocument.Paragraphs(pcount).Range.InsertBefore vbCr
' mcount = mcount + 1
' pcount = pcount + 1
' End If
lastpara = ""
End If

' look for heat number and store it
para = ActiveDocument.Paragraphs(pcount)
If Left(para, 4) = "Heat" Or Left(para, 4) = "Fast" Or Left(para, 4) = "Semi" Or Trim(para) = vbCrLf Then
If Left(para, 4) = "Heat" Or Left(para, 4) = "Fast" Then heat = "H" & Str(Val(Right(para, 4)))
If Left(para, 4) = "Semi" Then heat = "S" & Str(Val(Right(para, 4)))
If Trim(para) = vbCrLf Then heat = "F "
With ActiveDocument.Paragraphs(pcount)
.Range.Delete
.Range.InsertAfter vbCr
End With
End If

para = ActiveDocument.Paragraphs(pcount)
If Left(para, 5) = "Final" Or Left(para, 7) = "B Final" Then
final = True
End If

' look for Lane header and delete it
para = ActiveDocument.Paragraphs(pcount)
If Left(para, 4) = "Lane" Then
ActiveDocument.Paragraphs(pcount).Range.Delete
mcount = mcount - 1
If final = True Then
pcount = pcount - 1
final = False
End If
End If

End If

If pcount = mcount Then Exit For
Next pcount

ActiveDocument.PageSetup.TextColumns.SetCount NumColumns:=2
ActiveDocument.PageSetup.TextColumns.EvenlySpaced = True
ActiveDocument.PageSetup.TextColumns.LineBetween = True

Selection.EndKey Unit:=wdStory
Selection.HomeKey Unit:=wdStory

'Delete Competitor Numbers'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "\(*^t"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindEnd
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

andshrew · 22 Nov 2016 at 13:48

I'm pretty sure that with the type of dialog that you're using you cannot set what file types it defaults to.

You can replicate what the InsertFile dialog is doing though using the dialog example dazzerd gave. You use that dialog (which you can customise as you like) and then pass the selected file path to Word's InsertFile function (this is what the built in dialog you're using is actually doing).

You can just replace your dialog code with the following, and after the InsertFile you can continue with the rest of your code as normal.

Code:

Option Explicit

Sub filePickerExample()

    Dim dialog As FileDialog
    Set dialog = Application.FileDialog(msoFileDialogFilePicker)
    
    '   Configure and display the file picker.
    With dialog
        .Title = "Select a File"
        .AllowMultiSelect = False
        .Filters.Add "All Files", "*.*"
        .Show
    End With
    
    '   If a file has been selected, insert it into the current Word document.
    If dialog.SelectedItems.Count = 1 Then
    
        Dim path As String: path = dialog.SelectedItems(1)
        Selection.InsertFile FileName:=path, Range:="", ConfirmConversions:=False, Link:=False, Attachment:=False
    End If
    
End Sub