CODEWITHSUNDEEP
excelvbavbscript
Mike
Mike

Reputation: 43

Create an Excel Table in VBScript

I am trying to auto-detect a data range in an xlsx file and turn it into a formatted table. I want it to be the same as the Excel formatted table (CTRL-T in app)

I am getting an error saying 'Object doesn't support this property of method: wb.ListObjects'.

I show that VBA uses ListObjects to create a table. Is there something different in VB Script?

Const xlDelimited                =  1
Const xlTextQualifierDoubleQuote =  1
Const xlOpenXMLWorkbook          = 51

Set xl = CreateObject("Excel.Application")

''Working: Convert csv to xlsx. Is all this necessary for a simple csv to xlsx convert?
xl.Workbooks.OpenText "C:\Users\me\Desktop\Automate\data2.csv", , , xlDelimited _
  , xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
  , Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
  , Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook


'This is still not working...
'wb.ListObjects.add xlSrcRange,x1.Range(),,XlYes
wb.ListObjects(1).method


''Working: Save file with current date.
wb.SaveAs "C:\Users\me\Desktop\Automate\data2" & "(" & Right(Day(Date),2) & MonthName(Month(Date()),1) & Year(Date) & ")" & ".xlsx", xlOpenXMLWorkbook, , , , False


wb.Close

xl.Quit

Thanks!

Upvotes: 0

Views: 4231

Answers (2)

Tim Williams
Tim Williams

Reputation: 166146

Const xlOpenXMLWorkbook = 51
Const xlYes = 1
Const xlSrcRange = 1
Dim wb,sht

Set xl = CreateObject("Excel.Application")
'xl.visible=true 'uncomment if you want to see Excel

Set wb = xl.Workbooks.open("D:\Analysis\VBScript\data.csv")
Set sht = wb.worksheets(1)

'create a new listobject from the Range with top-left=A1
sht.ListObjects.Add xlSrcRange, sht.Range("A1").CurrentRegion, , xlYes

xl.DisplayAlerts = False 'don't show "a file already exists..."
wb.SaveAs "D:\Analysis\VBScript\dataOut.xlsx", xlOpenXMLWorkbook, , , , False
xl.DisplayAlerts = True
wb.Close

xl.Quit

Upvotes: 1

Serenity
Serenity

Reputation: 1918

There are no constants available in vbscript. You have to look values up in Object Viewer and put them in. EG xlYes = 1 so replace with a 1.

According to the object model it is a collection of ListObjects in a worksheet not a single object, so wb.ListObjects(1).method.

According to the object model there is no add method.

Help has an example on how to use this.

Upvotes: 0

Related Questions