Recording a 'Save As...'

C

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
 
B

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
 
C

camlad

Many thanks Bard - it looks just right

Camlad

Barb Reinhardt said:
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
 
T

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 :)
 
C

camlad

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

Camlad
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hiding an Excel file using VBA 1
Simplify save code 11
Save with ref. to cell A1 2
Two digit dates and two digit days 3
save as - to any desktop 2
Save as marco 3
Save workbook without shapes 0
Save As File Format 1

Top