Question AND my 1st positive contribution
 

OK, although I originally wrote the following for VBA/Word it also works
in VBA/Excel...

GOAL:
I want to save a file based on specific fields/cells automatically with
the neumonic "LastNameFirstName.doc(or.xls)...

SOLUTION:the following code works...

Dim strMessage As String, strTitle As String
Dim Response As Long
Dim X As String
Dim filename As String
'Dim ObjWord As Word.Application
'Set ObjWord = New Word.Application
'MsgBox ActiveDocument.Bookmarks.Count
ActiveDocument.Bookmarks("LastName").Select
X = Selection
ActiveDocument.Bookmarks("FirstName").Select
Y = Selection
strTitle = "Bookmark Value 1"
strMessage = "" & X & ""
'Response = MsgBox(strMessage, vbOKOnly + vbCritical, strTitle)
strTitle = "Bookmark Value 2"
strMessage = "" & Y & ""
'Response = MsgBox(strMessage, vbOKOnly + vbCritical, strTitle)
filename = X + Y + ".doc"
strTitle = "File Name to Save As"
strMessage = "" & filename & ""
'Response = MsgBox(strMessage, vbOKOnly + vbCritical, strTitle)
ChangeFileOpenDirectory "C:\BCGprograms\TestFiles2\"
ActiveDocument.SaveAs filename:="" & filename & "",
FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True,
WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False,
SaveAsAOCELetter:= _
False

(note that I commented out the MsgBoxs'...they were there to help me
along...)

My ultimate goal is to have a folder automatically created instead of
being put in 'TestFiles2'...

I've tried the following:

MkDir "C:\BCGprograms\Clients\"" & filename & ""\"

but it creates a folder called 'filename'...

I thought using the same logic that created the new document name
wouldnalso work in creating the new folder...wrongo...thoughts or
suggestions would be appreciated.

Thanks,
Ron


---
Message posted from http://www.ExcelForum.com/

Question AND my 1st positive contribution
 

if you use the following:

MkDir "C:\BCGprograms\Clients\" & filename & "\"

the new folder is created...hope this helps somebody.

Ro

--
Message posted from http://www.ExcelForum.com