Access to Excel-SaveAs + password (1 Viewer)

argsName = "G:\OrderForms\" & argsName
xlsheet.Parent.SaveAs FileName:=argsName, Password:="cheese"

Currently using Access2000 to migrate data from access to excel using automation. The above code saves the excel workbook, but does not attach a password to it-despite the fact access runs through the code without error!

try adding in "FileFormat:=xlNormal,"

xlsheet.Parent.SaveAs FileName:=argsName, FileFormat:=xlNormal, Password:="cheese"

Not sure if thats it, but Office can sometimes do this... This is the correct FULL statement (Up to the password), skipping optional stuff is not allways the best way. This makes office to the thinking and that is what you do not want.... You may even want to try using the really full saveas statement, including ALL optional stuff.

Regards

I get a run time error of 1004......it doesn't recognise the constant "xlNormal"...says its empty??

this is all the code:

Dim xlApp As Object, xlsheet As Object
Set xlApp = CreateObject("excel.sheet.5")
Set xlsheet = xlApp.Application.ActiveWorkbook.sheets("sheet1")
xlApp.activesheet.Name = "Order Form"
xlsheet.cells(10, 2).formula = "Your Ref"
xlsheet.cells(12, 1).formula = "Code"
xlsheet.Parent.SaveAs FileName:= _
"C:\Documents and Settings\Test1.xls", FileFormat:=1 _
, Password:="alex", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Set xlsheet = Nothing
Set xlApp = Nothing

I have used 1 instead of xlnormal constant

Offcourse you are trying to do this from Access not Excel, so its normal that Access doesnt recognize an excel value (xl) same as Excel dont recognize the Access values (ac)

this is the actual value:
?xlnormal
-4143

you can declare it as a constant or something if you want or use the -4143 in the saveas command.... Declaring the constant is the "better practice"

Regards

I wrote out the full saveas command, but still to no avail. It may be related to a missing reference but wouldn't know where to start. The 50 or so excel files I needed to add passwords to do was acheived by putting code into a stand alone excel macro. When this excel file is opened from access the macro is automatically triggered. Not a very tidy way of doing it- you also have to click yes to enable macros on opening the excel file. By far the biggest head ache I've had with access yet.

xlsheet.Parent.SaveAs FileName:=argsName, FileFormat:=-4143, Password:=password1, WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False