Prompt in Word 2003 for Filename then save to two places 2

[colinmitton]

I have created a word form and have then created a macro to save the document twice. Once is the form in words format the second is the form data to be upload to a DB.

I have the code here :

Sub formsaving()
  ChangeFileOpenDirectory "\\server1\form\"
    ActiveDocument.SaveAs FileName:="Data Collection Form.doc", FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    With ActiveDocument
        .SaveFormsData = True
    End With
    ChangeFileOpenDirectory "\\server1\formdata\"
    Application.DefaultSaveFormat = ""
    ActiveDocument.SaveAs FileName:="Data Collection Form.txt", FileFormat:= _
        wdFormatText, LockComments:=False, Password:="", AddToRecentFiles:=True, _
        WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
         SaveNativePictureFormat:=False, SaveFormsData:=True, SaveAsAOCELetter:= _
        False, Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False _
        , LineEnding:=wdCRLF
    With ActiveDocument
        .SaveFormsData = False
    End With
    Application.DefaultSaveFormat = ""
End Sub

What I need to do now is make the 'save' name currently "Data Collection Form" a prompt at the beginning so when the person filling out the form presses the button to run this macro puts the relevent filename in then the macro does the rest.

I've done simular in Excel but have failed on this one!

Thanks in advance

 

[SkipVought]

Hi,

Use a variable for the filename.

sName = InputBox("enter name")
'...
ActiveDocument.SaveAs FileName:=sName & ".txt",

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[colinmitton]

Thanks thats worked a treat, one question though! if someone clicks 'Cancel' on the Window how do I get it to close nicely instead of the debug message. My code is below!

Sub formsaving()
'
' formsaving Macro
' Macro recorded 02/09/2008 by Colin Mitton
'
    
    Dim sName As String
    sName = InputBox("enter name")
    With ActiveDocument
    ChangeFileOpenDirectory "\\server1\Client Data Form\"
    End With
    ActiveDocument.SaveAs FileName:=sName & ".doc", FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    With ActiveDocument
        .SaveFormsData = True
    End With
    ChangeFileOpenDirectory "\\server1\Client Data Form\db\"
    Application.DefaultSaveFormat = ""
    ActiveDocument.SaveAs FileName:=sName & ".txt", FileFormat:= _
        wdFormatText, LockComments:=False, Password:="", AddToRecentFiles:=True, _
        WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
         SaveNativePictureFormat:=False, SaveFormsData:=True, SaveAsAOCELetter:= _
        False, Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False _
        , LineEnding:=wdCRLF
    With ActiveDocument
        .SaveFormsData = False
    End With
    Application.DefaultSaveFormat = ""
End Sub

Thanks once again Skip.

 

[SkipVought]

    Dim sName 
    sName = InputBox("enter name", vbcancel)
    if sname <> vbcancel then
       ' your saveas code here

    end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[colinmitton]

I'm having a little trouble putting the vbcancel option in? Is it possible to give a little more detail. I have added in to the macro as suggested but when the cancel button is still continues to try and do the save in two places part. My concern was all the 'with' statements in the code if that was throwing off the end if statement which I put before the end sub.

I'm sorry I dont have the code here (I was playing with it at home last night) but I will post it later to show what I'm trying.

Thanks... One day I will learn how to program!

 

[PHV]

Dim sName
sName = InputBox("enter name")
If sName <> "" Then
' your saveas code here
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[colinmitton]

Thanks, I'll give that a try later.

Colin

 

[colinmitton]

Thanks for that the final code is :

Sub formsaving()
'
' formsaving Macro
' Macro recorded 02/09/2008 by Colin Mitton
'
    Dim sName
    sName = InputBox("Please Enter Filename")
    If sName <> "" Then
    With ActiveDocument
    ChangeFileOpenDirectory "\\dfaserver2\Client Data Form\"
    End With
    ActiveDocument.SaveAs FileName:=sName & ".doc", FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    With ActiveDocument
        .SaveFormsData = True
    End With
    ChangeFileOpenDirectory "\\dfaserver2\Client Data Form\Act\"
    Application.DefaultSaveFormat = ""
    ActiveDocument.SaveAs FileName:=sName & ".txt", FileFormat:= _
        wdFormatText, LockComments:=False, Password:="", AddToRecentFiles:=True, _
        WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
         SaveNativePictureFormat:=False, SaveFormsData:=True, SaveAsAOCELetter:= _
        False, Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False _
        , LineEnding:=wdCRLF
    With ActiveDocument
        .SaveFormsData = False
    End With
    Application.DefaultSaveFormat = ""
    End If
End Sub

Thanks for your help
Colin