VBS把.CSV格式转成.XLS格式(转)
[code]'W_Path = createobject("Scripting.FileSystemObject").GetFile(Wscript.ScriptFullName).ParentFolder.Path
str = left(wscript.scriptfullname,instrrev(wscript.scriptfullname,"\")-1) '获取当前路径
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(str+"\RESULT.CSV") Then
If fso.FileExists(str+"\RESULT.xls") Then
fso.DeleteFile str+"\RESULT.xls", true
End If
strCsvFile = str+"\RESULT.CSV"
set objExcel = CreateObject("Excel.Application")
set Csvbook = objExcel.Workbooks.open(strCsvFile) '打开CSV文件
set Csvsheet = Csvbook.Worksheets(1) '取CSV文件的sheet
set Excelbook = objExcel.Workbooks.Add '新建EXCEL文件
set Excelsheet = Excelbook.Worksheets(1) '添加工作页
'获取CSV第一行列数
i=1
j=1
do while not isempty(Csvsheet.cells(i,j).value)
j=j+1
loop
k=j
'以下为复制数据
do while not isempty(Csvsheet.cells(i,1).value)
j=1
do while j<k
Excelsheet.cells(i,j).value=Csvsheet.cells(i,j).value
j=j+1
loop
i=i+1
loop
Excelbook.SaveAs str+"\RESULT.xls" '保存xls文件
wscript.echo str+"\RESULT.xls"+" 创建成功!"
'释放资源
Excelbook.close
Csvbook.close(false) '不保存对CSV文件的修改
objExcel.quit
else
wscript.echo str+"\export.csv"+" 不存在!"
end if
WScript.quit
[/code] 自己做个备份,网上很难找到直接有效的,只有如何建立excel对象的连接和操作,结合在一起就不懂了~ 我也写一个[code]'将 Csv 文件拖入此文件图标即生成同名的 Xls 文件
CsvFile = WScript.Arguments(0)
Set ExcelApp = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
If UCase(Right(CsvFile,4)) = ".CSV" Then
XlsFile = Left(CsvFile,instrrev(CsvFile,".")) & "XLS"
Set CsvBook = ExcelApp.Workbooks.open(CsvFile)
Set CsvSheet = CsvBook.Worksheets(1)
Set XlsBook = ExcelApp.Workbooks.Add
Set XlsSheet = XlsBook.Worksheets(1)
CsvSheet.UsedRange.Copy
XlsSheet.Range("A1").PasteSpecial
ExcelApp.DisplayAlerts = False
XlsBook.SaveAs(XlsFile)
XlsBook.Close
CsvBook.Close(False)
End If
WScript.Echo ".Csv 转 .Xls 完成!"
WScript.Quit[/code] [code]
Dim filespec : filespec = WScript.Arguments(0)
'CSV格式
Const xlCSV = 6
'当前版本EXCEL默认格式
Const xlWorkbookDefault = 51
'普通
Const xlWorkbookNormal = -4143
RecognizedFormatConvert filespec, xlCSV
RecognizedFormatConvert filespec, xlWorkbookDefault
RecognizedFormatConvert filespec, xlWorkbookNormal
Sub RecognizedFormatConvert(sAnyFile, lXLFormat)
Dim excel, fso, f, fname
Set excel = CreateObject("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(sAnyFile)
fName = Left(f.Name, InStrRev(f.Name, ".") - 1)
excel.Workbooks.Open f.Path, 0, True
excel.Workbooks(1).SaveAs fso.BuildPath(f.ParentFolder.Path, fName & "_New"), lXLFormat
excel.Workbooks.Close
excel.Quit
End Sub
[/code] [b]回复 [url=http://www.bathome.net/redirect.php?goto=findpost&pid=113864&ptid=18224]3#[/url] [i]cutebe[/i] [/b]
请问一下,转换CSV文件有中文,转换后,中文还是乱码的,请问下这个怎么处理
谢谢 矮油、、非常感谢楼主的分享!支持...
页:
[1]