ExcelBanter - excel 2007 macro/vb programing question

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - excel 2007 macro/vb programing question (https://www.excelbanter.com/excel-programming/398887-excel-2007-macro-vbulletin-programing-question.html)

Keith

excel 2007 macro/vb programing question

hello I am trying to figure out how to program excel to save a templete
through the save as function.

this is what I am trying in my vb script...

Excel.Application.ActiveWorkBook.SaveAs ("Filename:=c:\test.xlsm",
"FileFormat:=xlOpenXMLWorkbookMacroEnabled")

but when i run this command i get an error stating that the string is
invalid...i have searched on msdn for how to do this ...and that is the way
they said to do it...what am i missing

Jim Rech

excel 2007 macro/vb programing question

Your quotes don't look right:

ActiveWorkbook.SaveAs Filename:="c:\test.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled

--
Jim
"Keith" wrote in message
...
| hello I am trying to figure out how to program excel to save a templete
| through the save as function.
|
| this is what I am trying in my vb script...
|
| Excel.Application.ActiveWorkBook.SaveAs ("Filename:=c:\test.xlsm",
| "FileFormat:=xlOpenXMLWorkbookMacroEnabled")
|
| but when i run this command i get an error stating that the string is
| invalid...i have searched on msdn for how to do this ...and that is the
way
| they said to do it...what am i missing
|
|

Keith

excel 2007 macro/vb programing question

Thank you for your quick reply Jim,
I have tried the line that you supplied me, and i get error:

the requested action with this item has failed....

is this a vista permission error?

"Jim Rech" wrote:

Your quotes don't look right:

ActiveWorkbook.SaveAs Filename:="c:\test.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled

--
Jim
"Keith" wrote in message
...
| hello I am trying to figure out how to program excel to save a templete
| through the save as function.
|
| this is what I am trying in my vb script...
|
| Excel.Application.ActiveWorkBook.SaveAs ("Filename:=c:\test.xlsm",
| "FileFormat:=xlOpenXMLWorkbookMacroEnabled")
|
| but when i run this command i get an error stating that the string is
| invalid...i have searched on msdn for how to do this ...and that is the
way
| they said to do it...what am i missing
|
|

Tim Williams

excel 2007 macro/vb programing question

Excel constants and named arguments aren't available to you when automating
Excel from script.
Try substituting the actual values of the constants, and omitting the names
(they must be in the default order of course...)

Excel.Application.ActiveWorkbook.SaveAs "c:\test.xlsm", 1 'just guessing the
value here - look it up in the Excel object browser

Tim

"Keith" wrote in message
...
Thank you for your quick reply Jim,
I have tried the line that you supplied me, and i get error:

the requested action with this item has failed....

is this a vista permission error?

"Jim Rech" wrote:

Your quotes don't look right:

ActiveWorkbook.SaveAs Filename:="c:\test.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled

--
Jim
"Keith" wrote in message
...
| hello I am trying to figure out how to program excel to save a templete
| through the save as function.
|
| this is what I am trying in my vb script...
|
| Excel.Application.ActiveWorkBook.SaveAs ("Filename:=c:\test.xlsm",
| "FileFormat:=xlOpenXMLWorkbookMacroEnabled")
|
| but when i run this command i get an error stating that the string is
| invalid...i have searched on msdn for how to do this ...and that is the
way
| they said to do it...what am i missing
|
|

Jim Rech

excel 2007 macro/vb programing question

Excel.Application.ActiveWorkbook.SaveAs "c:\test.xlsm", 52

--
Jim
"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
| Excel constants and named arguments aren't available to you when
automating
| Excel from script.
| Try substituting the actual values of the constants, and omitting the
names
| (they must be in the default order of course...)
|
| Excel.Application.ActiveWorkbook.SaveAs "c:\test.xlsm", 1 'just guessing
the
| value here - look it up in the Excel object browser
|
| Tim
|
| "Keith" wrote in message
| ...
| Thank you for your quick reply Jim,
| I have tried the line that you supplied me, and i get error:
|
| the requested action with this item has failed....
|
| is this a vista permission error?
|
| "Jim Rech" wrote:
|
| Your quotes don't look right:
|
| ActiveWorkbook.SaveAs Filename:="c:\test.xlsm", _
| FileFormat:=xlOpenXMLWorkbookMacroEnabled
|
|
| --
| Jim
| "Keith" wrote in message
| ...
| | hello I am trying to figure out how to program excel to save a
templete
| | through the save as function.
| |
| | this is what I am trying in my vb script...
| |
| | Excel.Application.ActiveWorkBook.SaveAs ("Filename:=c:\test.xlsm",
| | "FileFormat:=xlOpenXMLWorkbookMacroEnabled")
| |
| | but when i run this command i get an error stating that the string is
| | invalid...i have searched on msdn for how to do this ...and that is
the
| way
| | they said to do it...what am i missing
| |
| |
|
|
|
|
|

Keith

excel 2007 macro/vb programing question

ok i got this to work...kind of...

what i can run is:

Excel.Application.ActiveWorkbook.SaveAs "test.xlsm"

and that will work...but i can only get it to save into the documents
driectory. how can i change where to save it to?

"Jim Rech" wrote:

Excel.Application.ActiveWorkbook.SaveAs "c:\test.xlsm", 52

--
Jim
"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
| Excel constants and named arguments aren't available to you when
automating
| Excel from script.
| Try substituting the actual values of the constants, and omitting the
names
| (they must be in the default order of course...)
|
| Excel.Application.ActiveWorkbook.SaveAs "c:\test.xlsm", 1 'just guessing
the
| value here - look it up in the Excel object browser
|
| Tim
|
| "Keith" wrote in message
| ...
| Thank you for your quick reply Jim,
| I have tried the line that you supplied me, and i get error:
|
| the requested action with this item has failed....
|
| is this a vista permission error?
|
| "Jim Rech" wrote:
|
| Your quotes don't look right:
|
| ActiveWorkbook.SaveAs Filename:="c:\test.xlsm", _
| FileFormat:=xlOpenXMLWorkbookMacroEnabled
|
|
| --
| Jim
| "Keith" wrote in message
| ...
| | hello I am trying to figure out how to program excel to save a
templete
| | through the save as function.
| |
| | this is what I am trying in my vb script...
| |
| | Excel.Application.ActiveWorkBook.SaveAs ("Filename:=c:\test.xlsm",
| | "FileFormat:=xlOpenXMLWorkbookMacroEnabled")
| |
| | but when i run this command i get an error stating that the string is
| | invalid...i have searched on msdn for how to do this ...and that is
the
| way
| | they said to do it...what am i missing
| |
| |
|
|
|
|
|

Jim Rech

excel 2007 macro/vb programing question

Keith

excel 2007 macro/vb programing question

right sorry what i ment to say is i get an error when i specify the c:\
before the file name...

when i try this i get:

Run-time error '1004':

this extension cannot be used with the selected file type. change the file
extension in the file name text box or select a different file type by
changing the save as type.

"Jim Rech" wrote:

Excel saves to the current folder if you do not specify one. (In my example
I specified c:\).

So add the path in the file save or change the current folder (ChDrive and
ChDir).

--
Jim
"Keith" wrote in message
...
| ok i got this to work...kind of...
|
| what i can run is:
|
| Excel.Application.ActiveWorkbook.SaveAs "test.xlsm"
|
| and that will work...but i can only get it to save into the documents
| driectory. how can i change where to save it to?
|
| "Jim Rech" wrote:
|
|
| Excel.Application.ActiveWorkbook.SaveAs "c:\test.xlsm", 52
|
| --
| Jim
| "Tim Williams" <timjwilliams at gmail dot com wrote in message
| ...
| | Excel constants and named arguments aren't available to you when
| automating
| | Excel from script.
| | Try substituting the actual values of the constants, and omitting the
| names
| | (they must be in the default order of course...)
| |
| | Excel.Application.ActiveWorkbook.SaveAs "c:\test.xlsm", 1 'just
guessing
| the
| | value here - look it up in the Excel object browser
| |
| | Tim
| |
| | "Keith" wrote in message
| | ...
| | Thank you for your quick reply Jim,
| | I have tried the line that you supplied me, and i get error:
| |
| | the requested action with this item has failed....
| |
| | is this a vista permission error?
| |
| | "Jim Rech" wrote:
| |
| | Your quotes don't look right:
| |
| | ActiveWorkbook.SaveAs Filename:="c:\test.xlsm", _
| | FileFormat:=xlOpenXMLWorkbookMacroEnabled
| |
| |
| | --
| | Jim
| | "Keith" wrote in message
| | ...
| | | hello I am trying to figure out how to program excel to save a
| templete
| | | through the save as function.
| | |
| | | this is what I am trying in my vb script...
| | |
| | | Excel.Application.ActiveWorkBook.SaveAs
("Filename:=c:\test.xlsm",
| | | "FileFormat:=xlOpenXMLWorkbookMacroEnabled")
| | |
| | | but when i run this command i get an error stating that the
string is
| | | invalid...i have searched on msdn for how to do this ...and that
is
| the
| | way
| | | they said to do it...what am i missing
| | |
| | |
| |
| |
| |
| |
| |
|
|
|

Jim Rech

excel 2007 macro/vb programing question

Jon Peltier

excel 2007 macro/vb programing question

Isn't the extension you want .xltm?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Keith" wrote in message
...
right sorry what i ment to say is i get an error when i specify the c:\
before the file name...

when i try this i get:

Run-time error '1004':

this extension cannot be used with the selected file type. change the
file
extension in the file name text box or select a different file type by
changing the save as type.

"Jim Rech" wrote:

Excel saves to the current folder if you do not specify one. (In my
example
I specified c:\).

So add the path in the file save or change the current folder (ChDrive
and
ChDir).

--
Jim
"Keith" wrote in message
...
| ok i got this to work...kind of...
|
| what i can run is:
|
| Excel.Application.ActiveWorkbook.SaveAs "test.xlsm"
|
| and that will work...but i can only get it to save into the documents
| driectory. how can i change where to save it to?
|
| "Jim Rech" wrote:
|
|
| Excel.Application.ActiveWorkbook.SaveAs "c:\test.xlsm", 52
|
| --
| Jim
| "Tim Williams" <timjwilliams at gmail dot com wrote in message
| ...
| | Excel constants and named arguments aren't available to you when
| automating
| | Excel from script.
| | Try substituting the actual values of the constants, and omitting
the
| names
| | (they must be in the default order of course...)
| |
| | Excel.Application.ActiveWorkbook.SaveAs "c:\test.xlsm", 1 'just
guessing
| the
| | value here - look it up in the Excel object browser
| |
| | Tim
| |
| | "Keith" wrote in message
| | ...
| | Thank you for your quick reply Jim,
| | I have tried the line that you supplied me, and i get error:
| |
| | the requested action with this item has failed....
| |
| | is this a vista permission error?
| |
| | "Jim Rech" wrote:
| |
| | Your quotes don't look right:
| |
| | ActiveWorkbook.SaveAs Filename:="c:\test.xlsm", _
| | FileFormat:=xlOpenXMLWorkbookMacroEnabled
| |
| |
| | --
| | Jim
| | "Keith" wrote in message
| | ...
| | | hello I am trying to figure out how to program excel to save a
| templete
| | | through the save as function.
| | |
| | | this is what I am trying in my vb script...
| | |
| | | Excel.Application.ActiveWorkBook.SaveAs
("Filename:=c:\test.xlsm",
| | | "FileFormat:=xlOpenXMLWorkbookMacroEnabled")
| | |
| | | but when i run this command i get an error stating that the
string is
| | | invalid...i have searched on msdn for how to do this ...and
that
is
| the
| | way
| | | they said to do it...what am i missing
| | |
| | |
| |
| |
| |
| |
| |
|
|
|

Bad.Fetus

excel 2007 macro/vb programing question

Here is an example:
'=============
ActiveWorkbook.SaveAs Filename:= _
sNewItem & "\Template ISO " & sSheetName & " Audit
mm.dd.yy.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False,
WriteResPassword:="xxxx"
'=============
The "_" underscore after "ActiveWorkbook.SaveAs Filename:=" is a row
continuation symbol that lets you break lines into rows. There are
two underscores in this example.
( The symbols and < used to stop confusion of ("") quote usage.

The string sNewItem & "\Template ISO " & sSheetName & " Audit
mm.dd.yy.xlsx"<< means:
sNewItem is a variable (string) that contains the folder path
"\Template ISO " is simply text
sSheetName is a variable for the sheet name
" Audit mm.dd.yy.xlsx" is simply text (spaces intentional in this
case)

My example would create this string: (Assuming sNewItem is "C:
\Myfolder" and sSheetName is "MySheet")
C:\Myfolder\Template ISO MySheet Audit mm.dd.yy.xlsx

If you want to replace a variable with text simply use "" quotes
around the text.
"C:\Myfolder" & "\Template ISO " & "MySheet" & " Audit mm.dd.yy.xlsx"
will create the same result as my example.
C:\Myfolder\Template ISO MySheet Audit mm.dd.yy.xlsx

Also I show the use of password protecting the sheet
(WriteRespassword). Changing "WriteRespassword" to "Password" will
protect the workbook and not the sheet.

All times are GMT +1. The time now is 03:00 PM.