Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
 
If there is any problem with this code, or you just looking for the explanation on how it works? Please be specific. Thanks. –  Alex Bell May 9 at 19:06
 
Part 2 and 3 of this code don't work. I pieced them together from what I could find and what I know of programming, but they are definitely missing things because I get an error. –  ESmith5988 May 9 at 19:08

2 Answers

up vote 2 down vote accepted

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
share|improve this answer
 
I get the following error: "Object doesn't support this property or method: 'Cells'" Code: 800A01B6. And I'm afraid I don't know enough to start troubleshooting this. –  ESmith5988 May 9 at 19:46
 
Sorry, forgot the worksheet reference –  GTG May 9 at 19:52
 
Thanks a million! Two quick fixes: (1) I wanted to do the find and replace in the first row so it should be "oBook.Worksheets(1).Cells(1,1)..."; and (2) I only need to check the first cell in row 2 because the rest of it contains data. After I removed .EntireRow from the first part of the statement everything worked perfectly. –  ESmith5988 May 9 at 20:06
up vote 1 down vote

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.

share|improve this answer
 
Any chance you could make that more specific to my code? I'm not following exactly... c.Value refers to the entire cell contents, right? So for statement (2) would I have something along the lines of: For Each c In Worksheets("Sheet1").Range("A1:G1").Cells Replace (c, " ", "") Next. And for statement (3) why would I search through a range when I only need to check the value in one cell (the first cell in row 2, A2)? Sorry for my lack of vision with this. –  ESmith5988 May 9 at 19:37
 
Correct: looping applies to the statement (2). For the statement (3) just check the value of that critical cell you have mentioned ("A2") and apply EntireRow.Delete if condition is met. –  Alex Bell May 9 at 19:43
 
I never got this solution to work with my code (I'm not knowledgeable enough to put it into practice effectively), but thanks for the assistance! –  ESmith5988 May 9 at 20:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.