Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
still stuck: get filename from range
I'm new at this. With much help from this forum I've almost finished major timesaving macro for our lab. But stuck on following problem. load a list of files that are in a worksheet and do a for-next loop o them,. Before closing each file I want to save it as an XLS file AND a a text file. I don't understand VBA syntax well enough to know how t 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 I 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 FILENAM do some stuff wbDest.Save WBDEST.SAVEAS FILENAME:=RCELL, FILEFORMAT:=XLTEXT, CREATEBACKUP:=FALSE NOTE: SUB'ING RCELL.VALUE FOR RCELL ABOVE DOESN'T WORK EITHE wbDest.Close savechanges:=False Next End Sub What do I need to change to send the right filename to the SaveA statement? TIA, Ro -- rroac ----------------------------------------------------------------------- rroach's Profile: http://www.excelforum.com/member.php...fo&userid=2109 View this thread: http://www.excelforum.com/showthread.php?threadid=38722 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit search range then loop thru' each cell...a bit stuck... | Excel Programming | |||
Newbie stuck on Passing a Variable into a Range | Excel Programming | |||
Newbie stuck on Passing a Variable into a Range | Excel Programming | |||
Newbie stuck on Passing a Variable into a Range | Excel Programming | |||
Newbie stuck on Passing a Variable into a Range | Excel Programming |