VBSsript delete column in current Excel sheet

Reimon · Post by **Reimon** » Thu Oct 15, 2009 10:47 am

Hello !!!

Please, can you give me an example of VBScript delete specific column in current open Excel sheet ?

Thanks and greetings,

Reimon

JRL · Post by **JRL** » Fri Oct 16, 2009 4:09 am

Most of the following is blatantly plagiarized from Marcus' Blog Article.
Methods for Accessing Excel Data

I came up with the "DelCol" function

Code: Select all

Let>ExcelFile=C:\test.xls

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename)
end sub

'Use this to close Excel later
Sub CloseExcel
   xlApp.quit
   Set xlApp = Nothing
End Sub

'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Cells(Row,Column).Value = NewValue
End Function

Function DelCol(Sheet,Col)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.range(Col).Delete
End Function
VBEND

//Do the business
VBRun>OpenExcelFile,ExcelFile
VBEval>GetCell("Sheet1",5,4),theValue
//MessageModal>Cell value: %thevalue%
//VBEval>SetCell("Sheet1",28,2,998),nul

//Will delete column B
//To delete a range of columns, change to "B:E" for example
VBEval>DelCol("Sheet1","B:B"),nul
//VBRun>CloseExcel

Reimon · Post by **Reimon** » Fri Oct 16, 2009 8:08 am

Very Thanks JRL !!!

I have read the Marcus Excel information but there was nothing on DelCol function.

Thanks and greetings,

Ramon Riera

Post by **Marcus Tettmar** » Fri Oct 16, 2009 9:09 am

There was nothing on the "DelCol" function because the "DelCol" function is a function that JRL just created! I couldn't write about something that someone else in the future was some day going to create.

JRL has created you a function that calls Excel's Delete method (App->Book-Sheet->Range->Delete)

If you record a macro inside Excel and delete a column, then view the source of the macro you will see very similar code created by Excel.

Then take a look at this blog post to get an idea of how to translate that code into VBScript which you can run inside Macro Scheduler:

http://www.mjtnet.com/blog/2008/04/28/c ... -vbscript/

Reimon · Post by **Reimon** » Sat Oct 17, 2009 3:36 pm

Dear Marcus and JRL, thanks.

I'm amazed !!! I'm shocked !!!

I don't know if, some day, I will be able to comprehend it, but I will keep trying !!!

Very thanks !!!

Reimon

Reimon · Post by **Reimon** » Sat Oct 17, 2009 3:58 pm

Please, if I need to load xml file with Excel with VBScript

Set xlBook = xlApp.WorkBooks.Open(xlFileName) not work well.

I see in VBA "LoadOption:=xlXmlLoadImportToList" but I do not know how to implement.

Thanks and greetings,

Reimon

Reimon · Post by **Reimon** » Sat Oct 17, 2009 4:15 pm

Sorry, in VBA:

Workbooks.OpenXML Filename:="C:\temp.xml", _
LoadOption:=xlXmlLoadImportToList

Post by **Marcus Tettmar** » Sun Oct 18, 2009 3:35 pm

Set newXMLbook = xlApp.WorkBooks.OpenXML("c:\temp.xml", , 2)

Reimon · Post by **Reimon** » Sun Oct 18, 2009 4:23 pm

Thanks Marcus.

Greetings,

Reimon