CODEWITHSUNDEEP
vbaexcelsas
ESmyth5988
ESmyth5988

Reputation: 518

VBA - open excel, find and replace, delete row, save as csv

I am trying to write a program in VBA so that I can remotely manipulate an excel file from SAS (a statistical programming software). I want the program to accomplish the following:

  1. open the specified excel file
  2. find and replace all blanks in the header row with nothing (e.g., "Test Name" become "TestName")
  3. delete the second row if first cell in row (i.e., A2) is blank
  4. save the file as a csv

I do not know VBA, have dabbled with it a little, know some other programming languages, and have tried to piece this together. I stole some code to make 1 and 4 work. I cannot get 2 and 3 to work. This is what I have:

I put the following in SAS to call the vba program -

x 'cd C:\Location';/*change to location of VBS file*/
x "vbsprogram.vbs inputfile.xls outputfile.csv";

The VBA Program -

'1 - Open the specified excel file
if WScript.Arguments.Count < 2 Then
    WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
    Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))

'2 - Find and Replace
oBook.Worksheets(1).Range("A1:G1").Select
    Selection.Replace What:="* *", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

'3 - Delete second row if blank
oBook.Cell("A2").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'4 - Save as csv
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"

Any assistance pointing me in the right direction would be much appreciated.

Also, is there a way to select all data in row 1 as the range (in part 2) and not have to specify a set range of cells?

Upvotes: 2

Views: 4245

Answers (2)

GTG
GTG

Reputation: 4954

Try:

'2:   - Find and Replace
oBook.Worksheets(1).Cells(2,1).EntireRow.Replace " ", ""

'3 - Delete second row if blank
If oExcel.CountA(oBook.Worksheets(1).Cells(2,1).EntireRow) = 0 Then
  oBook.Worksheets(1).Cells(2,1).EntireRow.Delete
End If

Upvotes: 3

Alexander Bell
Alexander Bell

Reputation: 7918

Based on your input, you need to fix statement (2) and (3) of the original code, and also concatenate the values of all cells in row 1. You can achieve this by looping through each cell in 2 Range objects corresponding to rows 1 and 2. General syntax for that operation in Excel VBA (source:http://msdn.microsoft.com/en-us/library/office/aa221353%28v=office.11%29.aspx) is shown in the following sample code snippet:

Sub FormatRow1()
    For Each c In Worksheets("Sheet1").Range("A1:G1").Cells
        if (c.Value) = {condition} then do something.
    Next
End Sub

FYI: string concatenation in Excel VBA performed with & operator. Also, you should use Replace ( string1, find, replacement, [start, [count, [compare]]] ) and Range(range).EntireRow.Delete VBA operators to complete the task.

Upvotes: 0

Related Questions