使用Inputbox VBA分配变量工作簿名称 [英] Assigning variable workbook name with Inputbox VBA

问题描述

我希望将输入框字符串分配给工作簿名称，以便处理从一张纸到另一张纸的数据.例如，当我生成报告时，会为其分配一个临时文件名"tmp1"，"tmp2"等.我想使用另一个excel工作表作为程序，该程序将运行宏以进行排序，复制等操作.生成的文件，只需输入临时文件的名称"tmp1"等.

I am looking to assign an input box string to a workbook name in order to manipulate data from one sheet to the next. For example, when I generate a report it is assigned a temporary file name "tmp1" "tmp2" etc. I would like to use another excel sheet as a program which will run a macro in order to sort, copy, etc. the newly generated file, with only having to enter the name of the temporary file "tmp1" etc.

我能够运行所需的宏，并且熟悉"With"语句，它将使我能够执行此操作.我的问题是当我运行以下代码时:

I am able to run the macro that I need and am familiar with the 'With' statements that will allow me to do so. my issue is when I run the following code:

Sub MyMacro()
Dim wb As Workbook
Dim X As String
X = InputBox("Set work book name")
Set wb = Workbooks(X)

With Workbooks(x)
.'code is entered here'
End with
End sub

我遇到了对象错误.看来我不正确地引用了新文件.任何帮助将不胜感激

I run into an object error. It seems I am incorrectly referencing the new file. Any help would be appreciated

谢谢

推荐答案

如果您尝试创建一个新的临时工作簿并为其分配文件名，请尝试:

If you are attempting to create a new temporary workbook and assign a filename to it then try:

Sub MyMacro()

    Dim X As String
    X = InputBox("Set work book name")
    Workbooks.Add

    ActiveWorkbook.SaveAs Filename:="C:\Users\garys\Desktop\" & X & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    With ActiveWorkbook
        'code is entered here'
    End With
End Sub

(当然，您将使用自己的文件夹名称和文件类型)

EDIT#1:特别是在您的代码中，请尝试替换:

EDIT#1: specifically in your code, try replacing:

With Workbooks(x)

具有:

With Workbooks(x & ".xlsx")

或

With Workbooks(x & ".xlsm")

EDIT#2:

如果另存为文本，请尝试:

If you are save as text, then try:

ActiveWorkbook.SaveAs Filename:="C:\Users\garys\Desktop\" & x & ".txt", FileFormat:=xlText, CreateBackup:=False

并引用如下:

With Workbooks(x & ".txt")

这篇关于使用Inputbox VBA分配变量工作簿名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！