如何修改VBA宏实现源Excel工作簿文件名复制到目标工作簿

解决方案 #

你现有代码里已经完成了「获取源工作簿文件名」的逻辑：你打开CSV文件后就把文件名存储到了Wb2Name变量里，只需要在粘贴数据后新增几行代码，把这个变量的值写入目标工作表的指定位置即可。

常见写入场景示例 #

场景1：把文件名写入DATA工作表的固定单元格（比如Z1，单独存储本次导入的数据源文件名）

在粘贴数据的代码ActiveSheet.Paste之后，新增一行：

Range("Z1").Value = Wb2Name

场景2：把文件名追加到本次导入所有数据行的最后一列，方便匹配每行数据的来源

粘贴数据后新增如下代码：

' 计算本次粘贴的数据行数
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
' 把文件名写入所有行的指定列（示例为J列，可自行修改列号）
Range(Cells(1, "J"), Cells(lastRow, "J")).Value = Wb2Name

修改后的完整宏代码 #

Sub LazyBill()
' LazyBill Macro
'
' Keyboard Shortcut: Ctrl+d
'
Dim SFC_Ver
Dim WB1 As Workbook, WbName As String
Dim WB2 As Workbook, Wb2Name As String
Dim Filename As Variant ' 补充变量声明避免报错
'Dim Ksearch As Range
Dim Kvalue As String
Set WB1 = ActiveWorkbook
    WbName = ActiveWorkbook.Name
'Open .cvs file to input
'MsgBox ("Once you click OK a File Open window will appear. Browse to the .cvs file that you wish to add to the trend data and click open")
    Filename = False
    Filename = Application.GetOpenFilename
        If Filename = False Then
            CloseMaster
        End If
        
Dim sStarDir As String
sStarDir = CurDir
    ChDir "\\sw\data\****"
Application.DefaultFilePath = sStarDir

    Workbooks.Open Filename:= _
    Filename
            Set WB2 = ActiveWorkbook
                Wb2Name = ActiveWorkbook.Name
    Cells.Select
    Selection.Copy
    Windows(WbName).Activate
    Sheets("DATA").Select
'Paste data into trend data file
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = 0

' ---------------新增逻辑开始---------------
' 此处可替换为你需要的写入规则，以下是场景1的示例
Range("Z1").Value = Wb2Name
' ---------------新增逻辑结束---------------

'Close Trend data .cvs file
    Windows(Wb2Name).Activate
            ActiveWorkbook.Close False

End Sub

补充说明 #

• 如果需要获取带完整存储路径的文件名，把Wb2Name = ActiveWorkbook.Name替换为Wb2Name = ActiveWorkbook.FullName即可
• 写入的单元格位置可根据实际需求调整，修改Range参数内的单元格坐标即可

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