以工作簿中的文本作为名称保存工作簿 [英] Saving workbook with text in cell as the name

问题描述

这是我想出的.我收到一个编译错误:

This is what I have come up with. I get a compile error:

需要对象.

Dim strName As String
Set wbk = "C:\Users\I53014\Desktop\Import_Creator.xlsm"
strName = wbk.Sheet1.Range("B2")
ActiveWorkbook.SaveAs strName

我需要获取"Import_Creator.xlsm" sheet1单元格b2中的文本，并用它来保存另一个工作簿作为该名称.

I need to take the text that is in "Import_Creator.xlsm" sheet1 cell b2 and use it to save another workbook as that name.

推荐答案

假设wbk是Workbook对象，

Assuming wbk is a Workbook object,

Set wbk = Workbooks.Open("C:\Users\I53014\Desktop\Import_Creator.xlsm")

当前，您正在尝试将此对象设置为字符串.

currently you are attempting to set this object to a string.

这本书也必须打开(即使是暂时打开)，因为 Workbooks 集合仅指打开的工作簿.

The book also has to be opened (even if temporarily) as the Workbooks collection only refers to open workbooks.

如果可能，在另一个工作簿中有一个引用B2的链接公式，这样可以省去打开和关闭B2的麻烦.

If possible, have a linked formula that refers to B2 in the other workbook, which will save you having to open and close it.

已添加:如果工作簿已打开，则可以使用

Added If the workbook is open then you could use

Set wbk = Workbooks("Import_Creator.xlsm")

如果本书包含当前正在运行的代码 ，则:

If this book contains the code that is currently running then:

Set wbk = ThisWorkbook

只是出于兴趣(对于任何阅读者而言)，都可以从工作簿中获取值，而无需打开和关闭它:

Just out of interest (for anyone reading) it is possible to get a value from a workbook without having to open and close it:

Range("A1").Formula = "='F:\Documents and Settings\student\My Documents\[AndysData7.xlsx]Staff List'!$D$6"
MsgBox Range("A1").Value
Range("A1").Clear

甚至可以在不使用单元格输入公式的情况下获得此值:

It is even possible to get this value without using a cell to enter the formula:

Debug.Print ExecuteExcel4Macro("'F:\Documents and Settings\student\My Documents\[AndysData7.xlsx]Staff List'!R6C4")

(感谢我的同事汉斯)

这篇关于以工作簿中的文本作为名称保存工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！