Recording a 'Save As...'

[camlad]

Recording a 'Save As...' I get this:



ActiveWorkbook.SaveAs Filename:= _

"C:\Documents and Settings\FH\Desktop\ Diary.xls", FileFormat:= _

xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _

, CreateBackup:=False



Two questions:



1

The file will be saved as a Template and used on other computers. How can
the code be changed so the file will be saved to the desktop of any
computer.



2

Can the macros be omitted in the saved file? If so what is the code?



Thanks



Camlad

 

[Barb Reinhardt]

Dim myFilePath as string
myFilePath = "C:\Documents and Settings\"
myFilePath = myfilepath & Environ("USERNAME")
myFilePath = myfilepath & "\Desktop\"

If you want to save it as a template without macros, I'd save it as a .xltx
file and the fileformat for that is 54. If you want to save as a
template with macros, save as a .xltm file, fileformat 53

HTH,
Barb Reinhardt

 

[camlad]

Many thanks Bard - it looks just right

Camlad

Dim myFilePath as string
myFilePath = "C:\Documents and Settings\"
myFilePath = myfilepath & Environ("USERNAME")
myFilePath = myfilepath & "\Desktop\"

If you want to save it as a template without macros, I'd save it as a
.xltx
file and the fileformat for that is 54. If you want to save as a
template with macros, save as a .xltm file, fileformat 53

HTH,
Barb Reinhardt

 

[Tim Zych]

Unfortunately using concatenation to build a directory location may not
always work. Years ago I used concatenation to build a directory location. I
don't remember what it was, maybe it was the desktop and maybe not. But then
for one user it did not work. Ever since then, where possible, I prefer to
let Windows/Excel/VBA tell me where certain directories are. Your example
will work for most people, but one day, for one user's configuration, for
some reason, it may not.

Here's another way to do it.

Public Const CSIDL_DESKTOP As Long = &H0
Public Const MAX_PATH = 260

Declare Function SHGetFolderPath Lib "shfolder.dll" _
Alias "SHGetFolderPathA" _
(ByVal hwndOwner As Long, _
ByVal nFolder As Long, _
ByVal hToken As Long, _
ByVal dwFlags As Long, _
ByVal lpszPath As String) As Long

Public Function sGetDesktopPath() As String
Dim sPath As String
sPath = Space$(MAX_PATH)
If SHGetFolderPath(0, CSIDL_DESKTOP, 0, 0, sPath) = 0 Then
sGetDesktopPath = Left$(sPath, InStr(sPath, vbNullChar) - 1)
End If
End Function

I got this off google. There are other ways using API calls so a google
search can reveal them.

I have comfort using an API call because if Windows does not know where the
desktop is, then we are screwed

 

[camlad]

Ah! Sorry Barb, I thought I had acknowleged and thanked - I do now - just
what I wanted.

Camlad