refering to the msoFileDialogueOpen variants

smndnm · #1 07-14-2014, 12:29 AM

Hello,

How does one refer to the files selected by this code?

Code:

 
  Dim dlgOpen As FileDialog
 Set dlgOpen = Application.FileDialog( _
 FileDialogType:=msoFileDialogOpen)
 With dlgOpen
 .AllowMultiSelect = True
 .Show
 End With

I can count them with:

Code:

dlgOpen.SelectedItems.Count

But I am not understanding how to reference them and thus specifically process them (given that these will change at each running of the macro)
The intention is:
open and update doc1, click the "update all other docs" button.
This button saves doc1, opens the dialogue to multiselect the other required docs and then invokes a loop(?) to run through an "update-saveas" process on those docs.

I am going guess at:

Code:

Dim i As Integer
Dim "dlgopen.SelectedItems" as Variant
For i = 1 to dlgOpen.SelectedItems.Count
' Open Variant, Update links, Save As, Close
Next i

I think I can do the open, save-as and close (I have a working macro for this), but the question is:
How do I refer to each file selected so as to be able to process them?

Many Thanks from Queensland.

macropod · #2 07-14-2014, 09:41 PM

Basically:

Code:

Sub Demo()
Dim i As Long
With Application.FileDialog(msoFileDialogOpen)
  .AllowMultiSelect = True
  If .Show = -1 Then
    For i = 1 To .SelectedItems.Count
      'Do your prcessing here
      MsgBox .SelectedItems(i)
    Next
  End If
End With
End Sub

smndnm · #3 07-14-2014, 11:11 PM

Thanks Paul,

I've been working (cutting & pasting from the interweb) on this all afternoon. This is what I have: (looks just like yours)

Code:

Sub Main()

Dim i As Long
Dim tgf As String
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select Folder for..."
    .Show
    tgf = .SelectedItems(1)
End With

MsgBox tgf

 
    Dim vrtSelectedItem As Variant


    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        If .Show = -1 Then

          
            For Each vrtSelectedItem In .SelectedItems

                Documents.Open FileName:=vrtSelectedItem
                With Dialogs(wdDialogFileSaveAs)
                .Format = wdFormatPDF
                Options.DefaultFilePath(wdDocumentsPath) = tgf
                .Show
                ActiveDocument.Close


    End With


        Next

        Else

        End If

    End With


Shell "C:\program files (x86)\adobe\acrobat 11.0\acrobat\acrobat.exe", vbNormalFocus

End Sub

And it mostly works... mostly

It doesn't keep the folder path as initially selected. I can see I've reused the .SelectedItems, but I have no clue how to manage this. I would like some advice on this.

I need to work out how to SaveAs silently unless the file already exists, but I'll keeping digging and start a new thread when I give up in frustration.

Regards from an overcast and slightly rainy Queensland (the locals are running around crying "the sky is falling, the sky is falling...)

macropod · #4 07-15-2014, 12:02 AM

Perhaps if you explained what you're trying to accomplish (this is apparently part of a larger project), a rather less piecemeal approach might be taken...

smndnm · #5 07-15-2014, 12:22 AM

It changed as I saw what could be done. I don't want a spoonfed answer, I want to understand the how, what, when and where. Nor do I wish to be seen as another "you-must-help-give-me-answers-now" type of poster as I have seen elsewhere on the interweb. This is why I have asked limited questions, so I can plug all the smaller parts together myself.

At this time I would like(subject to awareness):
a button in a document when pressed that will:
save the current document (for the bookmarks) and then select multiple .dotx and open them and update the links, modify the file name and the properties and save each of them to a specified common folder (that changes on each macro run) as pdf's with as minimal user interaction as possible and a small polite "no error" notification at the end

So far I have 80% (mostly thanks to you). Missing is:
Silently SaveAs pdf unless file exists (unlikely but one never knows)
SaveAs into the specified folder (I can see what is wrong, but clueless how to resolve it)

I am sure I'll have further assembly issues, but I am prepared to try to fix them myself before asking for help.
I intend to post the entire project 'withcomments back to this forum once it works, because I feel it is a worthwhile project with some good examples of practical macro code (observer bias aside) I didn't expect to have a fully automated workflow when I started, I just wanted to populate many document templates from a single doc.
One might be so bold to suggest it is all your fault for showing me the power and the depth of what a Word VBA macro can do, thus absolving myself of any responsiblity to behave appropriately in a forum...

Regards from Queensland

macropod · #6 07-15-2014, 01:08 AM

Why do the dotx files need to be opened? Or are you creating new documents based on them - an entirely different proposition?

For an active document, updating links can be as simple as:
ActiveDocument.Fields.Update

Silently saving a file merely requires the use of the SaveAs2 method. For example:

Code:

    With ActiveDocument
      .SaveAs2 FileName:=StrPth & StrNm & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With

where StrPth & StrNm represent the path & name, respectively. If you precede that with a Dir test for the presence of the files, you can invoke that process or the dialogue, as the case requires - or you can skip the test and simply let the macro overwrite any existing files. StrPth can be pre-coded or captured from the FileOpen dialogue. For example:

Code:

Sub Demo()
Dim i As Long, StrPth As String, StrNm As String, wdDoc As Document
With Application.FileDialog(msoFileDialogOpen)
  .AllowMultiSelect = True
  If .Show = -1 Then
    StrPth = Left(.SelectedItems(1), InStrRev(.SelectedItems(1), "\"))
    'Process each document
    For i = 1 To .SelectedItems.Count
      Set wdDoc = Documents.Open(FileName:=.SelectedItems(i), ReadOnly:=True, AddToRecentFiles:=False)
      With wdDoc
        .Fields.Update
        StrNm = .Bookmarks("docnumber").Range.Text & _
          .Bookmarks("doctype ").Range.Text & _
          .Bookmarks("sitename").Range.Text & _
          .Bookmarks("personname").Range.Text
        .BuiltInDocumentProperties("Title") = StrNm
        'Do your processing of the opened document here
        .SaveAs2 FileName:=StrPth & StrNm & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    Next
  End If
End With
End Sub

smndnm · #7 07-15-2014, 01:38 AM

The .dotx files are (QA controlled) templates from which we create new documents (and populate with customer data) and save those new documents as pdf (for securing the content and transmittal) into the relevant customer folder.
The .docx (generated from the .dotx) is discarded. We only require the pdf.

I was hoping to use the msoFileDialogueFolderPicker as it seemed an elegant way to select the destination file. I am just a bit dumb as to how this string(?) is kept until it is needed later as my "targetfolder" string seemed to get discarded once the FileMultiselect was invoked.
I am sure it'll make sense when I add my own notes through the working macro.

I'll plug your code into the project tomorrow, thank you for your time and effort with this.

Regards from a suffering Reds supporter.

macropod · #8 07-15-2014, 01:47 AM

To pick a path, & fallback to the source file's folder (if nothing is chosen), you could change:
StrPth = Left(.SelectedItems(1), InStrRev(.SelectedItems(1), "\"))
to:
StrPth = GetFolder & "\"
If StrPth = "\" Then StrPth = Left(.SelectedItems(1), InStrRev(.SelectedItems(1), "\"))
and add the following function to the code module:

Code:

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

smndnm · #9 07-15-2014, 05:03 PM

I'll need sometime to digest your code. However I have this so far which appears to work quite well. I'll fully test it today. There are aspects here that I do not understand, mostly about the when and how of declaring and retreiving variables. But I have a result of sorts.

Code:

Sub Main()

Dim i As Long
Dim tgf As String
Dim filenametitle As String
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select Folder for..."
    .Show
    tgf = .SelectedItems(1)
End With

MsgBox tgf

 
    Dim vrtSelectedItem As Variant


    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        If .Show = -1 Then

          
            For Each vrtSelectedItem In .SelectedItems

                Documents.Open FileName:=vrtSelectedItem
                Options.DefaultFilePath(wdDocumentsPath) = tgf
                filenametitle = ActiveDocument.BuiltInDocumentProperties("Title")
                With Dialogs(wdDialogFileSaveAs)
                .Name = filenametitle
                .Format = wdFormatPDF
                .Execute
                ActiveDocument.Close SaveChanges:=False


    End With


        Next

        Else

        End If

    End With


Shell "C:\program files (x86)\adobe\acrobat 11.0\acrobat\acrobat.exe", vbNormalFocus

End Sub

macropod · #10 07-15-2014, 09:42 PM

If you want to open the PDF, then instead of using:
.SaveAs2 FileName:=StrPth & StrNm & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
in my code or:
Shell "C:\program files (x86)\adobe\acrobat 11.0\acrobat\acrobat.exe", vbNormalFocus
in your code, you could use:
.ExportAsFixedFormat OutputFileName:=StrPth & StrNm & ".pdf", _
ExportFormat:=wdExportFormatPDF, OpenAfterExport:=True
with my code (or the equivalent in your code).

smndnm · #11 07-15-2014, 10:58 PM

I don't want to open the PDFs, I need to batch process them to apply security settings, it is an attempt to open an instance of Adobe pro locally if not already open.

I am so close, I can taste it...
There are only two issues with the code below and it is still valid inside this thread.

issue1 = It throws an exception at the"msoFileDialogFilePicker" stage when the user presses cancel. I cannot make the previous solution work (If .Show = -2 Then Exit Sub).
Issue2 = It doesn't remember the variable "target1" when I save the PDF and thus won't select the chosen location. it works when all docs are local on my laptop, but it has fallen over when it tested on the network.

When it works correctly, I'll change the .Show for the .Execute, which means the user has nothing to do except acknowledge the end.

I would very much like some help with identifying what changes need to be made to fix these two issues.

Code:

Private Sub CommandButton1_Click()

Dim MBxAns1 As Long
Dim MBxAns2 As Long
Dim i As Long
Dim target1 As String
Dim filenametitle As String
Dim StrNm As String
Dim vrtSelectedItem As Variant

MBxAns1 = MsgBox("Did you update the SWMS Number?", vbOKCancel, "Bunny Check...")
If MBxAns1 = vbCancel Then Exit Sub


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

MBxAns2 = MsgBox(target1, vbOKCancel, "The Destination Folder is...")
If MBxAns2 = 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

                Documents.Open FileName:=vrtSelectedItem
                Options.DefaultFilePath(wdDocumentsPath) = target1
                
                With ActiveDocument
                .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
                filenametitle = ActiveDocument.BuiltInDocumentProperties("Title")
                 End With
                
                
                With Dialogs(wdDialogFileSaveAs)
                .Name = filenametitle
                .Format = wdFormatPDF
                .Show
                '.Execute
                ActiveDocument.Close SaveChanges:=False


    End With


        Next

        Else

        End If

    End With
    
    MsgBox "remember to secure the PDF before sending"
    ActiveDocument.Close SaveChanges:=False
    
End Sub

Regards from Queensland

macropod · #12 07-16-2014, 06:22 AM

Quote:

Originally Posted by smndnm

I don't want to open the PDFs, I need to batch process them to apply security settings, it is an attempt to open an instance of Adobe pro locally if not already open.

In that case, you'd probably do better to automate Acrobat Pro (assuming that's what you're using).

Quote:

issue1 = It throws an exception at the"msoFileDialogFilePicker" stage when the user presses cancel. I cannot make the previous solution work (If .Show = -2 Then Exit Sub).
Issue2 = It doesn't remember the variable "target1" when I save the PDF and thus won't select the chosen location. it works when all docs are local on my laptop, but it has fallen over when it tested on the network.

These issues could be avoided if you used the function I provided you with instead of your first call to the file dialogue. All you'd need to call the Function with is:
target1 = GetFolder & "\"
If target1 = "\" Then Exit Sub

smndnm · #13 07-16-2014, 05:20 PM

I'll redo the macro today with your code. however my curiosity won't let go of why it doesn't work. Provoking my curiosity is; when I insert the code below right at the end, it works exactly as I expect. So I am terribly confused why "target1" doesn't work further up the macro.
In my world, I'd rather get a result than be right, but I am intensly curious none the less.

Code:

MsgBox ("remember to secure the PDF before sending")
    Call Shell("explorer.exe """ & target1 & "", vbNormalFocus)
End Sub

I'll get back later today with a result.

Regards from a sunny Queensland.

smndnm · #14 07-16-2014, 08:24 PM

not really a result...
I am clearly well out of my depth. I have exactly the same result... with the addition of not knowing how to preselect the starting folder when selecting the destination folder (a sub-folder of the preselected folder)
It doesn't correctly select the destination SaveAs folder, even though the last instruction is to open the destination folder (which it does correctly).
It opens the Templates folder on my local drive.

Code:

 
Private Sub CommandButton1_Click()


Dim MBxAns1 As Long
Dim MBxAns2 As Long
Dim i As Long
Dim target1 As String
Dim filenametitle As String
Dim StrNm As String
Dim vrtSelectedItem As Variant

MBxAns1 = MsgBox("Did you update the SWMS Number?", vbOKCancel, "Bunny Check...")
If MBxAns1 = vbCancel Then Exit Sub



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



MBxAns2 = MsgBox(GetFolder, vbOKCancel, "The Destination Folder is...")
If MBxAns2 = 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

                Documents.Open FileName:=vrtSelectedItem
                Options.DefaultFilePath(wdDocumentsPath) = GetFolder
                
                With ActiveDocument
                .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
                filenametitle = ActiveDocument.BuiltInDocumentProperties("Title")
                 End With
                
                
                With Dialogs(wdDialogFileSaveAs)
                .Name = filenametitle
                .Format = wdFormatPDF
                .Show
                '.Execute
                ActiveDocument.Close SaveChanges:=False


    End With


        Next

        Else

        End If

    End With
    
    MsgBox ("remember to secure the PDF before sending")
    Call Shell("explorer.exe """ & GetFolder & "", vbNormalFocus)


End Sub

I can feel how close this is to being a great result, but I can't seem to get through this issue. To my mind, it reads correctly, but the evidence shows that it doesn't work. I really would like to better understand why this isn't working...and I don't understand how GetFolder and oFolder are interacting, but I'll get there.

Regards from Queensland

macropod · #15 07-17-2014, 03:24 AM

Try the following. It's still not apparent, though, why you want to use the SaveAs dialogue given that you expressed a desire for a 'silent' save and that the .ExportAsFixedFormat method allows you to force the saved PDF to open so you can do the securing without the need for an additional process.

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
 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
 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

Note that I've commented-out the last line for testing.

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