Difficulties with SaveAs method

Help please.

I'm attempting to automate a File/SaveAs process with a password. The Excel
document is created by a DataJunction/Pervasive conversion. I've
incorporated other Excel formatting and save functions post-conversion
without issue, but have run into a roadblock with the SaveAs method. I've
tried the following code (and many variations thereof), and encounter one of
two errors.

Sample obtained (and modified slightly to meet DataJunction syntax
requirements) from Microsoft
TechNet(http://www.microsoft.com/technet/scr...5/hey0207.mspx)

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1, 1).Value = Now()
objWorkbook.SaveAs "C:\Test.xls","" ,"%reTG54w"
objExcel.Quit

If I try this exactly as it is, I receive the following:

Runtime error 124: Property or method failed (Microsoft Excel: Unable to get
the SaveAs property of the Workbook class)

With other variations, I've been able to save the document, but it receives
a new name (i.e. "c:\test.xls, %reTG54w.xls"), where it imbeds the value of
the password parameter into the filename. Any ideas? Thanks in advance.

Hi aiyou

Try...
objWorkbook.SaveAs "C:\Test.xls", Password:="%reTG54w"

-----
XL2003
Regards

William
willwest22@yahoo.com


"aiyou" <aiyou@discussions.microsoft.com> wrote in message
news:767BB29D-5DF4-4598-BC56-093B0A9DC9E2@microsoft.com...
> Help please.
>
> I'm attempting to automate a File/SaveAs process with a password. The
> Excel
> document is created by a DataJunction/Pervasive conversion. I've
> incorporated other Excel formatting and save functions post-conversion
> without issue, but have run into a roadblock with the SaveAs method. I've
> tried the following code (and many variations thereof), and encounter one
> of
> two errors.
>
> Sample obtained (and modified slightly to meet DataJunction syntax
> requirements) from Microsoft
> TechNet(http://www.microsoft.com/technet/scr...5/hey0207.mspx)
>
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Visible = True
> objExcel.DisplayAlerts = FALSE
>
> Set objWorkbook = objExcel.Workbooks.Add
> Set objWorksheet = objWorkbook.Worksheets(1)
>
> objWorksheet.Cells(1, 1).Value = Now()
> objWorkbook.SaveAs "C:\Test.xls","" ,"%reTG54w"
> objExcel.Quit
>
> If I try this exactly as it is, I receive the following:
>
> Runtime error 124: Property or method failed (Microsoft Excel: Unable to
> get
> the SaveAs property of the Workbook class)
>
> With other variations, I've been able to save the document, but it
> receives
> a new name (i.e. "c:\test.xls, %reTG54w.xls"), where it imbeds the value
> of
> the password parameter into the filename. Any ideas? Thanks in advance.

Thanks, William, for your reply, but unfortunately I still get the same error.

Everything that I've read indicates SaveAs to be a 'method', but the error
indicates that it is unable to get the SaveAs 'property'? Could this be the
problem, and if so, how do I get SaveAs to be considered a method?

"aiyou" wrote:

> Help please.
>
> I'm attempting to automate a File/SaveAs process with a password. The Excel
> document is created by a DataJunction/Pervasive conversion. I've
> incorporated other Excel formatting and save functions post-conversion
> without issue, but have run into a roadblock with the SaveAs method. I've
> tried the following code (and many variations thereof), and encounter one of
> two errors.
>
> Sample obtained (and modified slightly to meet DataJunction syntax
> requirements) from Microsoft
> TechNet(http://www.microsoft.com/technet/scr...5/hey0207.mspx)
>
> Set objExcel = CreateObject("Excel.Application")
> objExcel.Visible = True
> objExcel.DisplayAlerts = FALSE
>
> Set objWorkbook = objExcel.Workbooks.Add
> Set objWorksheet = objWorkbook.Worksheets(1)
>
> objWorksheet.Cells(1, 1).Value = Now()
> objWorkbook.SaveAs "C:\Test.xls","" ,"%reTG54w"
> objExcel.Quit
>
> If I try this exactly as it is, I receive the following:
>
> Runtime error 124: Property or method failed (Microsoft Excel: Unable to get
> the SaveAs property of the Workbook class)
>
> With other variations, I've been able to save the document, but it receives
> a new name (i.e. "c:\test.xls, %reTG54w.xls"), where it imbeds the value of
> the password parameter into the filename. Any ideas? Thanks in advance.

aiyou

I retested your code and, as you say, the code failed on the line....
objWorkbook.SaveAs "C:\Test.xls", "", "%reTG54w"

I retested your code changing the line of code above with....
objWorkbook.SaveAs "C:\Test.xls", Password:="%reTG54w"
and it worked fine. I would normally declare my variables but since your
code contained no declarations, I also did not declare any variables. For
what its worth, I ran the code from an Escel workbook and from a Word
document with no instances of Excel open. No problems.

However, I do not know what DataJunction/Pervasive conversion is.
--

XL2003
Regards

William
willwest22@yahoo.com


"aiyou" <aiyou@discussions.microsoft.com> wrote in message
news:EC5D06DB-3DB5-4645-AE19-3CB0DB812D6F@microsoft.com...
> Thanks, William, for your reply, but unfortunately I still get the same
> error.
>
> Everything that I've read indicates SaveAs to be a 'method', but the error
> indicates that it is unable to get the SaveAs 'property'? Could this be
> the
> problem, and if so, how do I get SaveAs to be considered a method?
>
> "aiyou" wrote:
>
>> Help please.
>>
>> I'm attempting to automate a File/SaveAs process with a password. The
>> Excel
>> document is created by a DataJunction/Pervasive conversion. I've
>> incorporated other Excel formatting and save functions post-conversion
>> without issue, but have run into a roadblock with the SaveAs method.
>> I've
>> tried the following code (and many variations thereof), and encounter one
>> of
>> two errors.
>>
>> Sample obtained (and modified slightly to meet DataJunction syntax
>> requirements) from Microsoft
>> TechNet(http://www.microsoft.com/technet/scr...5/hey0207.mspx)
>>
>> Set objExcel = CreateObject("Excel.Application")
>> objExcel.Visible = True
>> objExcel.DisplayAlerts = FALSE
>>
>> Set objWorkbook = objExcel.Workbooks.Add
>> Set objWorksheet = objWorkbook.Worksheets(1)
>>
>> objWorksheet.Cells(1, 1).Value = Now()
>> objWorkbook.SaveAs "C:\Test.xls","" ,"%reTG54w"
>> objExcel.Quit
>>
>> If I try this exactly as it is, I receive the following:
>>
>> Runtime error 124: Property or method failed (Microsoft Excel: Unable to
>> get
>> the SaveAs property of the Workbook class)
>>
>> With other variations, I've been able to save the document, but it
>> receives
>> a new name (i.e. "c:\test.xls, %reTG54w.xls"), where it imbeds the value
>> of
>> the password parameter into the filename. Any ideas? Thanks in advance.