Desktop productivity for business analysts and programmers

Invoking VB Script with SAS EG

Reply
New Contributor
Posts: 3

Invoking VB Script with SAS EG

Hi All,

 

I want to run the following VB Script in SAS EG. The script is trying to copy the data from one excel sheet to another.

 

Dim sourcename
sourcename = "sheet1" ' CHANGE THIS TO SOURCE DATA SHEETS NAME
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objRawData = objExcel.Workbooks.Open("\\melinfprdfs01\users$\dhanarajg\Desktop\a.xlsx") ' SOURCE FILE
Set objPasteData = objExcel.Workbooks.Open("\\melinfprdfs01\users$\dhanarajg\Desktop\b.xlsx") ' TARGET FILE
Set obj1 = objPasteData.Worksheets("Sheet1") 'Worksheet to be cleared

'obj1.Cells.Clear ' NEED TO BE CAREFUL ON HERE
objRawData.Activate
'Name = objRawData.Sheets(2).Name ' CHANGE SHEETS INDEX
objRawData.Worksheets(sourcename).Select
objRawData.Worksheets(sourcename).Range("A1").Select ' SELECT A START POINT FROM SOURCE SHEETS
objExcel.ActiveSheet.UsedRange.Select
usedRowCount1 = objExcel.Selection.Rows.Count
objExcel.Range("A1:F" & usedRowCount1).Copy ' CHANGE H TO ACTUAL COLUMN
objPasteData.Activate
objPasteData.Worksheets("Sheet1").Select
objPasteData.Worksheets("Sheet1").Range("A1").PasteSpecial Paste = xlValues ' SPECIFY PASTE RANGE IN TARGET SHEETS
objPasteData.Save ' WORK OUT THE SAVE AS

 

Can someone please help me how to run the above script in SAS?

 

Thanks in advance!

Ask a Question
Discussion stats
  • 0 replies
  • 295 views
  • 0 likes
  • 1 in conversation