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
|
|
|

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
|
|
|

One way:

Option Explicit
Sub testme()
Dim myDocumentsPath As String

Dim wsh As Object

Set wsh = CreateObject("WScript.Shell")
myDocumentsPath = wsh.SpecialFolders.Item("mydocuments")

MsgBox myDocumentsPath

End Sub



DawnTreader wrote:

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
|
|
|






--

Dave Peterson

Hello again Ron and thanks for the suggestion Dave

ok. as i was coming back to look at this i realised i hadnt really asked the
right question.

what i am trying to do is make it so that anyone who uses my workbook can
save it and it will end up in a folder in thier my documents. with the code
you have given i should be able to work it out. but, we have 2 types of users
in the business.

1. roaming profiles on desktop machines. this is the one that i am having
trouble with. i just need to make sure that a workbook goes exactly where i
want it.

2. technicians with laptops. they have a "profile" as well, but thier
profile has the my documents on c drive, that is the easy one.

i need my solution to be able to do both with one swing. i dont need to open
explorer i just need to save the file in the right place, regardless if the
user has the folder i end up specifying. so here is the current code:

Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String

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
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs "C:\Documents and Settings\" &
Environ("Username") & "\My Documents\Time Cards\" & strFName
End If

ActiveWorkbook.SendMail ", strFName & "this is a test"

Application.Quit

End With

End Sub

what would be the modification to get it to save to a folder called "Time
Cards" in the my documents folder of any user, regardless of whether they
have a "Time Cards" folder or not?

Thanks for all help! :)

"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
|
|
|

dang forgot something

the line:

..Parent.SaveAs "C:\Documents and Settings\" & Environ("Username") & "\My
Documents\Time Cards\" & strFName

should not have the C:\ stuff in it, i would like to be able to build the
path like

..Parent.SaveAs rootfolderSTR & Environ("Username") & "\My Documents\Time
Cards\" & strFName

so to speak...
"DawnTreader" wrote:

Hello again Ron and thanks for the suggestion Dave

ok. as i was coming back to look at this i realised i hadnt really asked the
right question.

what i am trying to do is make it so that anyone who uses my workbook can
save it and it will end up in a folder in thier my documents. with the code
you have given i should be able to work it out. but, we have 2 types of users
in the business.

1. roaming profiles on desktop machines. this is the one that i am having
trouble with. i just need to make sure that a workbook goes exactly where i
want it.

2. technicians with laptops. they have a "profile" as well, but thier
profile has the my documents on c drive, that is the easy one.

i need my solution to be able to do both with one swing. i dont need to open
explorer i just need to save the file in the right place, regardless if the
user has the folder i end up specifying. so here is the current code:

Sub SaveWorkbook()

Dim USRNM As String
Dim SunDT As String
Dim strFName As String

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
strFName = "TimeCard " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs "C:\Documents and Settings\" &
Environ("Username") & "\My Documents\Time Cards\" & strFName
End If

ActiveWorkbook.SendMail ", strFName & "this is a test"

Application.Quit

End With

End Sub

what would be the modification to get it to save to a folder called "Time
Cards" in the my documents folder of any user, regardless of whether they
have a "Time Cards" folder or not?

Thanks for all help! :)

"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
|
|
|

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
|
|
|

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
|
|
|

On error resume next
mkdir myDocumentsPath & "\Time Cards"
on error goto 0

If the folder already exists, the error will be ignored. If it doesn't exist,
then it'll be created.

DawnTreader wrote:

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
|
|
|







--

Dave Peterson

Hello Ron

thanks, that helped.

here is the final code:

Dim USRNM As String
Dim SunDT As String
Dim strFName As String
Dim myDocumentsPath 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

thanks again! :)


"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
|
|
|

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
|
|
|