Topic: xlm-SaveAs method

[Amsterdammed]

Hello everybody,

I got myself into totally unknown territory with importing the Excel9.olb to access Excel.

Everything works fine but I can’t get the xlm-SaveAs method work.

I always get

too few actual parameters   

as error code.

Does anybody know what parameters Vlisp expects me to provide?
Thanks in Advance,

Bernd

[MP]

It's not so much what Visual LISP wants, as opposed to it trying to honour what excel wants. Ackkk, sorry to go semantic on you.

Anyway, if you open Excel and drill into the object model you'll find the SaveAs method defined thusly --

SaveAs([Filename], [FileFormat], [Password], [WriteResPassword], [ReadOnlyRecommended], [CreateBackup], [AccessMode As XlSaveAsAccessMode = xlNoChange], [ConflictResolution], [AddToMru], [TextCodepage], [TextVisualLayout], [Local])

Nice concise parameter list. Wait, there's more:

Filename - Optional Variant. A string that indicates the name of the file to be saved. You can include a full path; if you don't, Microsoft Excel saves the file in the current folder.

FileFormat - Optional Variant. The file format to use when you save the file. For a list of valid choices, see the FileFormat property. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

Password - Optional Variant. A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.

WriteResPassword - Optional Variant. A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened, the file is opened as read-only.

ReadOnlyRecommended - Optional Variant. True to display a message when the file is opened, recommending that the file be opened as read-only.

CreateBackup - Optional Variant. True to create a backup file.

AddToMru - Optional Variant. True to add this workbook to the list of recently used files. The default value is False.

TextCodePage - Optional Variant. Not used in U.S. English Microsoft Excel.

TextVisualLayout - Optional Variant. Not used in U.S. English Microsoft Excel.

Local - Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

[Amsterdammed]

Michael,
Well, i tried to think that way and looked in a VBA code i wrote where i used the SaveAs method. Sure, VBA gives all that parameters you mentioned, but it is satisfied with a filename only. (At least it saves the damn file with a  filename only) So am i supposed by approaching the same method out of Vlisp to give all the parameters a  shot?

Bernd

[MP]

I generally solve problems like this, i.e. no definitive lisp instruction, like many others -- find out what works from vba and then try to replicate that in lisp. Sometimes there isn't 100% corelation, i.e. the same number of parameters working in both platforms, so then you have to resort to a more sledge hammer like technique -- start with the minimum number of arguments and keep adding them until it works. I've also found there is sometimes subtle and not so subtle differences in vla calls, vs vlax-invoke / vlax-invoke-method calls. As Autodesk hasn't bothered to document this stuff very well it means a lot of experimentation. Personbally I find it fun, I enjoy the hunt, the chase, the discovery, but it can be aggravating when you want to produce something fast.

[Amsterdammed]

As Autodesk hasn't bothered to document this stuff very well

Not very well? Are you kidding me?

You must have your mild day today, Michael.

But i see the point, I will try until i find out, just hoped that i had not to do so (lack of time).

Thanks, Bernd

[MP]

Sorry I couldn't be more helpful Amsterdammed, like I said, I enjoy being a sleuth, so it doesn't really bug me that they've not taken the time to document ActiveX methodologies thoroughly. Having said that, it's not Autodesk's responsibility to document how to use Excel via ActiveX, be it VBA or Visual LISP. Think about it for a moment.

Respectfully,

Michael.

[Amsterdammed]

Fair enough Michael.

I'm happy that i learned in this Forum that there is a way to get VBA functions imported in Vlisp.

So that makes my work easier.

For the moment i use the vl-file-copy in vlisp to make a copy of my template.xls and use the xlm-Close method wit acTrue to save it. Works. 

 The SaveAs i will find out when i have a bit more time for it.

Thanks,

 Bernd

[Jeff_M]

Personbally I find it fun, I enjoy the hunt, the chase, the discovery, but it can be aggravating when you want to produce something fast.

I will attest to this from my personal standpoint!

Amsterdammed,
Here is a function snipped from the library I have for working with Excel files. Note that it may not work by itself, but it should give you an idea of what MUST be called for SAVEAS.

Code: [Select]

(setq msxc-xlNormal -4143 );;global var

(defun MSX_WorkbookSaveAs (WrkBok FilNam OvWrYN / AltStr)
  (or OvWrYN (vlax-put-property *ExcelApp* 'DisplayAlerts :vlax-False))
  (if
    (vl-catch-all-error-p
      (setq AltStr
        (vl-catch-all-apply
          'vlax-invoke-method (list WrkBok "SaveAs" FilNam msxc-xlNormal "" "" :vlax-False :vlax-False nil)
        )
      )
    )
    (alert (strcat "File " FilNam " non salvato!\n\n" (vl-catch-all-error-message AltStr)))
  )
  (or OvWrYN (vlax-put-property *ExcelApp* 'DisplayAlerts :vlax-True))
)

[LE]

 " non salvato!\n\n"

Nice, that you are learning Italian... 

[Jeff_M]

Nice, that you are learning Italian...

While my step-father is Italian, I've never even thought about trying to learn it. I have a hard enough time wth my native language.
All I have to say in my defense is:

Here is a function snipped from the library I have....

I didn't say I wrote it  Although in retrospect I should have included the fact that the snip is from a collection of functions compiled by Marc'Antonio Alessi. The original functions were created by David Stein and Reini Urban. The complete collection is freely available over at the adesk discussion groups, or by asking for it here. If anyone wishes to search for it, the file name is MsxUtl01.lsp

[LE]

I have a hard enough time wth my native language.

 :kewl:

[Amsterdammed]

Thanks Jeff
Bernd