vbscript在范围命令的xlsx文件中选择不同的行
问题描述:
我写了一个vbscript来将xlsx文件的特定范围保存到csv文件。 我想选择不相邻的不同列。每列的范围应包含其内容的最后一行(范围("F6").End(xlToRight)
)。
我的代码:vbscript在范围命令的xlsx文件中选择不同的行
Public Sub xlsToCsv()
Const WorkingDir = "C:\"
Const xlCSV = 6
Dim fso, SaveName, myFile
Dim objExcel, objWorkbook, sheet
myFile = "test.xlsx"
SaveName = "test.csv"
With CreateObject("Scripting.FilesystemObject")
If Not .FileExists(WorkingDir & myFile) Then
MsgBox "File not found:" & vbCrLf & WorkingDir & myFile, vbInformation, "Script Cancelled"
WScript.Quit
End If
End With
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Open(WorkingDir & myFile)
With objWorkbook.Sheets(1)
.Range("D87", .Range("D87").End(-4121)).Copy
objWorkbook.Sheets.Add().paste
.Range("E87", .Range("E87").End(-4121)).Copy
End With
set sheet = objWorkbook.Sheets.Add()
sheet.paste
objWorkbook.SaveAs WorkingDir & SaveName, 23
objWorkbook.Saved = true
objWorkbook.Close
Set objWorkbook = Nothing
Set objExcel = Nothing
Set fso = Nothing
Set myFolder = Nothing
End Sub
call xlsToCsv()
我得到第18行代码800A000D与 '范围' 一类型的冲突。
我该如何做这项工作?
答
认为您的一些代码可能会丢失,如WorkingDir的声明。 我已经明确地通过添加引用的范围:随着objWorkbook.Sheets(1)
Dim WorkingDir As String
WorkingDir = "C:\test.xlsx"
Dim fso, FileName, SaveName, myFile
Dim objExcel, objWorkbook
Set fso = CreateObject("Scripting.FilesystemObject")
Set myFile = fso.GetFile(WorkingDir)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
'main operation
FileName = Left(myFile, InStrRev(myFile, "."))
Set objWorkbook = objExcel.Workbooks.Open(myFile)
With objWorkbook.Sheets(1)
.Range("F6", .Range("F6").End(xlToRight)).Copy
.Range("C6", .Range("C6").End(xlToRight)).Copy
End With
Dim sheet: Set sheet = objWorkbook.Sheets.Add()
sheet.Paste
SaveName = FileName & "csv"
objWorkbook.SaveAs SaveName, 23
objWorkbook.Saved = True
objWorkbook.Close
Set objWorkbook = Nothing
Set objExcel = Nothing
Set fso = Nothing
' Set myFolder = Nothing
答
2这边的事情:
每当你使用Range对象
Range("F6").End(xlToRight)
,你需要提及它的类型/父对象。所以,在你的情况下,这应该被替换为objWorkbook.Sheets(1).Range("F6").End(xlToRight)
VBScript不知道
xlToRight
的含义。所以,你必须使用xlToRight的值,它是-4161
如下图所示:
替换下面的代码:
objWorkbook.Sheets(1).Range("F6", Range("F6").End(xlToRight)).Copy
objWorkbook.Sheets(1).Range("C6", Range("C6").End(xlToRight)).Copy
与
With objWorkbook.Sheets(1)
.Range("F6", .Range("F6").End(-4161)).Copy
.Range("C6", .Range("C6").End(-4161)).Copy
End With
当我保存它作为.vbs错误在l ine 3发生:指示结束expectet – nolags
您声明您保存为.csv,上面代码中的第3行也只是一个声明而不会失败。在哪一行失败的代码是什么? – QHarr
Dim WorkingDir As String – nolags