Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    South of Boston, Massachusetts, USA
    Posts
    54
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Macro to open any file in a directory (Word 2002 SP1)

    I don't even know how to search for this question, please forgive me. I'm barely competent in macro writing.

    I have a task of opening Microsoft Publisher documents in Word and extracting some information. What I did was write the following, hoping that it would open the directory and allow me to open any file:

    Sub Bruce1()
    '
    ' Bruce1 Macro
    ' Macro recorded 11/2/2006 by Ann Rein
    '
    Documents.Open FileName:="faxABSco.pub", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=13
    End Sub

    Unfortunately I have to select a file - I'd rather just get to the point where I'm in the proper directory and then be able to select a file, that way I can work my way through the file listing extracting one file after another. What I thought I could do was change the file name to ' *.pub ', using a wildcard to select any file. No joy.

    Is this possible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to open any file in a directory (Word 2002 SP1)

    You can't just open a Publisher document in Word - the file formats of Word and Publisher aren't compatible.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    South of Boston, Massachusetts, USA
    Posts
    54
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro to open any file in a directory (Word 20

    Yes, I can open Pub files in Word using "recover text from any file *.*' in the File type dropdown. I've been opening them by hand to extract the data I need, I was thinking there may be a way to automate at least the opening. In the macro I recorded, I included the step of dropping down the file type listing and selecting that option, but I can't see that it actually happened in the macro. Maybe I can't do this at all, but I'm hoping....I've got 540 pub files to extract data from!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to open any file in a directory (Word 20

    Does this do what you want?
    <code>
    Sub OpenPublisher()
    With Application.Dialogs(wdDialogFileOpen)
    .Name = "*.pub"
    .Show
    End With
    End Sub</code>

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro to open any file in a directory (Word 20

    Hi,
    FYI, the <code>Format = 13</code> bit refers to the "Recover text from any file" converter.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    South of Boston, Massachusetts, USA
    Posts
    54
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Macro to open any file in a directory (Word 20

    No, Hans, the problem is I don't have Publisher. If I did, then I'd just open it and copy and paste.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro to open any file in a directory (Word 20

    Does this work - change the path as appropriate (I've assumed all the files are in the same directory):
    <pre>Sub OpenPubFiles()
    Dim strFolder As String, strFile As String
    Dim docTemp As Document
    strFolder = "C:test*.pub"
    strFile = Dir(strFolder)
    Do While strFile <> ""
    Set docTemp = Documents.Open(FileName:=strFile, ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=13)
    docTemp.SaveAs strFile & ".doc", wdFormatDocument
    docTemp.Close
    strFile = Dir
    Loop
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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