现有代码复制Excel全列至文本文件，如何仅复制B列？

Hey there! Let's tweak your code to only pull column B from your Excel file into the text document. I'll cover two common approaches depending on what tool you're using—VBA (built right into Excel) and Python with pandas, in case you prefer a script outside of Excel.

If you were using a VBA macro to export all columns, here's how to modify it to target only column B:

Modified Code #

Sub ExportColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim fNum As Integer
    Dim i As Long
    
    ' Set the worksheet you want to use (active sheet here, change to Sheet1 if needed)
    Set ws = ActiveSheet
    ' Get the last row with data specifically in column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    fNum = FreeFile
    
    ' Replace this path with your desired output text file location
    Open "C:\Your\File\Path\output_column_b.txt" For Output As #fNum
    
    ' Loop through every row in column B and write to the text file
    For i = 1 To lastRow
        Print #fNum, ws.Cells(i, "B").Value
    Next i
    
    Close #fNum
    MsgBox "Column B exported successfully!", vbInformation
End Sub

Key Changes from Your Original Code: #

• Removed the inner loop that iterated over all columns
• Updated the lastRow calculation to target column B instead of other columns
• Now we only reference ws.Cells(i, "B") to pull values exclusively from column B
• Added a quick message box to confirm the export finished

If you'd rather use a script outside Excel, pandas makes this task super simple:

First, install the required packages if you haven't already:

pip install pandas openpyxl

Script Code #

import pandas as pd

# Replace with your Excel file's path
excel_file_path = "your_excel_file.xlsx"
# Load only column B from the Excel file
df = pd.read_excel(excel_file_path, usecols=["B"])

# Replace with your desired output text file path
output_file_path = "output_column_b.txt"
# Write column B to the text file (no row numbers or headers)
df.to_csv(output_file_path, index=False, header=False)

What This Does: #

• usecols=["B"] tells pandas to only load column B, ignoring all others
• to_csv writes the column data to a text file; index=False removes auto-generated row numbers, header=False skips writing the column header (remove this if you want to keep the header)

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