I am trying to write a VB Script to

[jw45]

I am trying to write a VB Script to run a SAP transaction and download to Excel. Then manipulate the output. What I want to do is with the first worksheet (objWB1), is to activate it, select all data in column A, insert a sheet ‘after’ the current sheet, paste and then remove duplicates. Then select and copy this data and go back to SAP to paste there.

I have tried so many variations of code in the section after the second objWB1.Activate I can’t even remember what I've tried. Can anyone help?

sFileName1  = "ProdOrd_Detail_Wks.xlsx"
sFileName2 =  "PlnOrd_Detail_Wks.xlsx"

Set Excelobject = GetObject(, "Excel.Application")
With Excelobject
.Visible = True
Set objWB1 = .Workbooks(sFileName1)
Set objWB2= .Workbooks(sFileName2)

objWB1.Activate
.Sheets(1).Name = "ProdOrd_Detail_Wks"
.Rows("1:1").Select
.selection.replace " ","",xlpart,xlbyrows,matchcase,,,replaceformat
.Range("A1").Select
objWB1.Saved = True
'objWB1.Close	
							' 
objWB2.Activate
.Sheets(1).Name = "PlnOrd_Detail_Wks"
.Rows("1:1").Select
.selection.replace " ","",xlpart,xlbyrows,matchcase,,,replaceformat
.Range("A1").Select
objWB2.Saved = True
'objWB2.Close
'All the code works fine until this point …  
objWB1.Activate
   . Columns("A:A").Select
    .Selection.Copy
    .Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
   . Columns("A:A").Select
    .Application.CutCopyMode = False
    objwb1.ActiveSheet.Columns(1).RemoveDuplicates 1
    .Selection.Copy

 

[strongm]

ok, so in what way does your code fail? What happens?

 

[jw45]

I fixed one error. I didn't notice the space before the C ... ". Columns("A:A").Select"

But now I get "Expected Statement" on this line ".Sheets.Add After:=ActiveSheet"

 

[strongm]

Try .ActiveSheet

 

[combo]

Note that:
- all your code in lines starting with dot refers to excel application object, check if all properties/methods are applicable,
- xlpart, xlbyrows, if not earlier set, are equal to 0, the same refers to matchcase and replaceformat.

combo