Running Excel VBA from VBScript

[abesimpson]

I currently run the following macro "outside" of Excel as a VBS script:

With CreateObject("Excel.Application")
.EnableEvents = False
.DisplayAlerts = False
.Workbooks.Open "C:\Test.csv"
With .ActiveSheet
' 1 = xlAscending, 0 = xlNo (Header)
.Range("A:C").Sort .Range("C1"), 1, .Range("B1"), , 1, .Range("A1"), , , 1, 0
End With
.ActiveWorkbook.Save
.ActiveWorkbook.Close False
End With

I would like to be able to do the same with the following code:

Option Explicit
Sub DeleteDups()
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub

Is this possible, or is there something about this code that would not allow this? I have tried, obviously with no luck.

Many thanks in advance

abe

 

[Norie]

Abe

How does it not work?

Perhaps this simple code will give you a start.

Set objXLApp = CreateObject("Excel.Application")
 
Set objXLWB = objXLApp.Workbooks.Open("C:\Test.csv")
 
x = objXLApp.Evaluate("=COUNTA(A:A)")
 
objXLWB.Worksheets(1).Range("B1") = x
 
objXLWB.Close True
 
objXLApp.Quit

Please remember this is a very simple example, but it does show you how to refer to objects like Workbooks in VBS.

Well it shows you the way I know how to do it, for some reason I've got a feeling you don't need set.

Or is that JavaScript.

 

[Norie]

This also works.

Set objXLApp = CreateObject("Excel.Application")
 
Set objXLWB = objXLApp.Workbooks.Open("C:\Test.csv")
 
NoRows = objXLApp.Evaluate("=COUNTA(A:A)")
 
For I = NoRows to 2 Step -1
    Set objRng = objXLWB.Worksheets(1).Range("A" & I)
 
    if objXlApp.Evaluate("=COUNTIF(A:A," & objRng & ")") > 1 Then
       objRng.Offset(,1) ="Dup"
 else
    objRng.Offset(,1) ="NoDup"
 end if
 
Next 
 
objXLWB.Close True
 
objXLApp.Quit

 

[abesimpson]

Norie,

I'm still lost (working with my original script). The script fails at line 4: Dim x.

With CreateObject("Excel.Application")
.EnableEvents = False
.DisplayAlerts = False
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End With

Thanks for your input, I'm sure they will lead to a slap on my forehead, a Doh! and a final Aha!!!

abe

 

[Norie]

abe

Is that the current code?

It doesn't seem to be using any of the sort of things I was using in the code I posted.

As for the variables, don't type them.

 

[abesimpson]

My guilty confession is I don't understand how any of the examples work. I am flailing about between all of them; I just thought I would include my poor effort in the mix.


Many thanks again for your help. I'll let you know what I come up with as a working solution, hopefully soon.

abe