VBA - 打开excel，找到并替换，删除行，另存为csv [英] VBA - open excel, find and replace, delete row, save as csv

问题描述

我正在VBA中编写一个程序，以便我可以远程操作SAS（统计编程软件）中的一个excel文件。我想让程序完成以下工作：

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. 打开指定的excel文件


2. 查找和替换标题行中没有任何内容的空白（例如，测试名称成为TestName）


3. 如果行中的第一个单元格（即A2）为空白，则删除第二行


4. 将文件另存为csv

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

我不知道VBA，知道一些其他的编程语言，并试图将它拼凑在一起。我偷了一些代码，使1和4工作。我不能得到2和3的工作。这是我所拥有的：

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:

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

VBA计划 -

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.

另外，有没有办法选择第1行中的所有数据作为范围（第2部分）而不是ha ve指定单元格的设置范围？

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?

推荐答案

尝试：

'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

这篇关于VBA - 打开excel，找到并替换，删除行，另存为csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！