Using VBS to write VBA macros in Excel

TheMSsForum.com: The Microsoft Software Forum

Is there any way to open a file in Excel and write a macro into the
visual basic editor?

At first I tried to do automation using VBS but found that I need to
keep on searching for values of various constants defined in Excel to
make them work in VBS.

My idea is this. Suppose I need to create a specific kind of chart, the
program I use (SAS) can generate a VBS file and run it. The VBS file in
turn will generate an excel file, open VB editor inside it and write a
macro and then run it.

Is there any way to do this?

Thanks in advance.

Indrajit
Top

Re: Using VBS to write VBA macros in Excel by Michael

Michael
Fri Sep 15 08:54:17 CDT 2006

On 15 Sep 2006 02:53:39 -0700, indra_calisto@... wrote in
microsoft.public.scripting.vbscript:

>Is there any way to open a file in Excel and write a macro into the
>visual basic editor?
>
>At first I tried to do automation using VBS but found that I need to
>keep on searching for values of various constants defined in Excel to
>make them work in VBS.
>
>My idea is this. Suppose I need to create a specific kind of chart, the
>program I use (SAS) can generate a VBS file and run it. The VBS file in
>turn will generate an excel file, open VB editor inside it and write a
>macro and then run it.
>
>Is there any way to do this?

I don't think so - it would present a major security problem.

What is it that you think you can do only in VBA but not in VBS?

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"
Top

Re: Using VBS to write VBA macros in Excel by mr_unreliable

mr_unreliable
Fri Sep 15 12:10:35 CDT 2006

hi Indrajit,

I would assert that it _is_ possible.

Here is an overview of the process:

- Create one (or more) code modules,
- Load your vb code into the code modules,
(I used "AddFromString", but you could
also "import" from a separate file)...
- Then execute your code, using "Run"...

You could get the script code for this by turning on
your macro recorder and (manually) going through the
steps. Then turn off the recorder, and convert the
vba code you get into vbScript (shouldn't be hard
to do).

If you wish, I can post some sample code.

cheers, jw
____________________________________________________________

You got questions? WE GOT ANSWERS!!! ..(but,
no guarantee the answers will be applicable to the questions)


indra_calisto@yahoo.com wrote:
> Is there any way to open a file in Excel and write a macro into the
> visual basic editor?
>
> At first I tried to do automation using VBS but found that I need to
> keep on searching for values of various constants defined in Excel to
> make them work in VBS.
>
> My idea is this. Suppose I need to create a specific kind of chart, the
> program I use (SAS) can generate a VBS file and run it. The VBS file in
> turn will generate an excel file, open VB editor inside it and write a
> macro and then run it.
>
> Is there any way to do this?
>
> Thanks in advance.
>
> Indrajit
>
Top

Re: Using VBS to write VBA macros in Excel by JHP

JHP
Fri Sep 15 12:35:48 CDT 2006

Turning on the Macro Recorder does not work for this particular problem, but
this does:
(watch word wrap)

NB*: Refer to the Microsoft article at the bottom to resolve security
issues...

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objWorkBook = objExcel.Workbooks.Add

With objWorkBook.VBProject
Set objMacro = .VBComponents.Add(1)
objMacro.CodeModule.InsertLines 1, "Sub NewMacro" & vbCRLF & vbTab &
"MsgBox(""TEST"")" & vbCRLF & "End Sub"
objExcel.Application.Run("NewMacro")
.VBComponents.Remove objMacro
Set objMacro = Nothing
End With
objWorkBook.Close()
objExcel.Quit()
Set objWorkBook = Nothing
Set objExcel = Nothing

'Security Issues: http://support.microsoft.com/kb/282830/EN-US/

'.VBComponents.Add 1 - Add Module
'.VBComponents.Add 2 - Add Class Module
'.VBComponents.Add 3 - Add UserForm


"mr_unreliable" <kindlyReplyToNewsgroup@notmail.com> wrote in message
news:%23Dy6ZkO2GHA.1040@TK2MSFTNGP06.phx.gbl...
> hi Indrajit,
>
> I would assert that it _is_ possible.
>
> Here is an overview of the process:
>
> - Create one (or more) code modules,
> - Load your vb code into the code modules,
> (I used "AddFromString", but you could
> also "import" from a separate file)...
> - Then execute your code, using "Run"...
>
> You could get the script code for this by turning on
> your macro recorder and (manually) going through the
> steps. Then turn off the recorder, and convert the
> vba code you get into vbScript (shouldn't be hard
> to do).
>
> If you wish, I can post some sample code.
>
> cheers, jw
> ____________________________________________________________
>
> You got questions? WE GOT ANSWERS!!! ..(but,
> no guarantee the answers will be applicable to the questions)
>
>
> indra_calisto@yahoo.com wrote:
>> Is there any way to open a file in Excel and write a macro into the
>> visual basic editor?
>>
>> At first I tried to do automation using VBS but found that I need to
>> keep on searching for values of various constants defined in Excel to
>> make them work in VBS.
>>
>> My idea is this. Suppose I need to create a specific kind of chart, the
>> program I use (SAS) can generate a VBS file and run it. The VBS file in
>> turn will generate an excel file, open VB editor inside it and write a
>> macro and then run it.
>>
>> Is there any way to do this?
>>
>> Thanks in advance.
>>
>> Indrajit
>>


Top

Re: Using VBS to write VBA macros in Excel by JHP

JHP
Fri Sep 15 12:45:24 CDT 2006

mr_unreliable,
I misread what you were saying (sorry) - we have the same approach...

Cheers!

"mr_unreliable" <kindlyReplyToNewsgroup@notmail.com> wrote in message
news:%23Dy6ZkO2GHA.1040@TK2MSFTNGP06.phx.gbl...
> hi Indrajit,
>
> I would assert that it _is_ possible.
>
> Here is an overview of the process:
>
> - Create one (or more) code modules,
> - Load your vb code into the code modules,
> (I used "AddFromString", but you could
> also "import" from a separate file)...
> - Then execute your code, using "Run"...
>
> You could get the script code for this by turning on
> your macro recorder and (manually) going through the
> steps. Then turn off the recorder, and convert the
> vba code you get into vbScript (shouldn't be hard
> to do).
>
> If you wish, I can post some sample code.
>
> cheers, jw
> ____________________________________________________________
>
> You got questions? WE GOT ANSWERS!!! ..(but,
> no guarantee the answers will be applicable to the questions)
>
>
> indra_calisto@yahoo.com wrote:
>> Is there any way to open a file in Excel and write a macro into the
>> visual basic editor?
>>
>> At first I tried to do automation using VBS but found that I need to
>> keep on searching for values of various constants defined in Excel to
>> make them work in VBS.
>>
>> My idea is this. Suppose I need to create a specific kind of chart, the
>> program I use (SAS) can generate a VBS file and run it. The VBS file in
>> turn will generate an excel file, open VB editor inside it and write a
>> macro and then run it.
>>
>> Is there any way to do this?
>>
>> Thanks in advance.
>>
>> Indrajit
>>


Top