I need to edit a macro to open *.Doc and *.Docx files

Rampum15 · #1 09-08-2022, 11:22 AM

Hello,
I'll start by saying that I do not know VBA at all, so any and all help will be GREATLY appreciated! I work in a manufacturing plant, and we have a macro that is used almost everyday to open and print *.Doc files. We are now adding bar codes to new documents to call up machine programs. The problem is that we start with a *.Docx file, but then have to save it as a *.Doc file for the macro to work. Doing this changes the bar code to an static image file, which then can not be edited. I'd like to edit our macro so that it will open both *.doc and *.docx files. I've tried several modifications to the macro with no luck. The problem always seems to come back to the file extension specified in the macro. I can change it to *.Docx, but then it doesn't work with the older files. Can someone show me how to open both types of files without specifying the extension? The section of the macro that calls the file is listed below. I'm sure it's probably a simple thing to fix, I just don't know how to do it.

Thanks in advance for your help!

Documents.Open FileName:="Z:\Travellers" & File & ".doc", _
ConfirmConversions:=False, ReadOnly:=True, AddToRecentFiles:=False, _
Revert:=False, Format:=wdOpenFormatAuto, XMLTransform:=""

macropod · #2 09-08-2022, 02:41 PM

It's not the opening of the documents that is the problem; it's the file format you're saving them in. They all need to be saved in the docx format - and you can't just change the extension to achieve that. However, since you haven't posted the relevant Save code, we can't give specific help there. That said, the code to ensure the active document is saved in the docx format might look like:

Code:

With ActiveDocument
  ' Save & close the output document
  If UBound(Split(.FullName, ".doc")) = 0 Then
    .SaveAs FileName:=Split(.FullName, ".doc")(0) & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
  Else
    .Save
  End If
  .Close SaveChanges:=False
End With

Rampum15 · #3 09-09-2022, 05:09 AM

Hi Paul,
Thanks for the reply, I wasn't sure how much detail to start with. Our macro is a simple one that opens a document, does a find and replace on three fields, prints it, and then closes it without saving. All I need is a way for the macro to open the file regardless if it has a *.doc or *.docx extension. We have over 3500 documents, and we print approximately 100 per day on average. I've pasted the entire macro below for reference.

Code:

Sub Travellers()
'
    MsgBox ("IS FINEPRINT SET AS THE DEFAULT PRINTER?")
    fsDT = InputBox("Due Date is...", "Due Date", "DUE DATE")
    If File = "quit" Then Num = 1 Else Num = 0
    Do While Num = 0
        fsWON = InputBox("Enter Work Order Number or press Cancel to exit", "Work Order #", "Work Order Number")
        If fsWON = "" Then Exit Do
        File = InputBox("Name of the Part File to open?", "Open File", "Part Number")
        QTY = InputBox("Order Quantity is...", "Order Quantity", "Quantity")
        Documents.Open FileName:="Z:\Travellers" & File & ".doc", _
            ConfirmConversions:=False, ReadOnly:=True, AddToRecentFiles:=False, _
            Revert:=False, Format:=wdOpenFormatAuto, XMLTransform:=""
        With ActiveDocument
            With Selection.Find
                .ClearFormatting
                .Replacement.ClearFormatting
                .Forward = True
                .Wrap = wdFindContinue
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
                .Text = "WRKORD"
                .Replacement.Text = fsWON
                .Execute Replace:=wdReplaceAll
                .Text = "QTY"
                .Replacement.Text = QTY
                .Execute Replace:=wdReplaceAll
                .Text = "DDT"
                .Replacement.Text = fsDT
                .Execute Replace:=wdReplaceAll
            End With
            .PrintOut Background = True
            .Close SaveChanges:=wdDoNotSaveChanges
        End With
    Loop
End Sub

macropod · #4 09-09-2022, 06:37 AM

You should not be using Word documents for this - at all. You should be using Word templates. To achieve that, all you need do is open each of those documents and save it as a dotx template if it has no macros, or as a .dotm template otherwise. That way, there's never a risk of the template file being overwritten, even if someone uses it to create a document without also using the macro.

Then, instead of:

Code:

Documents.Open FileName:="Z:\Travellers" & File & ".doc", _
            ConfirmConversions:=False, ReadOnly:=True, AddToRecentFiles:=False, _
            Revert:=False, Format:=wdOpenFormatAuto, XMLTransform:=""

You'd use:

Code:

Documents.Add FileName:="Z:\Travellers" & File & ".dotx"

or, if the template has macros:

Code:

Documents.Add FileName:="Z:\Travellers" & File & ".dotm"

Converting all those documents to dotx/dotm templates is also quite straightforward:

Code:

Sub CnvtDocsToTmplts()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document
strDocNm = ActiveDocument.FullName
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
Do While strFile <> ""
  If strFolder & "\" & strFile <> strDocNm Then
    Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
    With wdDoc
      If .HasVBProject = True Then
        .SaveAs2 FileName:=Split(.FullName, ".doc")(0) & ".dotm", FileFormat:=wdFormatXMLTemplateMacroEnabled, AddToRecentFiles:=False
      Else
        .SaveAs2 FileName:=Split(.FullName, ".doc")(0) & ".dotx", FileFormat:=wdFormatXMLTemplate, AddToRecentFiles:=False
      End If
      .Close SaveChanges:=False
    End With
    Kill strFolder & "\" & strFile
  End If
  strFile = Dir()
Loop
Set wdDoc = Nothing
Application.ScreenUpdating = True
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

With the above code, all Word documents in the selected folder will be replaced by Word dotx/dotm templates with the same names.

Rampum15 · #5 09-13-2022, 08:59 AM

Hi Paul,
Thank you very much, I appreciate your information! However, I don't want to sound ungrateful, but I'm not looking to change what we're doing or how we do it. All I really need to know is if there is a way to edit our current macro so that it opens word documents regardless of their extension. If that isn't possible, then I will convert all of our files to the new *.docx extension, and modify the macro accordingly.

Speaking of converting files, I've tried a couple of macros online, but they just change the extension so the files open in compatibility mode. Do you have a macro that will actually convert the files similar to how the Open/Save As function works?

Again, thank you for your help!

macropod · #6 09-13-2022, 04:16 PM

Quote:

Originally Posted by Rampum15

I'm not looking to change what we're doing or how we do it.

From an end-user perspective, the approach I proposed is the same as you're already using.

Quote:

Originally Posted by Rampum15

All I really need to know is if there is a way to edit our current macro so that it opens word documents regardless of their extension.

It is not possible to open an individual file without supplying the correct extension.

Quote:

Originally Posted by Rampum15

If that isn't possible, then I will convert all of our files to the new *.docx extension, and modify the macro accordingly.

Converting .doc files with macros to the docx format will delete those macros. Still, if that's what you want:

Code:

Sub CnvtDocsToDocx()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, strDocNm As String, wdDoc As Document
strDocNm = ActiveDocument.FullName
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
Do While strFile <> ""
  If strFolder & "\" & strFile <> strDocNm Then
    If UBound(Split(strDocNm, ".doc")) = 0 Then
      Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
      With wdDoc
        'If .HasVBProject = True Then
          '.SaveAs2 FileName:=.FullName & "m", FileFormat:=wdFormatXMLDocumentMacroEnabled, AddToRecentFiles:=False
        'Else
          .SaveAs2 FileName:=.FullName & "x", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        'End If
        .Close SaveChanges:=False
      End With
      Kill strFolder & "\" & strFile
    End If
  End If
  strFile = Dir()
Loop
Set wdDoc = Nothing
Application.ScreenUpdating = True
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

The commented-out lines, if un-commented, will save doc files containing macros in the required docm format. Of course, you'll then also have to modify the calling code to reference the correct extension to open the document...

Quote:

Originally Posted by Rampum15

Speaking of converting files, I've tried a couple of macros online, but they just change the extension so the files open in compatibility mode.

No, that is not what the macro does at all. The macro converts the files into actual Word templates, changing the extension is just part of that. You will notice, for example, that double-clicking on any of those templates will not open them but instead opens a new document with the same appearance but named Document1, Document2, etc., instead of the template's name. That is also why I said you would need to change your code from Documents.Open to Documents.Add, so the actual template doesn't get opened for editing.

Guessed · #7 09-13-2022, 04:59 PM

Paul, why do you think it is important for these source files to become templates? I presume this code is not contained in any of them since it is running before the source file is opened. I also presume that they are creating PDFs (via FinePrint) and not saving the source document in a Word format at all. I would have thought that making all the source files into separate templates makes it problematic to have a single 'attached template' that might contain code or standardised formatting across the suite of documents.

The improvements I would recommend for the code is:
1. Stop nagging with a MsgBox about Fineprint and just use Word's SaveAs to make a PDF (will need to ask what filename and location to save to).
2. Use the FileOpen dialog to allow the user to select the preferred source document and not have to type a part number - this solves the original question and avoids user typing errors. Rather than opening the file with their answer, use it with a Documents.Add method.

And are the search terms in any header/footers because that code is probably not finding those instances.

Rampum15 - is that what you want to achieve?

macropod · #8 09-14-2022, 05:57 AM

Quote:

Originally Posted by Guessed

Paul, why do you think it is important for these source files to become templates?

Because it's cleaner and, as I said in post #4:

Quote:

Originally Posted by macropod

That way, there's never a risk of the template file being overwritten, even if someone uses it to create a document without also using the macro.

Rampum15 · #9 09-14-2022, 01:15 PM

Thank you both for your time. I finally have the answer I was looking for.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cannot open files created in Word/show up as DOCX	Alcore	Office	4	10-24-2021 02:08 AM
View and edit docx files in Word 2003	orealius	Word	4	03-16-2019 08:11 AM
I cannot open .docx files even though I have MS Office 2016	syeh11	Word	1	09-14-2015 10:16 PM
How to open Docx files?	mond_bees	Word	12	08-29-2012 03:32 AM
Office 2007 will not open .docx files	Reg1987	Word	1	12-15-2010 11:55 AM