Excel Constants? 1

[AlexCuse]

Hi All,

I am trying to get the VBScript equivalent to this statement in excel VBA:

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False

Are there numeric constants I need to be using in place of all the paste options? Any help is greatly appreciated.

Thanks,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[PHV]

yourXLobject.Selection.PasteSpecial -4104, 4, False, False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

Thanks for the quick response PH.

I'm still getting the same error from when I used xlapp and the above code, although at least now I have the constants right.

Error is as follows:

Error Description : Error Code: 0
Error Source= Microsoft Office Excel
Error Description: PasteSpecial method of Range class failed

Error on line 17


Line 17 is as what you just showed me. Here is complete code, being used in a SQL Server DTS package.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
             Dim xlApp
             Set xlApp = CreateObject("Excel.Application")
             Set xlWB = xlApp.Workbooks.Open("c:\test.xls")
             Set WS = xlApp.Application.Workbooks(1).Sheets(1)
          
	xlApp.Range("H1").Select  	
		'enter 1 in an empty cell to use for conversion
    	xlApp.ActiveCell.FormulaR1C1 = "1"
    	xlApp.Range("H1").Select
    	xlApp.Selection.Cut		
		'cut the 1 for conversion (copy if needs to be used many times)
    	xlApp.Columns("C:C").Select
	xlApp.Selection.PasteSpecial -4104, 4, False, False

		 'Additional stuff I added for simple conversions (not text to number)
             xlApp.Columns("B:B").Select
             xlApp.Selection.NumberFormat = "General"
             xlApp.Columns("D:D").Select
             xlApp.Selection.NumberFormat = "0.00%"
              

		'Save and close open workbook
             xlApp.ActiveWorkbook.Save
             xlApp.ActiveWorkbook.Close
       
             Main = DTSTaskExecResult_Success
End Function

I have gotten this script to work, but when adding the paste special part it started giving me fits. Can you see anything in there that I am missing?

Thanks a lot,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

Hi guys, I'm an idiot. Shouldn't have used cut prior to paste special. Problem solved.

DOes anyone have a link they could post that has all of the numeric constants for excel operations?

Thanks alot,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[PHV]

Use the ObjectBrowser (F2) when in Excel's VBE.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886