excel to csv using visual studio

Question

我正在尝试使用Visual Studio将我的excel文件转换为csv，但在转换时遇到问题。我已经循环了我的代码以浏览文件夹中.xls或.xlsx文件，并将每个文件转换为csv。但是，我根本没有结果:(

文本框 1.文本是

所选文件夹，文本框 2.文本是目标文件夹。有人可以帮助我吗？

这是我的代码：

Dim xls As Excel.Application
Dim strFile As String, strPath As String
xls = New Excel.Application
strPath = TextBox1.Text
strFile = Dir(strPath & "*.xls")
While strFile <> ""
  xls.Workbooks.Open(strPath & strFile)
  xls.ActiveWorkbook.SaveAs(Filename:=Replace(TextBox2.Text & strFile, ".xls", ".csv"), FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlTextMSDOS)
  xls.Workbooks.Application.ActiveWorkbook.Close(SaveChanges:=False)
  strFile = Dir()
End While
xls.Quit()

Answer 1

把它放在一个文本文件中，另存为Excel2Csv.vbs。将其保存在包含所有 excel 文件的文件夹中。然后只需将您的 excel 文件拖到此 .vbs 文件上即可。

'* Usage: Drop .xl* files on me to export each sheet as CSV
'* Global Settings and Variables
Dim gSkip
Set args = Wscript.Arguments
For Each sFilename In args
    iErr = ExportExcelFileToCSV(sFilename)
    ' 0 for normal success
    ' 404 for file not found
    ' 10 for file skipped (or user abort if script returns 10)
Next
WScript.Quit(0)
Function ExportExcelFileToCSV(sFilename)
    '* Settings
    Dim oExcel, oFSO, oExcelFile
    Set oExcel = CreateObject("Excel.Application")
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    iCSV_Format = 6
    '* Set Up
    sExtension = oFSO.GetExtensionName(sFilename)
    if sExtension = "" then
        ExportExcelFileToCSV = 404
        Exit Function
    end if
    sTest = Mid(sExtension,1,2) '* first 2 letters of the extension, vb's missing a Like operator
    if not (sTest =  "xl") then
        if (PromptForSkip(sFilename,oExcel)) then
            ExportExcelFileToCSV = 10
            Exit Function
        end if
    End If
    sAbsoluteSource = oFSO.GetAbsolutePathName(sFilename)
    sAbsoluteDestination = Replace(sAbsoluteSource,sExtension,"{sheet}.csv")
    '* Do Work
    Set oExcelFile = oExcel.Workbooks.Open(sAbsoluteSource)
    For Each oSheet in oExcelFile.Sheets
        sThisDestination = Replace(sAbsoluteDestination,"{sheet}",oSheet.Name)
        oExcelFile.Sheets(oSheet.Name).Select
        oExcelFile.SaveAs sThisDestination, iCSV_Format
    Next
    '* Take Down
    oExcelFile.Close False
    oExcel.Quit
    ExportExcelFileToCSV = 0
    Exit Function
End Function
Function PromptForSkip(sFilename,oExcel)
    if not (VarType(gSkip) = vbEmpty) then
        PromptForSkip = gSkip
        Exit Function
    end if
    Dim oFSO
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    sPrompt = vbCRLF & _
        "A filename was received that doesn't appear to be an Excel Document." & vbCRLF & _
        "Do you want to skip this and all other unrecognized files?  (Will only prompt this once)" & vbCRLF & _
        "" & vbCRLF & _
        "Yes    - Will skip all further files that don't have a .xl* extension" & vbCRLF & _
        "No     - Will pass the file to excel regardless of extension" & vbCRLF & _
        "Cancel - Abort any further conversions and exit this script" & vbCRLF & _
        "" & vbCRLF & _
        "The unrecognized file was:" & vbCRLF & _
        sFilename & vbCRLF & _
        "" & vbCRLF & _
        "The path returned by the system was:" & vbCRLF & _
        oFSO.GetAbsolutePathName(sFilename) & vbCRLF
    sTitle = "Unrecognized File Type Encountered"
    sResponse =  MsgBox (sPrompt,vbYesNoCancel,sTitle)
    Select Case sResponse
    Case vbYes
        gSkip = True
    Case vbNo
        gSkip = False
    Case vbCancel
        oExcel.Quit
        WScript.Quit(10)    '*  10 Is the error code I use to indicate there was a user abort (1 because wasn't successful, + 0 because the user chose to exit)
    End Select
    PromptForSkip = gSkip
    Exit Function
End Function