VBS Script for replacing commas in excel file then saving file in csv format

Question

I need to replace commas in a large number of excel workbooks and then save them as csv, I have the following code but don't know why it isn't working:

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel

Set oExcel = CreateObject("Excel.Application")

Dim oBook

Set oBook = oExcel.Workbooks.Open(src_file)

Dim oBookRange
Set oBookRange = oBook.ActiveWorkbook.Sheets(1)

oBookRange.Replace What:=",", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

These lines are where it seems to fail:

set oBookRange = oBook.ActiveWorkbook.Sheets(1)

oBookRange.Replace What:=",", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False

OBookRange is a worksheet and not a Range object. So it thinks you are tying to replace the worksheet with a different worksheet. You need to reference the Range of active cells in the worksheet rather than the worksheet itself. — user6406676
– user6406676, Commented Sep 12, 2016 at 16:56
What is the purpose of replacing the commas? When Excel saves a .csv file, it adds text qualifiers to cells with a delimiter in them. — Comintern
– Comintern, Commented Sep 12, 2016 at 16:57
Hi, thanks for your response. I need to remove them as the files are bulk inserted into SQL and text qualifiers are causing errors in the bulk insert. I don't think I can use a format file as not all cells have commas just maybe 300 out of 500,000 lines for each file. David are you able to tell me how I would write that? I'm not quite sure as each file will have a different number of cells. Thanks, — user6823354
– user6823354, Commented Sep 12, 2016 at 17:11
What SQL import system are you using that doesn't recognize text qualifiers on csv input? I'm still thinking that solving that issue be a more robust solution than hacking up your input data. I've found that using Excel as a csv re-formatter usually causes more problems than it solves. — Comintern
– Comintern, Commented Sep 12, 2016 at 18:49

Gary's Student · Accepted Answer · 2016-09-12 16:56:00Z

1

Replace:

Set oBookRange = oBook.ActiveWorkbook.Sheets(1)

with:

Set oBookRange = oBook.Sheets(1).Cells

There may be other problems.

answered Sep 12, 2016 at 16:56

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user6823354 Over a year ago

Hi, thanks for your response, I have tried this but unfortunately its still not working, it seems to break down here:Replace What:=",", Replacement:=" ",

Mathieu Guindon Over a year ago

@user6823354 SO is about specific programming issues. As this answer states, there may be other problems - if you're looking for help getting your entire script to work, then your question is too vague to be answered, SO is not a debugging service.

user6823354 Over a year ago

hi, the entire script works bar these 3 lines Dim oBookRange Set oBookRange = oBook.ActiveWorkbook.Sheets(1) oBookRange.Replace What:=",", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False as a last resort i posted on here to see if anyone could provide me with any insight. Thanks.

Collectives™ on Stack Overflow

VBS Script for replacing commas in excel file then saving file in csv format

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related