[zeo]

hi there,

Can anyone please give me a hand with this macro? (BTW I have absolutly no idea about macros, or VB scripting, I found this on the web and tried to modify it for my use).

At the moment I have got the macro working to automatically assign a running number to the file name of each save document e.g. 0001.doc, 0002.doc. But the problem I am having is that all of the saved files are ending up in my documents folder?!! Is there anyway I can define what location/folder/drive? the new files save to?

Sub OnFileNew()
' Automatically creates a new document in a sequentially-numbered order
' based upon the stored sequence number in MySeq text file

' Dimension the variables
Dim strMyFileName As String

' Open MySeq file and load the sequence number
MySeq = System.PrivateProfileString("C:\Documents and Settings\Luqman & Inesa\Application Data\Microsoft\Word\MySeq.txt", _
"", "MySeq")

If MySeq = "" Then
MySeq = 1
Else
MySeq = MySeq + 1
End If

' Update the sequence number in MySeq
System.PrivateProfileString("C:\Documents and Settings\Luqman & Inesa\Application Data\Microsoft\Word\MySeq.txt", "", _
"MySeq") = MySeq

' Load the filename string with leading zeroes
strMyFileName$ = Format(MySeq, "000#")



' Set the filename to the new sequence number
ActiveDocument.SaveAs FileName:=strMyFileName

End Sub

------------------

Also is it possible to setup a macro that with automatically save the file name of a mailmerge (word) document to the first line that is on the document.

e.g. for a document with this info

-----------
David Smith 24-05-06

42 Clarkson avenue
London
N14 8BP
-----------

the file name of the mailmerge document will save as David Smith 24-05-06.doc (with a single click) to a specified folder (if possible)


Thanks in advanced.

[zeo]

bump

[bomb #21]

I guess you got it here:

http://pubs.logicalexpressions.com/p...icle.asp?ID=32

The one here:

http://word.mvps.org/FAQs/MacrosVBA/...edFilename.htm

seems much more straightforward (note the PathAndFileName = "C:\Data\temp" line)

[zeo]

ah thanks.

I dont suppose you could tell me how I could get the macro to also paste the strMyFileName onto the document before it savess it?

[bomb #21]

Advising on tweaking Word macros is so not my area -- cristobal or Tony J could probably help you. Nevertheless ...

... just by tinkering I found that:

Selection.HomeKey Unit:=wdStory

takes you to the top of the doc (Word 2K), and:

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"FILENAME ", PreserveFormatting:=True

inserts a filename field at the insertion point. So if the macro is "global" that might work after the save (or even before, if the filename field is smart enough to auto-update as & when).

HTH,
bomb

[zeo]

bomb #21 - thanks for your help! I really appreciate it.

I wasnt able to use the macro from the mvps site, as it kept coming up with a syntax error for some reason. But your suggestion for tweaking the macro code really helped with updating the document with the current running number.

The only thing that I'm unable to do so far is set the location for where the new documents should be saved. I've tried using the location code from the other macro but I've so far been unable to adapt it.

Sub NumberSeq()
' Automatically creates a new document in a sequentially-numbered order
' based upon the stored sequence number in MySeq text file

' Dimension the variables
Dim strMyFileName As String


' Open MySeq file and load the sequence number
MySeq = System.PrivateProfileString("C:\Documents and Settings\Luqman & Inesa\Application Data\Microsoft\Word\MySeq.txt", _
     "", "MySeq")

If MySeq = "" Then
     MySeq = 1
Else
     MySeq = MySeq + 1
End If

' Update the sequence number in MySeq
System.PrivateProfileString("C:\Documents and Settings\Luqman & Inesa\Application Data\Microsoft\Word\MySeq.txt", "", _
     "MySeq") = MySeq

' Load the filename string with leading zeroes
strMyFileName$ = Format(MySeq, "000#")


' Set the filename to the new sequence number
ActiveDocument.SaveAs FileName:=strMyFileName


' This inserts strMyFileName at the top of the document
Selection.HomeKey Unit:=wdStory

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"FILENAME ", PreserveFormatting:=True

' Saves the document
ActiveDocument.Save

End Sub

[cristobal03]

That's the craziest thing I ever heard of. You don't need to store a list of numbers in a separate file just to increment the filenames unless you're saving the files in different locations, or deleting the older ones periodically, or something like that. Is this the case?

Also, if your documents are based on a template you could add that FILENAME field to your template instead of putting it in programmatically. That'd give you a bit more control over its placement (the document's footer is a pretty common place for file information).

chris.

[cristobal03]

[bump]

Sorry, I misread your question. You're trying to set the destination folder, yes?

chris.

[cristobal03]

[bump 2]

I'll assume that's the case.

You need to change the line

ActiveDocument.Save

to

ActiveDocument.SaveAs "C:\tmp" & strMyFileName$ & ".doc"

where C:\tmp is the path to the destination directory.

HTH

chris.

[edit]
The SaveAs method has no option for prompting overwrites. Therefore you might want to check to make sure you aren't overwriting any files. This shouldn't be the case, using that PrivateProfileString, but then again the code you're using isn't at all what I would use if I were keeping all the files in a single directory.
[/edit]

[edit 2]
Added the .doc extension, though I don't think it's necessary.
[/edit 2]

[bomb #21]

Originally Posted by cristobal03

You don't need to store a list of numbers in a separate file just to increment the filenames

Exactly what I thought, & why I suggested the far less complicated one.

All I can say is that:

Sub SaveIncrementedFilename()

Dim PathAndFileName As String, n As Long

PathAndFileName = "C:\Documents and Settings\Andy\My Documents\test"
If Dir(PathAndFileName & ".doc") = "" Then
ActiveDocument.SaveAs (PathAndFileName & ".doc")
Else
n = 1
Do While Dir(PathAndFileName & n & ".doc") <> ""
n = n + 1
Loop
ActiveDocument.SaveAs PathAndFileName & n & ".doc"
End If

Selection.HomeKey Unit:=wdStory

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"FILENAME ", PreserveFormatting:=True

ActiveDocument.Save

End Sub

works fine for me.

You'll figure it out Chris.

[cristobal03]

Yeah, it really threw me because PrivateProfileString is used to make registry changes or change system settings. It can be used to store things in text files but. Like I said, it would only be necessary if you had to increment files that weren't kept in the same place. Or, say, the previous file had been removed but you still needed to retain the next enumeration.

chris.

[zeo]

ahhh it works great, thank you

The actual template files will be kept in a seperate folder to that of the location of where I want the increment files to be stored. (well separate mapped network drive to be exact).

The reason for this macro code is that I've got a database program that store patient information. The program can generate order forms, but only as a word mailmerge file.

I thought that if I had a macro code like this, it would make things easier for me to
- keep a copy of the generated ordersheet on my system that I can always refer back to.
- also print out a copy of the order sheet with the increment number to use as an order number to refer back to this exact order. I'm planning on setting the macro to also print a copy of the document to a remote printer (located in my lab).

Anyway everything seems to be working better then I could imagine. So I would like to thank you guys again for your help.

Much appreciated.

Regards

Zeo(UK)

p.s. is there away that I could get the macro to close the document after it saves the copy? I've tried ActiveDocument.Close - but i dont think thats right (it was just a guess)

[cristobal03]

ActiveDocument.Close will close the document not the Application. I believe Application.Quit might be what you mean. Just add those two lines, one after another, at the end of your routine.

chris.

[zeo]

I've got another problem,

Is there any macro I can use that will automatically run after a document is mailmerged that would enable "Form protect" feature?

The problem that I am having is that I designed a template for an order sheet on MS Word. My datebase program fills in most of the usual information about the client such as Name, address, prescription etc.

For the variable part of the order I inserted some drop down forms in the document that I can select what type of frame, lenses etc that the client wants ordered.

I noticed that the drop down menus dont work (unable to select) unless I enable protection for the forms. The problem that I am having is that when I enabled protection on the main template. I'm unable to mailmerge from my database as it comes up with an error saying:

-2147216899 The OpenDataSource2000 method or property is not available because the object refers to a protected area of the document. in frmWrite.MergeDocument

So I was wondering if it was possible to insert a macro that would run form protection automatically after the document opened and merged.

Or is there maybe another method I could get around this?

[zeo]

bump