需要VBA代码来保存工作簿与单元格中的文本 [英] Need VBA code for saving workbook with text in cell

问题描述

我已经搜索近和远的答案，这是我想出来的，但我不断收到一个编译错误：对象需要。所以这里是我得到的：

  Dim strName As String 
设置wbk =C：\Users\ I53014\Desktop\Import_Creator.xlsm
 
 
 strName = wbk.Sheet1.Range（B2）
 
 ActiveWorkbook.SaveAs strName 
  

我需要使用Import_Creator.xlsmsheet1 cell b2中的文本，并使用它将另一个工作簿保存为那个名字？

解决方案

假设wbk是一个Workbook对象，

 设置wbk = Workbooks.Open（C：\Users\I53014\Desktop\Import_Creator.xlsm）
  

目前您正在尝试将此对象设置为字符串。

该书也必须打开如果暂时的话）作为工作簿集合仅指开放的工作簿。

如果可能，请参考一个链接的公式到另一个工作簿中的B2，这将节省您不得不打开和关闭它。

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

 设置wbk =工作簿（Import_Creator.xlsm）
  

如果本书包含当前正在运行的代码 / em>然后：

 设置wbk = ThisWorkbook 
  

只是不感兴趣（对于任何人阅读），可以从工作簿中获取值，而无需打开和关闭它：

$ b $公式=='F：\Documents and Settings\student\My Documents\ [AndysData7.xlsx]员工清单'！ $ D $ 6
MsgBox范围（A1）。值
范围（A1）。清除

甚至可以获取此值而不使用单元格输入公式：

 调试.Print ExecuteExcel4Macro（'F：\Documents and Settings\student\My Documents\ [AndysData7.xlsx] Staff List'！R6C4）
  

（感谢一个col我的联盟，汉斯）

I have searched near and far for an answer to this and this is what I have come up with, but I keep getting a compile error: object required. So here is what I got:

Dim strName As String
Set wbk = "C:\Users\I53014\Desktop\Import_Creator.xlsm"


strName = wbk.Sheet1.Range("B2")

ActiveWorkbook.SaveAs strName

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?

解决方案

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.

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

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")

(thanks to a colleague of mine, Hans)

这篇关于需要VBA代码来保存工作簿与单元格中的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！