Issue Converting VBA to VBS

Question

I am having issues converting the following VBA code to VBS. I am a novice in regards to writing VBS, so any assistance would be greatly appreciated.

Public Sub Details()

    Set xl = New Excel
    Set wb = xl.obj_.Workbooks.Add
    Const xlWhole = 1 
    Const xlPart  = 2 
    Const ColField = "Combined Field2"

    Dim k, bfind 
    Set bfind = x1.ActiveSheet.UsedRange.Cells.Find(what:=ColField, lookat:=xlWhole)
    k = bfind.Column
    Columns(k).Delete
    'create pivot
                x1.ActiveSheet.Activate
     Set PCache = x1.ActiveWorkbook.PivotCaches.Create(SourceType:=1, SourceData:=Range("A1").CurrentRegion.Address)
         x1.Worksheets.Add
         x1.ActiveSheet.Name = "Pivot"
         x1.ActiveWindow.DisplayGridlines = False
     Set pt = x1.ActiveSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=Range("A1"), TableName:="PivotTable1")
     With pt
            .InGridDropZones = True
            .RowAxisLayout xlTabularRow

        .DisplayContextTooltips = False
        .ShowDrillIndicators = False
       End With

End Sub

http://stackoverflow.com/questions/25927864/convert-vba-code-to-vbscript If you need to more specific help then you need to describe exactly what "issues" you have... — Tim Williams, Dec 11 '14 at 20:55
You will want to mention in your post what your QUESTION is, and what Problems you are having. Posting the code is a great start, don't get me wrong. Perhaps someone can just look at it and know what's wrong instantly. I'm guessing since you don't have a specific question and you are just stating you are having issues without listing them specifically, it won't get the attention you need. as a general rule of thumb. — peege, Dec 11 '14 at 20:56
The issue starts with the line: Set bfind = x1.ActiveSheet.UsedRange.Cells.Find(what:=ColField, lookat:=xlWhole) — Christopher Cannata, Dec 11 '14 at 21:20
Wait a minute... are you confusing `l` with `1`? Did you type this using a typewriter? Anyhow, I suggest you type `Option Explicit` at the top of your code. — Jean-François Corbett, Dec 11 '14 at 22:11
@Jean-FrançoisCorbett oooh good catch here! Wow... indeed it looks like OP is declaring `xl` and then using `x1`. — Mathieu Guindon, Dec 12 '14 at 22:23

score 1 · Accepted Answer · edited May 23 '17 at 11:43

Set xl = New Excel

I don't think VBScript can create an Excel instance just like that. I don't do VBScript, so it might, but I've always seen Excel instantiated something like this:

' 1a. Get an existing Excel instance...
Set Excel = GetObject(, "Excel.Application")

' 1b. Or, create one. Make it visible for testing.
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True

(taken from this answer)

Issue Converting VBA to VBS

1 Answers1