Windows Vista Forums

String for excel macro creation from VBS File
  1. #1


    asimon Guest

    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

      My System SpecsSystem Spec

  2. #2


    Richard Mueller [MVP] Guest

    Re: String for excel macro creation from VBS File


    "asimon" <asimon623@xxxxxx> wrote in message
    news:5963b707-8de4-4480-8106-41a1bc1bc4a7@xxxxxx

    > 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
    I 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
    --



      My System SpecsSystem Spec

  3. #3


    T Lavedas Guest

    Re: String for excel macro creation from VBS File

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

    > "asimon" <asimon...@xxxxxx> wrote in message
    >
    > news:5963b707-8de4-4480-8106-41a1bc1bc4a7@xxxxxx
    >
    >
    >

    > > 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
    >
    > I 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
    > --
    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
    ***********

      My System SpecsSystem Spec

  4. #4


    asimon Guest

    Re: String for excel macro creation from VBS File

    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

      My System SpecsSystem Spec

  5. #5


    asimon Guest

    Re: String for excel macro creation from VBS File

    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

      My System SpecsSystem Spec

String for excel macro creation from VBS File problems?

Similar Threads
Thread Thread Starter Forum Replies Last Post
vbscript start excel run macro not working khstng50 VB Script 0 17 Mar 2010
running Excel Macro in in specified Excel files in spe Observer PowerShell 0 03 Apr 2009
Excel macro opens with broken links bigezbookkeepin Microsoft Office 0 27 Mar 2009
Running an Excel macro usning .vbs scot VB Script 2 27 Jun 2008
Vista won't run macro in excel jay Vista General 3 20 May 2008