MrWinelover
Tue Apr 29 02:03:01 CDT 2008
So far the method given by ekkard horner works for the big part of what i
wont to achieve.
The script that i made makes a new Excel file and import a CSV file into it.
then Column headers were set and sorting is done. So now i want to ad the
part of conditional formatting. Its now this last part where a found
dificulties.
Like I earlier indicated, i tried to convert a Excel macro to VBScript.
So the line from the macro
> selection.FormatConditions.Add Type:=xlTextString, String:="8-", TextOperator:=xlBeginsWith
I changed in
> selection.FormatConditions.Add xlTextString, "8-", xlBeginsWith
and made the constants Const xlTextString = 9 and Const xlBeginsWith = 2
This approach do not works. Does someone have a suggestion?
--
MrWinelover...
"ekkehard.horner" wrote:
> MrWinelover schrieb:
> > I had a macro in Excel with the a command to Add conditional formatting based
> > on the check if a cells value begins with some predefined text.
> > Selection.FormatConditions.Add _
> > Type:=xlTextString, String:="apple", TextOperator:=xlBeginsWith
> > Now i will change from having an Excel file with a macro in it, to a
> > VBScript with is creating the Excel file from scratch. Placing the code from
> > the macro in the VBScript does't work.
> > Does someone have an idea?
>
> To convert a VBA macro to VBScript:
>
> (1) Get rid of VBA's type specifiers
>
> Dim VarName As Type ==> Dim VarName
>
> (2) Add constants for all xl*-constants used; Excel 'knows'
> xlDescending; VBScript has to be told:
>
> Const xlDescending = 2
>
> (you can avoid this by using a .wsf (<reference> tag);
> this
>
> ---------
> <?xml version="1.0" standalone="yes" encoding="iso-8859-1" ?>
> <package>
> <job id="QEC">
> <reference object="Excel.Sheet" reference="true"/>
> <script language="VBScript">
> <![CDATA[
> ' ############################################################################
> a = inputbox ( "Name of an Excel Constant?" )
> msgbox a & " = " & eval(a)
> ' ############################################################################
> ]]>
> </script>
> </job>
> </package>
> ---------
>
> shows the use of the <reference> tag; as long as there is no loop
> in the script, using Excel's Immediate Window to get the values is
> much more convenient)
>
> (3) Change VBA's named parameters to positional ones:
>
> Use the Excel/VBA documentation to determine the prototype of the
> function(s)/sub(s)/method(s) you want to use. E.g.: In a macro the
> FormatConditions.Add method may be called like this:
>
> Selection.FormatConditions.Add _
> Type:=xlCellValue _
> , Operator:=xlGreater _
> , Formula1:="50000"
>
> if you are lucky the method's parameter list looks like
>
> Add Type, Operator, Formula1, Formula2
>
> then you can write
>
> Selection.FormatConditions.Add xlCellValue, xlGreater, "50000"
>
> (you have defined xlCellValue etc, I trust)
> otherwise you'll have to distribute your values to the correct
> positions. E.g.
>
> Add Operator, Formula2, Type, Formula1
> ==>
> Selection.FormatConditions.Add xlGreater, , xlCellValue, "50000"
>
> (use , , to skip (optional) parameters you can't/don't want to set)
>
> (4) Replace the context/user action dependencies (.Selection, .Active*)
> with context free references (.Range(...), .Workbooks( "Name" ))
>
> (5) Post your macro code if my attempt to explain what has to be done/
> what I mean has failed
>
> Good luck!
>
>