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. 查找标题行中的所有空白并将其替换为空(例如，测试名称"变为测试名称")
3. 如果行中的第一个单元格(即 A2)为空白，则删除第二行
4. 将文件另存为 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 程序 -

'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 部分中)，而不必指定一组单元格?

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屋！