VBA自动写入.tex文件、复制单元格值及运行.tex需求咨询

• 现有Excel用户窗体「Saving」的命令按钮，功能是将工作表保存为CSV文件后打开指定.tex文件
• 当前痛点：需手动在.tex文件中输入CSV文件名，希望通过VBA自动写入该文件名（对应Excel单元格Range("D1")的值）
• 备选方案：弹出消息框允许复制Range("D1")的值，方便手动粘贴到.tex文件
• 理想需求：用VBA直接运行.tex文件生成PDF，但担心额外安装程序会给同事带来负担

VBA代码（命令按钮点击事件） #

Private Sub CmdB_Click()
'save the changes
        Dim CWB As String '.csv filename and location

        Application.DisplayAlerts = False
        CWB = Range("E1") & Range("D1")

        'create CSV file
        Application.DisplayAlerts = False

        ThisWorkbook.Sheets(1).Copy

        ActiveWorkbook.SaveAs fileName:=CWB, FileFormat:=xlCSV, CreateBackup:=True
        ActiveWorkbook.Close
 
        Application.DisplayAlerts = True

        'open TeX file (to write or run?)
        CreateObject("Shell.Application").Open (ThisWorkbook.Path & "\input.tex")

        Unload Saving
End Sub

对应.tex文件内容 #

\documentclass[a4paper,12p]{article}

%Pricelist name to be entered
\newcommand{\pricelist}{241128_1135_CSV.csv} % Range("D1")

%%%% DON'T ALTER %%%%%
\input{input/RunPDF}

1. 自动写入.tex文件（优先推荐） #

修改VBA代码，在生成CSV后，自动读取并修改.tex文件中的文件名，无需手动操作：

Private Sub CmdB_Click()
    Dim CWB As String '.csv filename and location
    Dim texPath As String
    Dim texContent As String
    Dim fso As Object
    Dim ts As Object
    Dim csvFileName As String
    
    Application.DisplayAlerts = False
    CWB = Range("E1") & Range("D1")
    ' 提取纯CSV文件名（不含路径）
    csvFileName = Mid(CWB, InStrRev(CWB, "\") + 1)
    
    ' 生成CSV文件
    ThisWorkbook.Sheets(1).Copy
    ActiveWorkbook.SaveAs fileName:=CWB, FileFormat:=xlCSV, CreateBackup:=True
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    
    ' 读取.tex文件内容
    texPath = ThisWorkbook.Path & "\input.tex"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile(texPath, 1) ' 只读模式打开
    texContent = ts.ReadAll
    ts.Close
    
    ' 替换\pricelist后的文件名
    Dim oldPricelistLine As String
    oldPricelistLine = Split(texContent, "\newcommand{\pricelist}{")(1)
    oldPricelistLine = Split(oldPricelistLine, "}")(0) & "}"
    texContent = Replace(texContent, "\newcommand{\pricelist}{" & oldPricelistLine, _
                        "\newcommand{\pricelist}{" & csvFileName & "}")
    
    ' 将修改后的内容写回.tex文件
    Set ts = fso.OpenTextFile(texPath, 2) ' 覆盖模式写入
    ts.Write texContent
    ts.Close
    
    ' 打开修改后的.tex文件
    CreateObject("Shell.Application").Open texPath
    
    Unload Saving
End Sub

• 说明：通过FileSystemObject读写.tex文件，精准替换\pricelist定义的文件名；若CSV与.tex文件不在同一目录，可调整csvFileName为带完整路径的文件名。

2. 弹出消息框并自动复制文件名 #

如果自动写入方案存在权限或路径问题，可采用此备选方案：

Private Sub CmdB_Click()
    Dim CWB As String '.csv filename and location
    Dim csvFileName As String
    
    Application.DisplayAlerts = False
    CWB = Range("E1") & Range("D1")
    csvFileName = Mid(CWB, InStrRev(CWB, "\") + 1)
    
    ' 生成CSV文件
    ThisWorkbook.Sheets(1).Copy
    ActiveWorkbook.SaveAs fileName:=CWB, FileFormat:=xlCSV, CreateBackup:=True
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    
    ' 将文件名复制到剪贴板
    Dim dataObj As Object
    Set dataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    dataObj.SetText csvFileName
    dataObj.PutInClipboard
    
    ' 弹出提示消息
    MsgBox "CSV文件名已复制到剪贴板：" & vbCrLf & csvFileName, vbInformation, "操作提示"
    
    ' 打开.tex文件
    CreateObject("Shell.Application").Open (ThisWorkbook.Path & "\input.tex")
    
    Unload Saving
End Sub

• 说明：利用DataObject将文件名复制到系统剪贴板，同事打开.tex文件后直接粘贴即可。

3. 可选：通过VBA编译.tex生成PDF #

若同事电脑已安装TeX发行版（如TeX Live、MikTeX），可直接调用命令行编译生成PDF：

Private Sub CmdB_Click()
    Dim CWB As String '.csv filename and location
    Dim texPath As String
    Dim csvFileName As String
    Dim fso As Object
    Dim ts As Object
    Dim texContent As String
    Dim cmd As String
    Dim shellObj As Object
    
    Application.DisplayAlerts = False
    CWB = Range("E1") & Range("D1")
    csvFileName = Mid(CWB, InStrRev(CWB, "\") + 1)
    
    ' 生成CSV文件
    ThisWorkbook.Sheets(1).Copy
    ActiveWorkbook.SaveAs fileName:=CWB, FileFormat:=xlCSV, CreateBackup:=True
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    
    ' 修改.tex文件中的文件名
    texPath = ThisWorkbook.Path & "\input.tex"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile(texPath, 1)
    texContent = ts.ReadAll
    ts.Close
    Dim oldPricelistLine As String
    oldPricelistLine = Split(texContent, "\newcommand{\pricelist}{")(1)
    oldPricelistLine = Split(oldPricelistLine, "}")(0) & "}"
    texContent = Replace(texContent, "\newcommand{\pricelist}{" & oldPricelistLine, _
                        "\newcommand{\pricelist}{" & csvFileName & "}")
    Set ts = fso.OpenTextFile(texPath, 2)
    ts.Write texContent
    ts.Close
    
    ' 调用XeLaTeX编译（可根据实际使用的TeX引擎调整为pdflatex等）
    cmd = "xelatex -interaction=nonstopmode """ & texPath & """"
    Set shellObj = CreateObject("WScript.Shell")
    ' 1表示显示命令窗口，True表示等待编译完成后再执行后续操作
    shellObj.Run cmd, 1, True
    
    ' 打开生成的PDF文件
    CreateObject("Shell.Application").Open Replace(texPath, ".tex", ".pdf")
    
    Unload Saving
End Sub

• 注意：需确保同事电脑的TeX引擎（如xelatex）已加入系统环境变量，可直接通过命令行调用；若文件路径包含空格，必须用双引号包裹路径。

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