[Excel] VBA New word docx doesn't save on other computers

[KWJones]

Hello all!
This is my first post in here, so please forgive me if I am in the wrong area I am fairly new to VBA, so any answers are greatly appreciated and even more so on a novice level.
1. I have an excel workbook with multiple scripts (VBA), formula's, and SQL queries all interacting with each other.
2. The problem area is with the script that creates a word docx and saves it with a file name (from a cell) into a folder that is created with another script.
3. The script works perfectly on my machine at work (which we are all on the same updates and versions of office) and also on my personal laptop at home.
4. The script fails on the last line where it is saving the docx when another person tries to use it on their computer.
- I have tried the following to no avail:
a. Changing to late binding
b. Changing "saveas2" to "saveas" (in both statements).
c. Yes, the users are "enabling macros"
d. The "filename:=Environ("UserProfile") & "\desktop\Project Files\"" portion is working fine, as the script that creates the folder on their desktop is working.
e. We are all using Windows 7 enterprise (SP1), and MS Office (pro plus)2010.
f. Finally, this is the line of code it keeps identifying with a debug error:
.ActiveDocument.SaveAs2 filename:=Environ("UserProfile") & "\desktop\Project Files\" & Range("'File Names'!B10") & ".docx"

Here is the actual code:

Private Sub PROC1_APWS_WK_SHEET()
    'Late Binding
    
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim sel As Word.Selection
    Dim SaveName As String
    Dim path As String

    
    path = Environ("UserProfile") & "\desktop\Project Files\" & Range("'Inputs'!D27")
    


   
' MsgBox " Documentation worksheet is being created will be saved in " & Environ("UserProfile") & "\desktop\Project Files\"
    
    'This opens word
    Set wdApp = New Word.Application

    
    'Makes word visible
    With wdApp
        .Visible = True
        .Activate

        .Documents.Add
    
        With .Selection
            .ParagraphFormat.Alignment = 1
            .BoldRun
            .Font.Underline = wdUnderlineThick
            .Font.Size = 16
            .Font.Name = Calibri
            .TypeText "PERIODIC REVIEW DOCUMENTATION WORKSHEET"
            .BoldRun
            
            .TypeParagraph
            .Font.Size = 12
            .ParagraphFormat.Alignment = 0
            .TypeParagraph

  
        End With
'Copies cells from excel into word

       Range("'1APWS worksheet'!$A$1:$I$14").Copy
        
        .Selection.Paste
        
            
        .ActiveDocument.SaveAs2
        



        .ActiveDocument.SaveAs2 filename:=Environ("UserProfile") & "\desktop\Project Files\" & Range("'File Names'!B10") & ".docx"


'        .ActiveDocument.Close
'        .Quit

 
 End With

  
  Set wdApp = Nothing
  
End Sub

[RobDog888]

Welcome to the Forums.

It doesnt look like there is a code error per-se. Since you are in a multi user environment you did make sure each created folder on the users desktop creates correctly so its just figuring out what is up with the save.

Did you log on to their computer and run the program?

If save is failing, there are no filename conflicts?
The file doesnt already exist in the users folder creating an overright prompt?

[westconn1]

.ActiveDocument.SaveAs2




.ActiveDocument.SaveAs2 filename:=Environ("UserProfile") & "\desktop\Project Files\" & Range("'File Names'!B10") & ".docx"

does the first saveAs save the file somewhere? you can use debug.print .activedocument.fullname between the 2 saveaAs to find where

f. Finally, this is the line of code it keeps identifying with a debug error:

what error?

how and when are you creating the folder when required??

see if this variation can work for you

mypath = Environ("UserProfile") & "\desktop\Project Files\"

If Len(Dir(mypath, vbDirectory)) = 0 Then MkDir mypath '  create folder if not exist
        ActiveDocument.SaveAs mypath & Range("'File Names'!B10") & ".docx"

if you still get error you should check folder permissions of the newly created folder

[KWJones]

Hi Rob,
Thank you for the speedy reply.
I just tried logging on to one of the other computers and running the script from there (file was on the desktop).
It creates the folders just fine, but still not saving the file nor naming the file ( Range("'File Names'!B10") & ".docx").
I don't think there is a file name conflict. There are no other files in the folder prior to running the script, and it doesn't have a problem saving it on my machine.
I liked your idea for me to log into their machine... you had my hopes up since I hadn't tried that yet... but no joy

[KWJones]

Hi Westconn!
Thanks for the reply. I just did some experimenting with your code. Let me start with answering your questions first.
1. I wasn't aware, but the first saveas WAS saving my files in another directory (Libraries\Documents) and I wasn't aware of it. So, thank you for the cool command to debug.print , I had never heard of that. It threw me off a bit because I didn't know where the output was going, but finally figured out that I had to turn on the "Immediate" view window to see it.

2. Not sure if it wasn't showing up, but the line the was giving me the error was in the bottom of my code which read:
.ActiveDocument.SaveAs2 filename:=Environ("UserProfile") & "\desktop\Project Files\" & Range("'File Names'!B10") & ".docx"

3. As far as the folder goes, I use to have it in this script, but I moved it to it's own script so I could create a separate button in excel to execute the "create folder". I have 14 different file name this generates based on several variables, and each one needs it's own unique folder.

4. I tried your version, but commented out my path statement just below the Dim statements, and it created the folder (same as the other script I had written for the folders), but it still kept stopping at the line:
ActiveDocument.SaveAs mypath & Range("'File Names'!B10") & ".docx

5. Alllllll that and the guy who's machine I was using says "Hey why don't you try it as .doc instead of .docx? My thought was it won't hurt and if anything it will satisfy him that I tried.... GUESS WHAT! The (&^^%$%()_ WORKED!!!
Soooo... Why is it, using office 2010, that it won't work right using .docx? Is there something I need to change in my code so that it will save using that?

I do have another question... My script to create the folders, also creates subfolders. How can I get my doc to save into the newly created sub folder
Here is the script for creating my folders:


Sub Create_PROC1_Folder()
'Late Binding


Dim SaveName As String
Dim path As String


path = Environ("UserProfile") & "\desktop\"



'Creates Main folder on desktop
If Len(Dir(path & "Project Files", vbDirectory)) = 0 Then
MkDir path & "Project Files"
End If


' MkDir path & "Project Files"
path = Environ("UserProfile") & "\desktop\Project Files\"

If Len(Dir(path & Range("'Inputs'!D27"), vbDirectory)) = 0 Then
MkDir path & Range("'Inputs'!D27")

End If



End Sub

[RobDog888]

Ah different versions of Word on the systems. You can do one of a couple things for that.

1. Save the document as a .doc file instead of .docx to get everyone using the lowest compatible version of word.
2. Programmatically check what version of Word they have installed and then save the document as which ever version file format you find (.doc for 1997-2003 and .docx for 2007+ and newer)
3. If they will be sharing the documents with one another then you would be better off using the lowest common file format (.doc) which can be opened on Word 1997-2016.

To save the document in a subfolder is not much different vs what you are doing now. Just add the folder and subfolder to the file path in your save as line of code.

[westconn1]

you still did not answer what error you were getting

Soooo... Why is it, using office 2010, that it won't work right using .docx?

my test in word 2000 saved the file as .docx, even though it does not support .docx files, except by courtesy of the compatibility pack

you maybe need to specify the file format to saveAs, to match the .docx extension

the fact it saveAs when nothing is specified, may set the file format, though i would have though that version 2007 + would have saved as .docx

check the file format of the Normal (or other default) template, in case that has been carried over from some previous version

[KWJones]

[QUOTE=RobDog888;5182285][color=navy]Ah different versions of Word on the systems. You can do one of a couple things for that.

1. Save the document as a .doc file instead of .docx to get everyone using the lowest compatible version of word.
2. Programmatically check what version of Word they have installed and then save the document as which ever version file format you find (.doc for 1997

Rob,
That is what is strange about it. We all have office 2010, so .docx should be compatible. I did double check, and we are all on the same version of office.

As for saving the document into a subfolder, the subfolder will have a dynamic name depending on what is generated in a particular cell based on multiple IF statements. So with this as my statement to create the folder name:

If Len(Dir(path & Range("'Inputs'!D27"), vbDirectory)) = 0 Then
MkDir path & Range("'Inputs'!D27")

I am not sure how to specify that as a directory path to savedto. Any suggestions?

Thanks again! I REALLY appreciate your help!!!
Keith

[KWJones]

Westconn,
My apologies, remember I am new to VBA, so I didn't really understand what you were asking, but I do now.

you still did not answer what error you were getting

The error I was getting was "Run-time error '6294': Incompatible file type and file extension"

my test in word 2000 saved the file as .docx, even though it does not support .docx files, except by courtesy of the compatibility pack
you maybe need to specify the file format to saveAs, to match the .docx extension

Honestly, I am not sure how to specify the file format except by changing the extension name at the end of my line of code. Is there a specific command I can use to do that? (please help?)

check the file format of the Normal (or other default) template, in case that has been carried over from some previous version

I know this sounds silly, but that sounds like greek to me I really don't understand what that means. Can you break it down in laymen's terms for me? I am not sure what " Normal (or other default) template" means.

[KWJones]

One quick question for posting. I now see how to use the right format for using quotes and code, but when I tried to go back and edit a previous response, I wasn't able to save my edit. Is there a trick to save what you edited?
Thanks again!
Keith

[jdc2000]

Word default Normal template info:

https://support.office.com/en-us/art...7-ccb5166f98ea

https://www.google.com/webhp?hl=en&i...=1498152541672

[KWJones]

Thank you JDC!
So if I changed the Normal.dotm template, wouldn't that only affect my computer? Seems like we would have to do that on everyone's computer for the default formatting to be the same?
Thank you!
Keith

[RobDog888]

You can change the users default file save extension type from the File > Options > Save and select the .doc file extension.

[jdc2000]

RobDog888 has the right idea. You would not want to change the user's default file type for saving - they could get very annoyed at you. You should be able to select the file type to Save As.

Links (for VBA, there should be VB equivalents):

https://msdn.microsoft.com/VBA/Word-...s2-method-word

https://www.google.com/webhp?hl=en&i...=1498152935797

[KWJones]

Thanks guys,
I learned something new and cool today! But... not sure how this helps with why I am not able to save as .docx to other computers who are running MSO 2010?

Rob/Anyone,
As for saving the document into a subfolder, the subfolder will have a dynamic name depending on what is generated in a particular cell based on multiple IF statements. So with this is my statement to create the folder name(s):

 
Sub Create_PROC1_Folder()
    'Late Binding
    
   
    Dim SaveName As String
    Dim path As String

    
    path = Environ("UserProfile") & "\desktop\"
    


'Creates Main folder on desktop
 If Len(Dir(path & "Project Files", vbDirectory)) = 0 Then
   MkDir path & "Project Files"
End If
 
 
' MkDir path & "Project Files"
    path = Environ("UserProfile") & "\desktop\Project Files\"
 
If Len(Dir(path & Range("'Inputs'!D27"), vbDirectory)) = 0 Then
  MkDir path & Range("'Inputs'!D27")
  
End If
 

  
End Sub

I am not sure how to specify a directory path that includes " Range("'Inputs'!D27") " to save new documents in. Any suggestions?

E.G.

.ActiveDocument.SaveAs2 filename:=Environ("UserProfile") & "\desktop\Project Files\" & Range("'File Names'!B10")\ & ".doc"

Which doesn't work, but my best guess at how it should have been.

This is what the actual directory path would look like once it is created. Except "K56D_VOR_A_RWY 1" is the value in cell File Names'!B10 and will always be a dynamic name.

C:\Users\KeithJones\Desktop\Project Files\K56D_VOR_A_RWY 1

[westconn1]

You would not want to change the user's default file type for saving - they could get very annoyed at you

probably they have no idea what file type they are using, for versions from 2007 on should be using the later file formats anyway

The error I was getting was "Run-time error '6294': Incompatible file type and file extension"

if you had known to post the error earlier, it specifies the problem, much better than most error codes

I wasn't able to save my edit. Is there a trick to save what you edited?

should just have to click the save button

I am not sure how to specify the file format

to specify the file format to save to

ActiveDocument.SaveAs mypath & "xxfile" & ".docx", wdFormatXMLDocument

wdFormatXMLDocument = 12

list of formats

Name Value Description
wdFormatDocument 0 Microsoft Office Word 97 - 2003 binary file format.
wdFormatDOSText 4 Microsoft DOS text format.
wdFormatDOSTextLineBreaks 5 Microsoft DOS text with line breaks preserved.
wdFormatEncodedText 7 Encoded text format.
wdFormatFilteredHTML 10 Filtered HTML format.
wdFormatFlatXML 19 Open XML file format saved as a single XML file.
wdFormatFlatXML 20 Open XML file format with macros enabled saved as a single XML file.
wdFormatFlatXMLTemplate 21 Open XML template format saved as a XML single file.
wdFormatFlatXMLTemplateMacroEnabled 22 Open XML template format with macros enabled saved as a single XML file.
wdFormatOpenDocumentText 23 OpenDocument Text format.
wdFormatHTML 8 Standard HTML format.
wdFormatRTF 6 Rich text format (RTF).
wdFormatStrictOpenXMLDocument 24 Strict Open XML document format.
wdFormatTemplate 1 Word template format.
wdFormatText 2 Microsoft Windows text format.
wdFormatTextLineBreaks 3 Windows text format with line breaks preserved.
wdFormatUnicodeText 7 Unicode text format.
wdFormatWebArchive 9 Web archive format.
wdFormatXML 11 Extensible Markup Language (XML) format.
wdFormatDocument97 0 Microsoft Word 97 document format.
wdFormatDocumentDefault 16 Word default document file format. For Word, this is the DOCX format.
wdFormatPDF 17 PDF format.
wdFormatTemplate97 1 Word 97 template format.
wdFormatXMLDocument 12 XML document format.
wdFormatXMLDocumentMacroEnabled 13 XML document format with macros enabled.
wdFormatXMLTemplate 14 XML template format.
wdFormatXMLTemplateMacroEnabled 15 XML template format with macros enabled.
wdFormatXPS 18 XPS format.

[RobDog888]

I see you have SaveAs2 twice, one with no arguments and one with. The first one will save the document with a default file name in the Documents default file location. Are the documents saving into this location on the users with the errors? The second one will save it as the specified file name and location. Just wondered why the two saves?

Can you check the file locations on the ones that fail to see if they are valid file path names, not the locations but the actual file paths as could have an invalid path character or such. Sometimes error messages are not always exact