使用VBScript将7个CSV文件追加为A.xls独立工作表的技术需求

Got it, let's sort this out for you. Below is a VBScript that will loop through all your CSV files, open each one, and add its content as a brand new worksheet to your existing A.xls file. It keeps any existing sheets in A.xls intact, and names each new sheet after the corresponding CSV filename (minus the .csv extension).

Option Explicit

Dim objExcel, objWorkbookTarget, objWorkbookCSV, strTargetPath, strCSVPath, strCSVFile
Dim fso, folder, file

' Update these paths to match your actual file locations
strTargetPath = "C:\Your\Folder\Path\A.xls" ' Path to your existing Excel file
strCSVPath = "C:\Your\CSV\Files\Folder\" ' Folder holding your 7 CSV files

' Initialize Excel object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False ' Run Excel in the background
objExcel.DisplayAlerts = False ' Skip annoying confirmation prompts

' Open the target workbook (A.xls)
Set objWorkbookTarget = objExcel.Workbooks.Open(strTargetPath)

' Set up file system object to scan for CSV files
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(strCSVPath)

' Loop through every file in the CSV folder
For Each file In folder.Files
    If LCase(fso.GetExtensionName(file.Name)) = "csv" Then
        strCSVFile = file.Path
        
        ' Open the CSV as a read-only workbook
        Set objWorkbookCSV = objExcel.Workbooks.Open(strCSVFile, , True)
        
        ' Copy the CSV's single sheet to the end of A.xls
        objWorkbookCSV.Sheets(1).Copy After:=objWorkbookTarget.Sheets(objWorkbookTarget.Sheets.Count)
        
        ' Rename the new sheet to match the CSV filename (remove .csv)
        objWorkbookTarget.Sheets(objWorkbookTarget.Sheets.Count).Name = Left(file.Name, Len(file.Name)-4)
        
        ' Close the CSV workbook without saving changes
        objWorkbookCSV.Close False
    End If
Next

' Save changes to A.xls and close it
objWorkbookTarget.Save
objWorkbookTarget.Close

' Clean up Excel process to avoid lingering background instances
objExcel.Quit
Set objWorkbookTarget = Nothing
Set objWorkbookCSV = Nothing
Set objExcel = Nothing
Set fso = Nothing
Set folder = Nothing
Set file = Nothing

WScript.Echo "Done! All CSV files have been added as worksheets to A.xls."

Quick Tips: #

• Path Tweaks: Don't forget to update strTargetPath and strCSVPath to point to your actual file locations.
• Custom Delimiters: If your CSVs use a delimiter other than commas (like semicolons), modify the Open method for the CSV to include your delimiter:

  Set objWorkbookCSV = objExcel.Workbooks.Open(strCSVFile, , True, 6, , , , , ";") ' Replace ";" with your delimiter
  

• Debug Mode: Set objExcel.Visible = True if you want to watch the script run step-by-step for troubleshooting.

Just save this code as AppendCSVsToExcel.vbs, adjust the paths, and double-click the file to run it. It'll handle all 7 CSVs automatically!

内容的提问来源于stack exchange，提问作者Rajat Chopra