Consulting

Results 1 to 4 of 4

Thread: How to include excel files?

  1. #1
    VBAX Tutor
    Joined
    Jul 2016
    Posts
    266
    Location

    How to include excel files?

    Hello everyone. Hope you are well. I have pieced together the following code but I need to include the possibility that an excel file type may be selected. Any guidance appreciated.

    Sub DeleteTypesOfFiles()
    'Kilroy
    Dim strFilename As String
    Dim strDocName As String
    Dim strPath As String
    Dim oDoc As Document
    Dim fDialog As FileDialog
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fDialog
        .Title = "Select folder and click OK"
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then
            Exit Sub
        End If
        strPath = fDialog.SelectedItems.Item(1)
        If Right(strPath, 1) <> "\" Then strPath = strPath + "\"
    End With
    If Documents.Count > 0 Then
        Documents.Close SaveChanges:=wdPromptToSaveChanges
    End If
    If Left(strPath, 1) = Chr(34) Then
        strPath = Mid(strPath, 2, Len(strPath) - 2)
    End If
    strFilename = Dir$(strPath & InputBox("File type? aterisk.type"))
    While Len(strFilename) <> 0
    Set oDoc = Documents.Open(strPath & strFilename)
         MyFile = ActiveDocument.path & "\" & ActiveDocument.Name
         ActiveDocument.Close (wdDoNotSaveChanges)
         Kill MyFile
         strFilename = Dir$()
    Wend
    End Sub

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    It's not apparent to me why you have:
         Set oDoc = Documents.Open(strPath & strFilename)
         MyFile = ActiveDocument.path & "\" & ActiveDocument.Name
         ActiveDocument.Close (wdDoNotSaveChanges)
         Kill MyFile
    There is no point I can see in opening the document, only to close & delete it. Surely all you need is:
         Kill strPath & strFilename
    In any event:
         MyFile = ActiveDocument.path & "\" & ActiveDocument.Name
    could be reduced to:
         MyFile = oDoc.FullName
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Tutor
    Joined
    Jul 2016
    Posts
    266
    Location
    Paul thanks for your input. It wasn’t apparent to me whythat statement was in there either, so I removed it and ran a couple of testsusing your suggestions. It really was what was causing all of my issues withthe different file types. I also used the “Kill strPath & strFilename” andthe code now doesn’t need the “MyFile” statements at all. Runs very smooth andmuch quicker.

    Question: You moved this post to Misc? I was asking a wordvba question so it’s unclear to me why it should be here.

    Thanks again!


  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Kilroy View Post
    Question: You moved this post to Misc? I was asking a wordvba question so it’s unclear to me why it should be here.
    Indeed, because without the deleted lines and with your requirement to include Excel, for example, nothing in the code is Word-specific - it could apply equally to any Office application.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •