still stuck: get filename from range

[rroach]

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

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>