Difficulties with SaveAs method

[aiyou]

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/scriptcenter/resources/qanda/feb05/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.

 

[William]

Hi aiyou

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

-----
XL2003
Regards

William
(e-mail address removed)

 

[aiyou]

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?

 

[William]

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
(e-mail address removed)