Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording a 'Save As...'
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording a 'Save As...'
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording a 'Save As...'
Many thanks Bard - it looks just right
Camlad "Barb Reinhardt" wrote in message ... 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording a 'Save As...'
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 :) -- Regards, Tim Zych "Barb Reinhardt" wrote in message ... 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recording a 'Save As...'
Ah! Sorry Barb, I thought I had acknowleged and thanked - I do now - just
what I wanted. Camlad "Barb Reinhardt" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recording macro to save file with name change | Excel Programming | |||
How do I make the Stop Recording bar pop up when recording macros | Excel Worksheet Functions | |||
Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: | Excel Programming | |||
recording | Excel Programming | |||
Problem- Recording macros for "file save" and "File open" | Excel Programming |