excel 如何在宏的顶部定义变量的值并在宏中使用它

qv7cva1a 于 2023-10-22 发布在其他

关注(0)|答案(1)|浏览(89)

我想在宏的顶部键入一个“文件名”，并在宏中使用此“名称”。（我做了一些Access数据库的工作，所以我在这里使用的是JavaScript代码。

// current macro begin
Sub TR_Weight_031023()
'
' TR_Weight_tracker_SAVE_111422 Macro

Application.DisplayAlerts = False
    ChDir "D:\Dropbox\G_emiguy_052921\__Twix and Rolo"
    ActiveWorkbook.SaveAs Filename:= _
        "D:\Dropbox\G_emiguy_052921\__Twix and Rolo\TR - weight after 031023.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "D:\Dropbox\G_emiguy_052921\__Twix and Rolo\TR - weight after 031023.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    ActiveWorkbook.SaveAs Filename:= _
        "D:\Dropbox\G_emiguy_052921\__Twix and Rolo\TR - weight after 031023.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Range("O5:P6").Select
    
Application.DisplayAlerts = True
End Sub
//current macro end

我的思维过程
1.定义一个文本字符串（对吗？）作为宏顶部的“文件名”。目前的“文件名”是“TR - weight after 031023”--但是--我在宏中输入了这个。
1.在“file name”字符串中键入一个新名称（或其他类型？text？？），并使用此新名称代替当前的“TR -031023后的重量”
这样做将允许我选择任何名称（例如，“TR - weight sent to Jim”），文件将是新名称。如果我把这个宏代码/宏变长，我只需要改变“文件名”的值。
我设想

** begin NEW macro with attempt to change
Sub TR_new_weight()

Dim file_name as string
file_name = "TR_new_file_name"

Application.DisplayAlerts = False
    ChDir "D:\Dropbox\G_emiguy_052921\__Twix and Rolo"
    ActiveWorkbook.SaveAs Filename:= _
        "D:\Dropbox\G_emiguy_052921\__Twix and Rolo\"&'TR_new_file_name'&".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "D:\Dropbox\G_emiguy_052921\__Twix and Rolo\"&'TR_new_file_name'&".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    ActiveWorkbook.SaveAs Filename:= _
        "D:\Dropbox\G_emiguy_052921\__Twix and Rolo\"&'TR_new_file_name'&".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Range("O5:P6").Select
    
Application.DisplayAlerts = True
End Sub

** end NEW macro with attempt to change

excel

来源：https://stackoverflow.com/questions/75714921/how-to-define-a-variables-value-at-the-top-of-a-macro-and-use-it-in-the-macro

1条答案

按热度按时间

plicqrtu1#

使用输入框输入新名称

Option Explicit

Sub TR_new_weight()
    
    Const FOLDER = "D:\Dropbox\G_emiguy_052921\__Twix and Rolo\" ' include \ at end
    Const DEFAULTNAME = "TR_new_file_name"
    
    ' enter filename
    Dim file_name
    file_name = InputBox("Enter File Name: ", "Folder " & FOLDER, DEFAULTNAME)
    If file_name = "" Then Exit Sub
    file_name = FOLDER & file_name
    
    ' save
    Application.DisplayAlerts = False
    With ActiveWorkbook
        .SaveAs Filename:=file_name & ".xlsx", FileFormat:=xlOpenXMLWorkbook, _
            CreateBackup:=False
        .ExportAsFixedFormat Filename:=file_name & ".pdf", Type:=xlTypePDF, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
        .SaveAs Filename:=file_name & ".xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        Range("O5:P6").Select
    End With
    Application.DisplayAlerts = True
    MsgBox "Files created", vbInformation
End Sub

赞(0）回复(0）举报 2023-10-22

我来回答

excel 如何在宏的顶部定义变量的值并在宏中使用它

1条答案

相关问题

热门标签

最新问答