CODEWITHSUNDEEP
excelvba
Tony Curado
Tony Curado

Reputation: 13

Saving workbook with text in cell as the name

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

object required.

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.

Upvotes: 0

Views: 1551

Answers (1)

Andy G
Andy G

Reputation: 19367

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)

Upvotes: 2

Related Questions