Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving open workbook to text string in cell
I am trying to saving a file based on the text name in a designated cell in the workbook. In other words, I am trying to save the open file as the name that is in a cell in that open file's worksheet. Evidently, the code below is not the way to approach it. I get an out of subscript error. Can anyone point me in the right direction? It seems like this shouldn't be such a hard thing, but I am baffled. However, with this code, I get an €śsubscript out of range€ť error. I am running out of ideas. There must be a way to do this! Seems to be problem with variable. Thanks for your help. Windows("fxRM_Update.xls").Activate Dim bk As Workbook, bk1 As Workbook Dim sstr As String Dim path2 As String path2 = ActiveWorkbook.Path Set bk = Workbooks("fxRM_update.xls") sstr = bk.Worksheets("lookup").Range("d39").Value Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS ActiveWorkbook.SaveCopyAs filename:= bk1 'bk1.saveas 'ActiveWorkbook.SaveCopyAs filename:= p & bk1 ' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving open workbook to text string in cell
Hi
This should do it. Windows("fxRM_Update.xls").Activate Dim SaveString As String Dim sstr As String Dim Path2 As String Path2 = ActiveWorkbook.Path sstr = Worksheets("lookup").Range("d39").Value SaveString = Path2 & "\" & sstr & ".xls" ActiveWorkbook.SaveCopyAs Filename:=SaveString Regards, Per "Andyjim" skrev i en meddelelse ... I am trying to saving a file based on the text name in a designated cell in the workbook. In other words, I am trying to save the open file as the name that is in a cell in that open file's worksheet. Evidently, the code below is not the way to approach it. I get an out of subscript error. Can anyone point me in the right direction? It seems like this shouldn't be such a hard thing, but I am baffled. However, with this code, I get an "subscript out of range" error. I am running out of ideas. There must be a way to do this! Seems to be problem with variable. Thanks for your help. Windows("fxRM_Update.xls").Activate Dim bk As Workbook, bk1 As Workbook Dim sstr As String Dim path2 As String path2 = ActiveWorkbook.Path Set bk = Workbooks("fxRM_update.xls") sstr = bk.Worksheets("lookup").Range("d39").Value Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS ActiveWorkbook.SaveCopyAs filename:= bk1 'bk1.saveas 'ActiveWorkbook.SaveCopyAs filename:= p & bk1 ' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving open workbook to text string in cell
hi,
you're missing someting. sstr is not a workbook yet. so i would dump this line.... Set bk1 = Workbooks(sstr) and replace it with.... ActiveWorkbook.SaveCopyAs Filename:=sstr & ".xls" regards FSt1 "Andyjim" wrote: I am trying to saving a file based on the text name in a designated cell in the workbook. In other words, I am trying to save the open file as the name that is in a cell in that open file's worksheet. Evidently, the code below is not the way to approach it. I get an out of subscript error. Can anyone point me in the right direction? It seems like this shouldn't be such a hard thing, but I am baffled. However, with this code, I get an €śsubscript out of range€ť error. I am running out of ideas. There must be a way to do this! Seems to be problem with variable. Thanks for your help. Windows("fxRM_Update.xls").Activate Dim bk As Workbook, bk1 As Workbook Dim sstr As String Dim path2 As String path2 = ActiveWorkbook.Path Set bk = Workbooks("fxRM_update.xls") sstr = bk.Worksheets("lookup").Range("d39").Value Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS ActiveWorkbook.SaveCopyAs filename:= bk1 'bk1.saveas 'ActiveWorkbook.SaveCopyAs filename:= p & bk1 ' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving open workbook to text string in cell
Name the cell where you have the full path and fielname as say rngFilename
then the only line of code you need is: ActiveWorkbook.SaveAs Filename range("rngFileName").value "Andyjim" wrote: I am trying to saving a file based on the text name in a designated cell in the workbook. In other words, I am trying to save the open file as the name that is in a cell in that open file's worksheet. Evidently, the code below is not the way to approach it. I get an out of subscript error. Can anyone point me in the right direction? It seems like this shouldn't be such a hard thing, but I am baffled. However, with this code, I get an €śsubscript out of range€ť error. I am running out of ideas. There must be a way to do this! Seems to be problem with variable. Thanks for your help. Windows("fxRM_Update.xls").Activate Dim bk As Workbook, bk1 As Workbook Dim sstr As String Dim path2 As String path2 = ActiveWorkbook.Path Set bk = Workbooks("fxRM_update.xls") sstr = bk.Worksheets("lookup").Range("d39").Value Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS ActiveWorkbook.SaveCopyAs filename:= bk1 'bk1.saveas 'ActiveWorkbook.SaveCopyAs filename:= p & bk1 ' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving open workbook to text string in cell
Thanks to both of you for such a speedy response! I will try this at the
first opportunity! "DomThePom" wrote: Name the cell where you have the full path and fielname as say rngFilename then the only line of code you need is: ActiveWorkbook.SaveAs Filename range("rngFileName").value "Andyjim" wrote: I am trying to saving a file based on the text name in a designated cell in the workbook. In other words, I am trying to save the open file as the name that is in a cell in that open file's worksheet. Evidently, the code below is not the way to approach it. I get an out of subscript error. Can anyone point me in the right direction? It seems like this shouldn't be such a hard thing, but I am baffled. However, with this code, I get an €śsubscript out of range€ť error. I am running out of ideas. There must be a way to do this! Seems to be problem with variable. Thanks for your help. Windows("fxRM_Update.xls").Activate Dim bk As Workbook, bk1 As Workbook Dim sstr As String Dim path2 As String path2 = ActiveWorkbook.Path Set bk = Workbooks("fxRM_update.xls") sstr = bk.Worksheets("lookup").Range("d39").Value Set bk1 = Workbooks(sstr) THIS IS WHERE SUBSCRIPT ERROR OCCURS ActiveWorkbook.SaveCopyAs filename:= bk1 'bk1.saveas 'ActiveWorkbook.SaveCopyAs filename:= p & bk1 ' ActiveWorkbook.SaveCopyAs filename:=path2 & "\" & bk1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
string assistance to open workbook via macro | Excel Discussion (Misc queries) | |||
Open a specific workbook...find value from other open workbook and then insert cells values in cell next to it. | Excel Programming | |||
keeping original workbook open when saving | Excel Programming | |||
problem saving an open excel workbook | Excel Worksheet Functions | |||
problem with saving a workbook using VBA with the file is open bef | Excel Programming |