Using a Worksheet name from a Cell

vectorges · Mar 12th, 2002, 11:07 AM

I need to open a succession of workbooks. I would like to store the workbook names in a column, use the data value in the Workbooks.Open Filename: command. How do I convert the value of a cell (i.e. A19) to use it in the open command??

NateO · Mar 12th, 2002, 11:29 AM

You could use something like this:

Sub openit()

fname = WorksheetFunction.Substitute(Range("a19").Value, ".xls", "")
Workbooks.Open Filename:= _
"c:temp" & fname & ".xls"
End Sub

Just need to get the file path right...You could put this in the cell as well and get rid of "c:temp" &

Double quotes in this post should really be single quotes. Hope this helps. Cheers,

Nate

[ This Message was edited by: NateO on 2002-03-12 10:30 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 11:07 AM	#1
vectorges New Member Join Date: Mar 2002 Location: George Posts: 4	I need to open a succession of workbooks. I would like to store the workbook names in a column, use the data value in the Workbooks.Open Filename: command. How do I convert the value of a cell (i.e. A19) to use it in the open command??

Mar 12th, 2002, 11:29 AM	#2
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	You could use something like this: Sub openit() fname = WorksheetFunction.Substitute(Range("a19").Value, ".xls", "") Workbooks.Open Filename:= _ "c:temp" & fname & ".xls" End Sub Just need to get the file path right...You could put this in the cell as well and get rid of "c:temp" & Double quotes in this post should really be single quotes. Hope this helps. Cheers, Nate [ This Message was edited by: NateO on 2002-03-12 10:30 ]