Thread: still stuck: get filename from range

Rob,

Do your file names on the sheet have .xls at the end?

Perhaps:

Wbdest.SaveAs Filename:=Substitute(RCell.Value, ".xls", ".txt"), FileFormat:=xlText

HTH,
Bernie
MS Excel MVP


"rroach" <rroach.1s65v7_1121360843.7756@excelforum-nospam.com> wrote in message
news:rroach.1s65v7_1121360843.7756@excelforum-nospam.com...
>
> I'm new at this. With much help from this forum I've almost finished a
> major timesaving macro for our lab. But stuck on following problem. I
> load a list of files that are in a worksheet and do a for-next loop on
> them,. Before closing each file I want to save it as an XLS file AND as
> a text file. I don't understand VBA syntax well enough to know how to
> get the filename for the filesave command.
>
> My code now:
> Sub File_Cut_Metrics()
>
> Dim wbList As Workbook
> Dim wbDest As Workbook
> Dim rcell As Range
>
> Set wbList = Workbooks.Open("G:\newlist2")
> For Each rcell In
> wbList.Sheets(1).Range("A1").CurrentRegion.Columns(1).Cells
> Set wbDest = Workbooks.Open(rcell.Value)
>
> NB: IF I PUT IN \"MSGBOX RCELL\" HERE IT SHOWS THE CORRECT FILENAME
>
> do some stuff
>
> wbDest.Save
>
> WBDEST.SAVEAS FILENAME:=RCELL, FILEFORMAT:=XLTEXT, CREATEBACKUP:=FALSE
> NOTE: SUB'ING RCELL.VALUE FOR RCELL ABOVE DOESN'T WORK EITHER
>
> wbDest.Close savechanges:=False
> Next
>
> End Sub
>
> What do I need to change to send the right filename to the SaveAs
> statement?
>
> TIA,
>
> Rob
>
>
> --
> rroach
> ------------------------------------------------------------------------
> rroach's Profile: http://www.excelforum.com/member.php...o&userid=21093
> View this thread: http://www.excelforum.com/showthread...hreadid=387227
>