save a file using VBA

[oggstar1]

I would like to automatically save a file to a particular directory. I am not sure how exactly to code the macro but it should look something like what I have typed below..
'
The file directory will be taken from the current open record in the access database

The file name will be (Today date) letter.doc
i.e. ; 2003-12-24 letter.doc

' save Macro
'
FileDirectory ""
Access database path= C:\database\CS1.mdb
Filepath = Forms!Main![path]

ActiveDocument.SaveAs FileName:=" (todaysDate, "yyyy-mm-dd&quot letter.doc",

FileFormat:=wdFormatDocument, _ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _ :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False
End Sub

Any help would be appreciated. Thanks.

 

[SBendBuckeye]

Are you already using Word via Automation? If not, try something like this. It is a way to control Word from Access. How are you calling your Macro, from a form or some other way?

1. Open Word as normal and turn on the Macro recorder by clicking Tools, Macro, Record New Macro.

2. Open a new document

3. Save it as you would for your post above

4. Stop the macro recorder

5. Click Tools, Macro, Macros and click on the macro you just recorded above and then click edit.

6. Copy the generated code into an Access Module

7. Add this code to create Word object for Access to control

Dim objWord As Object 'Avoids setting reference to Word
    Set objWord = CreateObject("Word.Application")
    objWord.Documents.Add DocumentType:=wdNewBlankDocument
    obj.ActiveDocument.Sentences(1).Text = _
        "OK, did this work? I guess we will find out!"
    objWord.ActiveDocument.SaveAs _
        FileName:="YourFileNameAndPath", _
        FileFormat:=wdFormatDocument, _
        LockComments:=False, _
        Password:="", _  
        AddToRecentFiles:=True, WritePassword:="", _
        ReadOnlyRecommended:=False, _
        EmbedTrueTypeFonts:=False, _
        SaveNativePictureFormat:=False, _
        SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    Set objWord = Nothing
End Sub

I hope this helps get you started. Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[BrainB]

The basic code is :-

'----------------------------------------------------
Sub test()
Dim MyFilename As String
MyFilename = Format(Now, "dd-mm-yy&quot & " letter.doc"
ActiveDocument.SaveAs FileName:=MyFilename
End Sub
'-----------------------------------------------------


Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[oggstar1]

Thanks for the replies I am making progress. Although I do not think I explained the problem very well so I have added more information including all the code which is run from an access button on my form.

The following code works well, however I now need to change the following line
ChangeFileOpenDirectory "C:\Andrewsfolder\". The file path varies depending on the record in my database, and thus I need to change "C:\Andrewsfolder\".
to include the directory path which is located in a field (named [path] or = Forms!Main![path]) in the current record open in my access database, when I click on my button named Command 35.

The code is as follows

Private Sub Command35_Click()
Dim WordObj As Word.Application
Set WordObj = CreateObject("Word.Application&quot
WordObj.Documents.Open ("\\Linux\temp\Fax&quot
WordObj.Visible = True
With WordObj
.Run "mergedoc"

ChangeFileOpenDirectory "C:\Andrewsfolder\"
ActiveDocument.SaveAs FileName:=Format(Now, "yyyy-mm-dd&quot & "Doc9.doc", FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False
End With
End Sub

Hope it makes sense,

 

[MakeItSo]

So actually this would suffice:
ActiveDocument.SaveAs FileName:=Me!path & "\" & Format(Now, "yyyy-mm-dd&quot & "Doc9.doc",

Correct?

Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[SBendBuckeye]

Hello oggstar1,

You are very, very close. If you add the following, I think it will do what you want it to do:

1. On your form, if you do not already have a control bound to the path field in your form recordsource, add a hidden textbox bound to path (for our purposes, let's say it is named txtPath)

2. Add the following to your Command35_Click code - the Replace$ just ensures you always have a trailing backslash \ on your directory name

Dim strPath As String

strPath = Replace$(Me.txtMyPath.Value & "\", "\\", "\&quot

3. Make the following additional changes in Command35_Click

a. Remove ChangeFileOpenDirectory "C:\Andrewsfolder\"

b. Change FileName:=Format to
FileName:=strPath & Format

Good Luck!





Have a great day!

j2consulting@yahoo.com

 

[oggstar1]

Thanks for the replies

SBendBuckeye

I have made the changes you suggusted , and there seems to be no problems with the code when i run the compiler.

The macro works fine, until it is time to save the document in the specified directory.

I receive the following error message. The remote server machine does not exist or is unavailable.

Any ideas as to why this occurs

I have changed the field reference for the file path from Me.txtMyPath to Forms!Main!txtPath. As the command button is not on the same form as the file path feild [txtpath].

Thanks

________________________________________________________
Private Sub Command95_Click()

Dim stDocName As String
stDocName = "goto.signature"
DoCmd.RunMacro stDocName

On Error GoTo Err_Command95_Click
Dim WordObj As Word.Application
Set WordObj = CreateObject("Word.Application&quot
WordObj.Documents.Open ("\\Linux\Planning\proforma.doc&quot
WordObj.Visible = True
With WordObj
.ActiveDocument.Bookmarks("Photo&quot.Select
.Selection.Paste
.ActiveDocument.MailMerge.Execute


End With
With WordObj
Set WordDoc = .Documents.Open("\\Linux\Planning\proforma.doc&quot
End With
With WordDoc
.Close SaveChanges:=False
End With

With WordObj
Dim strPath As String
strPath = Replace$(Forms!Main!txtPath.Value & "\", "\\", "\&quot


ActiveDocument.SaveAs Filename:=strPath & Format(Now, "yyyy-mm-dd&quot & "Doc9.doc", FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False
End With
Exit_Command95_Click:
Exit Sub

Err_Command95_Click:
MsgBox Err.Description

End Sub
________________________________________________________

Makeitso

The same error message is returned with this code

________________________________________________________

ActiveDocument.SaveAs Filename:=Forms!Main!txtPath & "\" & Format(Now, "yyyy-mm-dd&quot & "Doc9.doc", FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False

_________________________________________________________

 

[SBendBuckeye]

Hello Oggstar1,

Sorry, I didn't know you were using UNC pathing. In that case, my Replace$ statement would replace the leading \\ with one backslash. Try this instead:

strPath = Replace$(Me.txtMyPath.Value & "\", "\\", "\", 2)

The 2 at the end tells it to start the replace operation beginning with character 2. Hopefully, this will take care of it. If not, kick your code into single step mode and check to ensure all variables contain what you are expecting them to hold. Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[oggstar1]

Thanks SendBuckeye

I have tried this, and I receive a error mesasge in relation to the path. This is not a valid file name, check the path to make sure it is typed correctly.

If I exlcude this line

strPath = Replace$(Me.txtMyPath.Value & "\", "\\", "\", 2)

or in my case
strPath = Replace$(Forms!Main!temp.Value & "\", "\\", "\", 2). I changed my feild name which has the file path to temp

the macro works fine, thus this appears to be the problem line. I have tried everything i can think of.

Regards

 

[SBendBuckeye]

If you single step it, what is the value of strTemp? Do you need to trim your field name or something silly like that we haven't thought of? What are you hard coding in test to replace the strTemp value? There has to be something we're missing. Good Luck!

Have a great day!

j2consulting@yahoo.com