![]() |
![]() | ![]() | ![]() | ![]() | ![]() | ![]() | ![]() |
| Welcome to Windows Vista Forums. Our forum is dedicated to helping you find solutions with any problems, errors or issues you are experiencing with Windows Vista. The Vista forum also covers news and updates and has an extensive Windows Vista tutorial section that covers a wide range of tips and tricks. |
| |||||||
![]() |
| |
| | #1 (permalink) |
| | 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 Specs![]() |
| | #2 (permalink) |
| | Re: String for excel macro creation from VBS File "asimon" <asimon623@xxxxxx> wrote in message news:5963b707-8de4-4480-8106-41a1bc1bc4a7@xxxxxx Quote: > 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 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 Specs![]() |
| | #3 (permalink) |
| | Re: String for excel macro creation from VBS File On Jul 15, 9:37*am, "Richard Mueller [MVP]" <rlmueller- nos...@xxxxxx> wrote: Quote: > "asimon" <asimon...@xxxxxx> wrote in message > > news:5963b707-8de4-4480-8106-41a1bc1bc4a7@xxxxxx > > > Quote: > > 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 Quote: > > * * * ' 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" Quote: > > xlmodule.CodeModule.AddFromString strCode Quote: > > objExcel.Run "sortApamaNOW" > > objWorkbook.Close True > > objExcel.Quit > > Set objWorkbook = Nothing > > Set objExcel = Nothing > > ______________________________________ Quote: > > 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" Quote: > > 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 > -- 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 Specs![]() |
| | #4 (permalink) |
| | 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 Specs![]() |
| | #5 (permalink) |
| | 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 Specs![]() |
![]() |
| Thread Tools | |
| |
Similar Threads | ||||
| Thread | Forum | |||
| running Excel Macro in in specified Excel files in spe | PowerShell | |||
| Excel macro opens with broken links | Microsoft Office | |||
| Preventing Excel Auto_Open() Macro runnig on vpc -"Shift" doesn't | Virtual PC | |||
| Running an Excel macro usning .vbs | VB Script | |||
| Vista won't run macro in excel | Vista General | |||