VBS-循环浏览文件夹中的多个.csv文件并将其转换为.xlsx [英] VBS - Loop through multiple .csv files in a folder and convert the files to .xlsx

问题描述

我设法将以下代码放在一起：

I managed to get the following piece of code put together:

'Constants
Const xlOpenXMLWorkbook = 51             '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50                     '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56                       '(97-2003 format in Excel 2007-2016, xls)

' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get folder name to process off the command line, make sure it's valid
If (WScript.Arguments.Count > 0) Then
    strFolder = WScript.Arguments(0)
    If Not objFSO.FolderExists(strFolder) Then
        WScript.StdErr.WriteLine "Specified folder does not exist."
        WScript.Quit
    End If
Else
    WScript.StdErr.WriteLine "No folder name specified to process."
    WScript.Quit
End If

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
    ' Get full path to file
    strPath = objFile.Path
    ' Only convert CSV files
    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Then
        ' Display to console each file being converted
        WScript.Echo "Converting """ & strPath & """"
        ' Load CSV into Excel and save as native Excel file
        Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True)
        objWorkbook.SaveAs Replace(strPath, strCSV, strExcel), xlOpenXMLWorkbook
        objWorkbook.Close False
        Set objWorkbook = Nothing
    End If
Next

'Wrap up
objExcel.Quit
Set objExcel = Nothing
Set objFSO = Nothing 

不幸的是，我有3个问题：

Unfortunately I have 3 issues:

1. 我被指示按照以下方式运行它：
   
   复制上面的代码并保存它作为 csv.vbs
   
   转到CMD并输入

1. I was instructed to run this in the following manner:
   Copy the code above and saved it as csv.vbs
   Go to CMD and type in

cscript csv.vbs "C:\Users\Eitel\Desktop\3rd Party\Work Folder"

这是CSV文件所在的路径。

This is the path where the CSV files are.

我希望通过单击/打开来执行代码一个VBScript。

I would prefer to have a way of executing the code by clicking on/opening a VBScript.

我收到此错误：

输入错误：找不到脚本文件 C：\Users\Eitel\csv.vbs

Input Error: Can not find script file "C:\Users\Eitel\csv.vbs"

我转到 C：\Users\Eitel\csv.vbs 并粘贴了 csv。 vbs 文件放在此位置。我再次运行该命令，这就是显示的内容：

I went to "C:\Users\Eitel\csv.vbs" and pasted the csv.vbs file in this location. I ran the command again and this is what was displayed:

C：\Users\Eitel\Desktop\3rd Party \工作文件夹\TestFile.CSV

C：\用户\Eitel\csv.vbs（44.9）Microsoft Excel：无法另存为该名称。文档以只读方式打开。

"C:\Users\Eitel\Desktop\3rd Party\Work Folder\TestFile.CSV"
C:\Users\Eitel\csv.vbs(44.9) Microsoft Excel: Cannot save as that name. Document was opened as read-only.

我不知道这是什么意思，为什么会发生？

I have no clue what this means or why it happens?

我注意到，虽然大多数文件都是.csv扩展名，但有些文件扩展名显示为.CSV，而有些则是.csv。我想知道这是否会影响脚本的执行方式吗？

I noticed that while most of the files are .csv extensions, some of the files extensions are displayed as .CSV and some are .csv. I am wondering if this will affect the way in which the script is executed?

推荐答案

这是我需要的解决方案：

Here is the solution I needed:

链接： https://www.experts-exchange.com/questions/29088597/Change-Multiple-csv-files-into -xlsx-files.html？notificationFollowed = 205599875

'Constants 
Const xlOpenXMLWorkbook = 51             '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50                     '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56                       '(97-2003 format in Excel 2007-2016, xls)

' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"
strXLS = "xls"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

strFolder = "B:\EE\EE29088597\Files"

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
    ' Get full path to file
    strPath = objFile.Path
    ' Only convert CSV files
    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
        ' Display to console each file being converted
        Wscript.Echo "Converting """ & strPath & """"
        ' Load CSV into Excel and save as native Excel file
        Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True)
        strNewPath = objFSO.GetParentFolderName(strPath) & "\" & objFSO.GetBaseName(strPath) & "." & strExcel
        objWorkbook.SaveAs strNewPath, xlOpenXMLWorkbook
        objWorkbook.Close False
        Set objWorkbook = Nothing
    End If
Next

'Wrap up
objExcel.Quit
Set objExcel = Nothing
Set objFSO = Nothing

这将扫描目录中的任何xls或csv文件，并将它们转换为xlsx文件。

This will scan the directory for any xls or csv file and convert them into xlsx files.

这篇关于VBS-循环浏览文件夹中的多个.csv文件并将其转换为.xlsx的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！