VBA导出指定区域至新Excel文档报错及功能优化问询

Hey there! Let's fix your VBA export macro step by step—we'll resolve the 1004 error first, then add your requested features for preset folder saving and dynamic filenames.

First, the 1004 error in your SaveAs line happens because you're passing the file extension as a separate parameter, which isn't how the SaveAs method works. Let's rewrite the code to fix that and add your two requirements:

Sub ExportTest()
    Dim originalWS As Worksheet
    Dim newWB As Workbook
    Dim exportRng As Range
    Dim presetFolder As String
    Dim fileName As String
    
    ' Set reference to your original worksheet (update "Sheet1" to your actual sheet name)
    Set originalWS = ThisWorkbook.Worksheets("Sheet1")
    
    ' Define the range you want to export
    Set exportRng = originalWS.Range("A1:K10")
    
    ' Create a new blank workbook
    Set newWB = Application.Workbooks.Add
    
    ' Copy the target range to the new workbook's first sheet
    exportRng.Copy Destination:=newWB.ActiveSheet.Range("A1")
    
    ' Set your preset folder path (make sure it ends with a backslash)
    presetFolder = "C:\Your\Target\Folder\" ' Replace with your actual folder path
    
    ' Pull filename from a cell in your original workbook (e.g., cell B2 on Sheet1)
    fileName = originalWS.Range("B2").Value ' Adjust this to your target cell
    
    ' Save the new workbook correctly
    ' xlOpenXMLWorkbook is the official constant for .xlsx (non-macro) files
    newWB.SaveAs Filename:=presetFolder & fileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    
    ' Optional: Close the new workbook if you don't need it open
    newWB.Close SaveChanges:=False
End Sub

Key Fixes & Additions: #

• 1004 Error Resolution: We combined the folder path, dynamic filename, and extension into a single Filename argument, and used the official xlOpenXMLWorkbook constant for .xlsx format instead of passing the extension separately.
• Preset Folder Support: The presetFolder variable lets you define a fixed save location—just replace the example path with your actual folder, and ensure it ends with a backslash (\).
• Dynamic Filename: We pull the filename directly from a cell in your original workbook (update originalWS.Range("B2").Value to the cell that holds your desired filename).
• Explicit References: Using ThisWorkbook ensures we're always referencing the workbook where the macro is stored, avoiding confusion if other workbooks are open.

Extra Tips: #

• If your preset folder might not exist, add this check before the SaveAs line to create it automatically:

  If Dir(presetFolder, vbDirectory) = "" Then
      MkDir presetFolder
  End If
  

• Make sure the cell you use for the filename doesn't contain invalid characters (like \ / : * ? " < > |)—these will cause a save error.

内容的提问来源于stack exchange，提问作者Dean Cohen