Vista - String for excel macro creation from VBS File

This fails:
___________________________________________________________________________
Dim objExcel
Dim objWorkbook
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings
\asimon\My Documents\NetBeansProjects\RBSApamaCompare\apama.xls")
Dim xlmodule
Set xlmodule = objWorkbook.VBProject.VBComponents.Add(1)
'vbext_ct_StdModule

' Add a macro to the module...
Dim strCode
strCode = _
"Sub sortApamaNOW()"& vbCr & _
"Sheet1.Activate" & vbCr & _
"Sheet1.Cells.Select" & vbCr & _
"Selection.sort Key1:=Range("E1"), Order1:=xlAscending,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal" & vbCr & _
"Sheet1.Activate" & vbCr & _
"Sheet1.Cells.Select" & vbCr & _
"Selection.sort Key1:=Range("I1"), Order1:=xlAscending, Key2:=Range
("F1"), Order2:=xlAscending, Key3:=Range("H1"), Order3:= xlAscending,
OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:=xlSortNormal" & vbCr & _
"End Sub"

xlmodule.CodeModule.AddFromString strCode

objExcel.Run "sortApamaNOW"
objWorkbook.Close True
objExcel.Quit
Set objWorkbook = Nothing
Set objExcel = Nothing
______________________________________


The problem, I believe, is that the ":=" is not understood to be a
part of the string but rather some other function.
This script works if I replace the macro that I actually need with an
example one like:
"sub sortApamaNOW()" & vbCr & _
"msgbox ""Inside generated macro!!!"" " & vbCr & _
"end sub"

Anybody have a way around this? Thanks

"asimon" <asimon623@xxxxxx> wrote in message
news:5963b707-8de4-4480-8106-41a1bc1bc4a7@xxxxxxI think the problem is that you did not double quotes embedded in some of
the strings. For example, the fragment below has doubled quotes:

Selection.sort Key1:=Range(""I1""), Order1:=xlAscending,

For troubleshooting, you can echo the value of strCode in a command window
to see what it looks like.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

On Jul 15, 9:37*am, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxx> wrote:Richard,

What you say would appear to be correct. However, that may not be all
there is.

Further, my question is whether trying to build and run an Excel macro
is the appropriate way to accomplish the task. It has got to be a
nightmare to troubleshoot, as OP has found. It would seem to me that
either the macro should be installed in the spreadsheet and run from
there OR the procedures should be exercised from the VBS script
itself. The hybrid approach just seems too problematic to me.

I don't have time to test this, but my first cut at doing it entirely
from VBS would look something like this ...

Dim objExcel
Dim objWorkbook
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(_
"C:\Documents and Settings\asimon\My Documents\" _
& "NetBeansProjects\RBSApamaCompare\apama.xls")

sortApamaNOW objWorkbook

objWorkbook.Close True
objExcel.Quit
Set objWorkbook = Nothing
Set objExcel = Nothing

Sub sortApamaNOW(oWBK)
Const xlAscending = 1, _
xlTopToBottom = 1, _
xlSortNormal = 0

with oWBK.Sheet1
.Activate
.Cells.Select
oWBK.Selection.sort Range("E1"), xlAscending, 1, False, _
xlTopToBottom,xlSortNormal
.Activate
.Cells.Select
oWBK.Selection.sort Range("I1"), xlAscending,Range("F1"), _
xlAscending, Range("H1"), xlAscending,1, True, _
xlTopToBottom,xlSortNormal, xlSortNormal,xlSortNormal
end with
End Sub

Once checked and corrected, this certainly would be a lot easier to
understand and maintain, I think.

Tom Lavedas
***********

Thanks for helping -- I didn't realize this was even an option.
It fails at line 21 --the oWBK.Sheet1 error says, "object doesn't
support this property or method"
--not sure what this line is even hoping to accomplish and thus am
having difficulty troubleshooting it


/Alex

oh didn't even see post #1 --double quotes works. Nice man, thanks
alot. Hybrid method. I'm thinking now that maybe I should try Tom's
straight in method though --might be more reliable/quicker.

Thanks