使用VBA调用xlwings执行Python脚本创建文件失败求助

Hey there! Let's break down why your myfile.txt isn't showing up when running your xlwings script from Excel, and fix it step by step.

Common Issues & Fixes #

1. You're Using a Relative Path (File Is Hiding Somewhere!) #

When you run xlwings from Excel, the default working directory isn't always your Excel file's folder—it might be Excel's installation directory, Python's base folder, or another system location. That means your myfile.txt is probably being created, just not where you expect it.

Fix: Use your Excel file's directory as the target location. This guarantees the file shows up right next to your workbook (or wherever you specify relative to it):

import xlwings as xw
import os

def world():
    # Get the current Excel workbook object
    wb = xw.Book.caller()
    # Extract the folder path where your Excel file lives
    workbook_folder = os.path.dirname(wb.fullname)
    # Build the full path for your text file
    text_file_path = os.path.join(workbook_folder, "myfile.txt")
    
    # Use a `with` statement to safely create/write to the file
    with open(text_file_path, "w") as f:
        f.write("Success! This file was created via xlwings.")
    
    # Optional: Pop up a message to confirm where the file was saved
    xw.msgbox(f"File created at:\n{text_file_path}")

2. You Didn't Close the File Properly #

Your original code only opens the file but never closes it. Without closing or using a with statement, the file might not be saved to disk (especially if the script exits before the file buffer flushes).

Fix: Always use the with statement for file operations—it automatically closes the file once you're done, ensuring all changes are saved (like the example above).

3. Permission Issues #

If the default working directory is a system folder (e.g., C:\Program Files), you might not have write permissions there, so the file creation fails silently.

Fix: As mentioned in point 1, using your Excel file's directory avoids this—you almost always have full permissions for folders where you store your own files.

Quick Checks to Verify #

• Make sure you've enabled macros in Excel and have the xlwings add-in installed correctly (since you got the original "Hello World" example working, this is probably okay, but it never hurts to double-check!).
• After running the script, use the message box from the example above to confirm exactly where the file was created—no more guessing!

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