refering to the msoFileDialogueOpen variants

smndnm · #16 07-17-2014, 06:11 PM

thank you so much, it works as expected. It appears there are many roads to Rome and quite few that don't

I used SaveAs simply because it worked in way I understood, and not because I knew it would work.

I see you have given the .Name a full folder path AND the filename, this solves the problem I was facing. This does makes sense, but then so did my way and I am non the wiser for why, which shows only that I have no idea what I am looking at.

Thank you very much for your patience and your sharing of knowledge. The beer is in the post.

Regards from a very windy Queensland

smndnm · #17 07-17-2014, 06:30 PM

Oops, wait, I've spoken too soon... (but the beer is already on its way...)
The folder selection doesn't work.
for example I have selected the folder "\\Customers L-Z\T\Test"
but the save as dialogue only takes me to "\\\Customers L-Z\T". Which will prevent the full auto workflow.

Clearly I'll never work this out myself. Would please help with this?

Regards Queensland

smndnm · #18 07-17-2014, 07:15 PM

I've jumped the gun and undersold myself.
I've added .. & "\" .. and its all good now.

Cheers.

macropod · #19 07-18-2014, 12:04 AM

Well done. I thought of that too, but I've been otherwise occupied and couldn't get back to this until now.

smndnm · #20 07-19-2014, 07:54 PM

Hello,

This code currently does NOT exit gracefully when clicking cancel.

It causes a "Run time Error '5': invalid proedure call or argument."

I am thinking the problem occurs because there is no possible value to assign to 'Fldr', but then I also would have thought 'Exit Sub' meant Exit Sub now!!

Code:

 With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = "G:\Admin - MASTER\Customers\"
    .AllowMultiSelect = False
    .Title = "Select Destination Folder for the SWMS"
    If .Show = -2 Then Exit Sub
    Fldr = .SelectedItems(1)
End With

any suggestions?

Regards from a brisk Queensland

macropod · #21 07-19-2014, 10:12 PM

As we've previously discussed, you could use the GetFolder function I've previously supplied and demonstrated...

smndnm · #22 07-20-2014, 01:33 AM

I really struggled with understanding the code you supplied. I couldn't work out how to preset the starting folder. Neither do I understand about Functions. (keeping in mind the whole project has been developed by semi-mindless fingers crossed copy paste and hope..)

I'll give it another shot though.

The code above presumes the user will select OK to continue. Would it work if the code presumes the user selects cancel and only explicitly continues on OK.
I am thinking something like:

If .Show = -1 then continue, Else End Sub. ( I am not sure how to write this correctly, I'll try again tomorrow)

Regards from Queensland

macropod · #23 07-20-2014, 02:34 AM

You could use:

Code:

Private Sub CommandButton1_Click()
Dim MBxAns
Dim i As Long
Dim Fldr As String
Dim StrNm As String
Dim vrtSelectedItem As Variant
Dim wdDoc As Document
MBxAns = MsgBox("Did you update the SWMS Number?", vbYesNo, "Bunny Check...")
If MBxAns <> vbYes Then Exit Sub
Fldr = GetFolder & "\"
If Fldr = "\" Then Exit Sub
MBxAns = MsgBox(Fldr, vbOKCancel, "The Destination Folder is...")
If MBxAns = vbCancel Then Exit Sub
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = "G:\QMS\OH&S"
    .Title = "Select the SWMS Templates"
    .AllowMultiSelect = True
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
            Set wdDoc = Documents.Open(FileName:=vrtSelectedItem)
            With wdDoc
                .Fields.Update
                StrNm = "SWMS " & .Bookmarks("SWMSNumber").Range.Text & " " & _
                    .Bookmarks("SWMSType").Range.Text & " - " & _
                    .Bookmarks("PrimaryContractor").Range.Text & " - " & _
                    .Bookmarks("ProjectName").Range.Text
                    .BuiltInDocumentProperties("Title") = StrNm
                    .BuiltInDocumentProperties("Subject") = .Bookmarks("SWMSType").Range.Text
                With Dialogs(wdDialogFileSaveAs)
                    .Name = Fldr & StrNm & ".pdf"
                    .Format = wdFormatPDF
                        .Show
                    End With
                    .Close SaveChanges:=False
                End With
            Next
        End If
    End With
    MsgBox "remember to secure the PDF before sending"
    'ActiveDocument.Close SaveChanges:=False
End Sub
  
 Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

You'll not the BrowseForFolder method takes four arguments. These are detailed at: http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx . The last of these allows you to set a root folder. This isn't just a starting folder, though. If you specify "G:\Admin - MASTER\Customers\", your users won't be able to browse to any higher folder. So you might need to use "G:\Admin - MASTER\", or even '"G:\".

Alternatively, if you're wedded to using 'Application.FileDialog(msoFileDialogFilePicker)', you could change:

Code:

    If .Show = -2 Then Exit Sub
    Fldr = .SelectedItems(1)

to:

Code:

    If .Show = -1 Then
      Fldr = .SelectedItems(1)
    Else
      Exit Sub
    End If

smndnm · #24 07-20-2014, 04:55 PM

yeah, I am wedded to the mso..FilePicker, only because it makes sense to me at this time. (reminds me of BASIC at tech school last century) Thanks for everything, I am really very impressed with this workflow project as it is very effective at reducing duplication. I am very grateful for your time and this forum.

I'll be paying close attention to our other processes to see what else can be done.

Thanks for all your help.

Regards from Queensland

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Refering figure number too text	danzi	Word	1	01-20-2012 12:13 PM
Refering to photos on other pages	woodfind	Word	1	05-17-2010 01:52 AM