How to add "Filter" and Delete columns and rows in CSV/XLS via VBScrip

[Leosy]

Hello.

I'm new to this forum. Hello Everyone.

Could you help me.

I have some report and I need to Open csv/xls and than:

1. delete first row
2. delete some columns
3. add 3 kolumn names

Could you help me ? (it needs to be a function)

I now hot to do it in VBA but I don;t have idea how to to it in VBS

I Have some code:

Function OK()

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\CSVFiles\Rapport.csv", ForReading)

(...)

objFile.Close

Set objFile = objFSO.OpenTextFile("c:\CSVFiles\Text.csv", ForWriting)

objFile.Write strNewContents
objFile.Close

End Function

Could you help me ?

 

[PHV]

I now hot to do it in VBA
So, can't you try to use OLE automation ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Leosy]

I need to do it in VBS. My script should open csv/xls and moddify it.

I found some info:

Function OK()

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\BegoCSVFiles\Rapport.csv", ForReading)

objExcel.Cells(1, 1).Value = "Test value"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Font.Size = 24
objExcel.Cells(1, 1).Font.ColorIndex = 3

objFile.Close

Set objFile = objFSO.OpenTextFile("c:\BegoCSVFiles\adding.csv", ForWriting)

objFile.Write strNewContents
objFile.Close

End Function

But I see I have error:

Microsoft VBScript runtime error: Object required: 'objExcel'


;(

 

[PHV]

Don't use Scripting.FileSystemObject but Excel.Application to set objExcel ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Leosy]

Function OK()

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\BegoCSVFiles\Rapport.csv", ForReading)


Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\New_users.xls")

objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"

Set objFile = objFSO.OpenTextFile("c:\BegoCSVFiles\adding.csv", ForWriting)

objFile.Write strNewContents
objFile.Close

End Function

(12, 1) Microsoft VBScript runtime error: ActiveX component can't create object: 'Excel.Application'


;(

 

[SkipVought]

a .csv is JUST A TEXT FILE. You can manipulate like any other TEXT file, ROW BY ROW.

However, if you want to "delete columns" AS COLUMNS, you must do that IN EXCEL.

Otherwise it is a ROW BY ROW process as a TEXT file.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Leosy]

a .csv is JUST A TEXT FILE. You can manipulate like any other TEXT file, ROW BY ROW.

ok so how to manipulate it via VBS ? could you help me ?

 

[Leosy]

a .csv is JUST A TEXT FILE. You can manipulate like any other TEXT file, ROW BY ROW.

Is it possible to delete column ?

DATE;DEPART;CLIENT;CLASSE;ERREURS
2012-03-29;03:43:32;excel;error

?

for example this:

DATE;
2012-03-29;

 

[jges]

faq329-3323 might help you out.