Prev: CopyFile readonly
Next: How to disable and enable my Local Area Connection using scripts
From: SammyL on 5 May 2010 14:53
Hi All,

I am using a vbs to create an excel file from sql data, I then add and
run a macro to format the excel, and then remove the macro using
"xlbook.VBProject.VBComponents.Remove
xlbook.VBProject.VBComponents("Module1") " in order to send the users
a clean excel without the macro. When the user tries to download using
pop3 it results in an error and the file cannot download. There seems
to be something remaining in the file after removing the macro that is
causing it to drive pop3 crazy. it seems that there is remnant of the
macro after removal.

If i skip creation of the macro i can send successfully, but the users
need it formatted.

I also tried formatting from vbs, such as
xlapp.ActiveSheet.PageSetup.Orientation = xlapp.xlLandScape and keep
getting an error that i cannot set the properties.
Please help!


From: Tom Lavedas on 5 May 2010 16:15
On May 5, 2:53 pm, SammyL <sam...(a)gmail.com> wrote:
> Hi All,
>
> I am using a vbs to create an excel file from sql data, I then add and
> run a macro to format the excel,  and then remove the macro using
> "xlbook.VBProject.VBComponents.Remove
> xlbook.VBProject.VBComponents("Module1") " in order to send the users
> a clean excel without the macro. When the user tries to download using
> pop3 it results in an error and the file cannot download. There seems
> to be something remaining in the file after removing the macro that is
> causing it to drive pop3 crazy. it seems that there is remnant of the
> macro after removal.
>
> If i skip creation of the macro i can send successfully, but the users
> need it formatted.
>
> I also tried formatting from vbs, such as
> xlapp.ActiveSheet.PageSetup.Orientation = xlapp.xlLandScape and keep
> getting an error that i cannot set the properties.
> Please help!

The xLwhatever constants cannot be accessed (at least not easily) from
a VBScript. Rather, their value needs to be hard coded into your
script. For example, add the definition for the xlLandScape in your
script and drop the object preface ...

Const xlLandScape = 2
xlapp.ActiveSheet.PageSetup.Orientation = xlLandScape

Do similar for all of the other xl constants and your script should be
able to do all of the formatting without resorting to calling an Excel
macro.
_____________________
Tom Lavedas
 | 
Pages: 1
Prev: CopyFile readonly
Next: How to disable and enable my Local Area Connection using scripts