whoops, there is some leftovers in there. here is the better version:

Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\Time Cards"

With ActiveSheet
USRNM = .Range("EmployeeName").Value
SunDT = Format(.Range("SundayDate").Value, "dd-mmm-yy")

If Trim(USRNM) = "" Or Trim(SunDT) = "" Then
MsgBox "Please add Employee Name and Sunday's Date" & vbLf &
"File not saved!"
Else
If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
End If

ActiveWorkbook.SendMail ", strFName

Application.Quit
End With

End Sub


"Ron de Bruin" wrote:

Try this

Sub test2()
Dim wsh As Object
Dim fs As Object
Dim DocPath As String
Dim DirString As String

Set wsh = CreateObject("WScript.Shell")
Set fs = CreateObject("Scripting.FileSystemObject")
DocPath = wsh.SpecialFolders.Item("mydocuments")
DirString = DocPath & "\yourfolder"

If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello again

almost working, but i need to know how to cause it to create a folder if it
isnt there.

i would do the whole if thing but isnt there a way to tell it to create it
if it isnt there?

this is the line that i have worked out:

.Parent.SaveAs myDocumentsPath & "\Time Cards\" & strFName

but the folder "Time Cards" doesnt exist and so it chokes and tells me that
the folder doesnt exist. is there a SaveAs modifier that i can use to tell it
to just make the directory? eg.

.Parent.SaveAs myDocumentsPath & "\Time Cards\" & strFName /mkdir

i dont want to create the directory if it is there, but if there isnt then
it would be good to do it.

any thoughts?

"Ron de Bruin" wrote:

Hi Dawn

See
http://www.rondebruin.nl/folder.htm#SpecialFolders



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DawnTreader" wrote in message ...
Hello Ron

is it possible to use the same type of application method to get to the my
documents folder of any one?

eg. Application.TemplatesPath would change to application.usernamepath? or
something like that.

Thanks

"Ron de Bruin" wrote:

Hi Liz

I use a install workbook to install a sheet template

Part of the code :

ThisWorkbook.Sheets("RDBMail").Copy
Set wb = ActiveWorkbook
With wb
wb.SaveAs Application.TemplatesPath & "RDBMail.xlt", _
FileFormat:=xlTemplate
.Close False
End With

Maybe you can use it
http://www.rondebruin.nl/mail/templates.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim Rech" wrote in message ...
Yes, I was referring to the user's actual name. The better setup programs
can determine things like this and incorporate them. If yours can't I don't
know what you can do.

--
Jim Rech
Excel MVP
"Liz" wrote in message
...
| If you mean use the actual username, can't do that, 700+ users will be
extracting this. If you mean actually type <username, doesn't work. My
predecessor had it going into the user's own folder - can't figure out how
he did it.
|
| "Jim Rech" wrote:
|
| I believe the correct path is:
|
| C:\Documents and Settings\<username\Application
Data\Microsoft\Templates
|
| --
| Jim Rech
| Excel MVP
| "Liz" wrote in message
| ...
| |I am trying to create a self extracting exe to install excel templates
into
| a current users templates folder. I've tried these paths and it won't
work:
| |
| | C:\Documents and Settings\Default User\Application
| Data\Microsoft\Templates
| |
| | C:\Documents and Settings\All Users\Application
Data\Microsoft\Templates
| |
| | Thanks
|
|
|