Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a workbook
I am using Excel 2003. I have a workbook named Main.xls I have a
worksheet in Main.xls named Ticket Cell j8 of Ticket contains a ticket number such as NT0104, Cell b8 in ticket contains a co. name such as Exxon, and cell b200 in ticket contains a dir path such as c:\2009\Feb\ . I want to save the workbook main.xls to the new name (in this case NT0104Exxon.xls) to the specified dir path (in this case c:\2009\Feb) and then I want to keep the original workbook, Main.xls open. When I run the following code, it saves NT0104Exxon.xls to My documents instead of to c:\2009\Feb and then it closes the workbook. I would appreciate any help on what I am doing wrong. Here's the code: rem sitting up variable that will determine where the files will be saved. Dim strappend As String Dim strpath As String Dim str3 As String Sheets("ticket").Select strappend = ActiveSheet.Range("j8").Value strpath = ActiveSheet.Range("b200").Value str3 = ActiveSheet.Range("c8").Value Rem Setting fsavename to directory and file fsavename = strpth & strappend & str3 & ".xls" Rem Saving the file fsavename to the designated directory ThisWorkbook.SaveAs Filename:=fsavename Rem this keeps the main workbook open ActiveWorkbook.Close False end sub Thanks in advance for the help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a workbook
try this code
oldname = ThisWorkbook.FullName Dim strappend As String Dim strpath As String Dim str3 As String With ThisWorkbook.Sheets("ticket") oldname = ThisWorkbook.FullName strappend = .Range("j8").Value strpath = .Range("b200").Value str3 = .Range("c8").Value Rem Setting fsavename to directory and file fsavename = strpth & strappend & str3 & ".xls" Rem Saving the file fsavename to the designated directory ThisWorkbook.SaveAs Filename:=fsavename Rem this keeps the main workbook open Set Newbk = ActiveWorkbook Set Oldbk = Workbooks.Open(Filename:=oldname) Newbk.Close savechanges:=False End With "bigjim" wrote: I am using Excel 2003. I have a workbook named Main.xls I have a worksheet in Main.xls named Ticket Cell j8 of Ticket contains a ticket number such as NT0104, Cell b8 in ticket contains a co. name such as Exxon, and cell b200 in ticket contains a dir path such as c:\2009\Feb\ . I want to save the workbook main.xls to the new name (in this case NT0104Exxon.xls) to the specified dir path (in this case c:\2009\Feb) and then I want to keep the original workbook, Main.xls open. When I run the following code, it saves NT0104Exxon.xls to My documents instead of to c:\2009\Feb and then it closes the workbook. I would appreciate any help on what I am doing wrong. Here's the code: rem sitting up variable that will determine where the files will be saved. Dim strappend As String Dim strpath As String Dim str3 As String Sheets("ticket").Select strappend = ActiveSheet.Range("j8").Value strpath = ActiveSheet.Range("b200").Value str3 = ActiveSheet.Range("c8").Value Rem Setting fsavename to directory and file fsavename = strpth & strappend & str3 & ".xls" Rem Saving the file fsavename to the designated directory ThisWorkbook.SaveAs Filename:=fsavename Rem this keeps the main workbook open ActiveWorkbook.Close False end sub Thanks in advance for the help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a workbook
Thanks a bunch. That fixed the problem of the workbook staying open real
nice. The only problem is, it still saved it to My documents instead of to c:\2009\Feb\. Do you have any idea what might be causing that? "Joel" wrote: try this code oldname = ThisWorkbook.FullName Dim strappend As String Dim strpath As String Dim str3 As String With ThisWorkbook.Sheets("ticket") oldname = ThisWorkbook.FullName strappend = .Range("j8").Value strpath = .Range("b200").Value str3 = .Range("c8").Value Rem Setting fsavename to directory and file fsavename = strpth & strappend & str3 & ".xls" Rem Saving the file fsavename to the designated directory ThisWorkbook.SaveAs Filename:=fsavename Rem this keeps the main workbook open Set Newbk = ActiveWorkbook Set Oldbk = Workbooks.Open(Filename:=oldname) Newbk.Close savechanges:=False End With "bigjim" wrote: I am using Excel 2003. I have a workbook named Main.xls I have a worksheet in Main.xls named Ticket Cell j8 of Ticket contains a ticket number such as NT0104, Cell b8 in ticket contains a co. name such as Exxon, and cell b200 in ticket contains a dir path such as c:\2009\Feb\ . I want to save the workbook main.xls to the new name (in this case NT0104Exxon.xls) to the specified dir path (in this case c:\2009\Feb) and then I want to keep the original workbook, Main.xls open. When I run the following code, it saves NT0104Exxon.xls to My documents instead of to c:\2009\Feb and then it closes the workbook. I would appreciate any help on what I am doing wrong. Here's the code: rem sitting up variable that will determine where the files will be saved. Dim strappend As String Dim strpath As String Dim str3 As String Sheets("ticket").Select strappend = ActiveSheet.Range("j8").Value strpath = ActiveSheet.Range("b200").Value str3 = ActiveSheet.Range("c8").Value Rem Setting fsavename to directory and file fsavename = strpth & strappend & str3 & ".xls" Rem Saving the file fsavename to the designated directory ThisWorkbook.SaveAs Filename:=fsavename Rem this keeps the main workbook open ActiveWorkbook.Close False end sub Thanks in advance for the help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a workbook
You've got to watch your typing.
Do you mean C8 or B8 for the cell with the company name? You used strPath to get the path, but then used strPth in the concatenated string. There's a .savecopyas that allows you to save a copy of the current file as a new name. Option Explicit Sub testme() Dim strappend As String Dim strpath As String Dim str3 As String Dim fSaveName As String With Worksheets("ticket") strappend = .Range("j8").Value 'nt0104 strpath = .Range("b200").Value 'c:\20009\feb 'b8 or c8???? str3 = .Range("c8").Value 'Exxon End With 'setting fsavename to directory and file fSaveName = strpath & strappend & str3 & ".xls" 'Saving the file fsavename to the designated directory ThisWorkbook.SaveCopyAs Filename:=fSaveName End Sub (Compiled, but untested) bigjim wrote: I am using Excel 2003. I have a workbook named Main.xls I have a worksheet in Main.xls named Ticket Cell j8 of Ticket contains a ticket number such as NT0104, Cell b8 in ticket contains a co. name such as Exxon, and cell b200 in ticket contains a dir path such as c:\2009\Feb\ . I want to save the workbook main.xls to the new name (in this case NT0104Exxon.xls) to the specified dir path (in this case c:\2009\Feb) and then I want to keep the original workbook, Main.xls open. When I run the following code, it saves NT0104Exxon.xls to My documents instead of to c:\2009\Feb and then it closes the workbook. I would appreciate any help on what I am doing wrong. Here's the code: rem sitting up variable that will determine where the files will be saved. Dim strappend As String Dim strpath As String Dim str3 As String Sheets("ticket").Select strappend = ActiveSheet.Range("j8").Value strpath = ActiveSheet.Range("b200").Value str3 = ActiveSheet.Range("c8").Value Rem Setting fsavename to directory and file fsavename = strpth & strappend & str3 & ".xls" Rem Saving the file fsavename to the designated directory ThisWorkbook.SaveAs Filename:=fsavename Rem this keeps the main workbook open ActiveWorkbook.Close False end sub Thanks in advance for the help -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a workbook
Thank you, I feel like an idiot. I worked that for hours and didn't catch
the typo. Thanks for finding it and for the advise. Jim Ford "Dave Peterson" wrote: You've got to watch your typing. Do you mean C8 or B8 for the cell with the company name? You used strPath to get the path, but then used strPth in the concatenated string. There's a .savecopyas that allows you to save a copy of the current file as a new name. Option Explicit Sub testme() Dim strappend As String Dim strpath As String Dim str3 As String Dim fSaveName As String With Worksheets("ticket") strappend = .Range("j8").Value 'nt0104 strpath = .Range("b200").Value 'c:\20009\feb 'b8 or c8???? str3 = .Range("c8").Value 'Exxon End With 'setting fsavename to directory and file fSaveName = strpath & strappend & str3 & ".xls" 'Saving the file fsavename to the designated directory ThisWorkbook.SaveCopyAs Filename:=fSaveName End Sub (Compiled, but untested) bigjim wrote: I am using Excel 2003. I have a workbook named Main.xls I have a worksheet in Main.xls named Ticket Cell j8 of Ticket contains a ticket number such as NT0104, Cell b8 in ticket contains a co. name such as Exxon, and cell b200 in ticket contains a dir path such as c:\2009\Feb\ . I want to save the workbook main.xls to the new name (in this case NT0104Exxon.xls) to the specified dir path (in this case c:\2009\Feb) and then I want to keep the original workbook, Main.xls open. When I run the following code, it saves NT0104Exxon.xls to My documents instead of to c:\2009\Feb and then it closes the workbook. I would appreciate any help on what I am doing wrong. Here's the code: rem sitting up variable that will determine where the files will be saved. Dim strappend As String Dim strpath As String Dim str3 As String Sheets("ticket").Select strappend = ActiveSheet.Range("j8").Value strpath = ActiveSheet.Range("b200").Value str3 = ActiveSheet.Range("c8").Value Rem Setting fsavename to directory and file fsavename = strpth & strappend & str3 & ".xls" Rem Saving the file fsavename to the designated directory ThisWorkbook.SaveAs Filename:=fsavename Rem this keeps the main workbook open ActiveWorkbook.Close False end sub Thanks in advance for the help -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a workbook
Addin
Option Explicit to the top of each module may make it seem like more work, but it would have saved a little time for you. bigjim wrote: Thank you, I feel like an idiot. I worked that for hours and didn't catch the typo. Thanks for finding it and for the advise. Jim Ford "Dave Peterson" wrote: You've got to watch your typing. Do you mean C8 or B8 for the cell with the company name? You used strPath to get the path, but then used strPth in the concatenated string. There's a .savecopyas that allows you to save a copy of the current file as a new name. Option Explicit Sub testme() Dim strappend As String Dim strpath As String Dim str3 As String Dim fSaveName As String With Worksheets("ticket") strappend = .Range("j8").Value 'nt0104 strpath = .Range("b200").Value 'c:\20009\feb 'b8 or c8???? str3 = .Range("c8").Value 'Exxon End With 'setting fsavename to directory and file fSaveName = strpath & strappend & str3 & ".xls" 'Saving the file fsavename to the designated directory ThisWorkbook.SaveCopyAs Filename:=fSaveName End Sub (Compiled, but untested) bigjim wrote: I am using Excel 2003. I have a workbook named Main.xls I have a worksheet in Main.xls named Ticket Cell j8 of Ticket contains a ticket number such as NT0104, Cell b8 in ticket contains a co. name such as Exxon, and cell b200 in ticket contains a dir path such as c:\2009\Feb\ . I want to save the workbook main.xls to the new name (in this case NT0104Exxon.xls) to the specified dir path (in this case c:\2009\Feb) and then I want to keep the original workbook, Main.xls open. When I run the following code, it saves NT0104Exxon.xls to My documents instead of to c:\2009\Feb and then it closes the workbook. I would appreciate any help on what I am doing wrong. Here's the code: rem sitting up variable that will determine where the files will be saved. Dim strappend As String Dim strpath As String Dim str3 As String Sheets("ticket").Select strappend = ActiveSheet.Range("j8").Value strpath = ActiveSheet.Range("b200").Value str3 = ActiveSheet.Range("c8").Value Rem Setting fsavename to directory and file fsavename = strpth & strappend & str3 & ".xls" Rem Saving the file fsavename to the designated directory ThisWorkbook.SaveAs Filename:=fsavename Rem this keeps the main workbook open ActiveWorkbook.Close False end sub Thanks in advance for the help -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a workbook
AddinG (with a G!).
Dave Peterson wrote: Addin Option Explicit to the top of each module may make it seem like more work, but it would have saved a little time for you. bigjim wrote: Thank you, I feel like an idiot. I worked that for hours and didn't catch the typo. Thanks for finding it and for the advise. Jim Ford "Dave Peterson" wrote: You've got to watch your typing. Do you mean C8 or B8 for the cell with the company name? You used strPath to get the path, but then used strPth in the concatenated string. There's a .savecopyas that allows you to save a copy of the current file as a new name. Option Explicit Sub testme() Dim strappend As String Dim strpath As String Dim str3 As String Dim fSaveName As String With Worksheets("ticket") strappend = .Range("j8").Value 'nt0104 strpath = .Range("b200").Value 'c:\20009\feb 'b8 or c8???? str3 = .Range("c8").Value 'Exxon End With 'setting fsavename to directory and file fSaveName = strpath & strappend & str3 & ".xls" 'Saving the file fsavename to the designated directory ThisWorkbook.SaveCopyAs Filename:=fSaveName End Sub (Compiled, but untested) bigjim wrote: I am using Excel 2003. I have a workbook named Main.xls I have a worksheet in Main.xls named Ticket Cell j8 of Ticket contains a ticket number such as NT0104, Cell b8 in ticket contains a co. name such as Exxon, and cell b200 in ticket contains a dir path such as c:\2009\Feb\ . I want to save the workbook main.xls to the new name (in this case NT0104Exxon.xls) to the specified dir path (in this case c:\2009\Feb) and then I want to keep the original workbook, Main.xls open. When I run the following code, it saves NT0104Exxon.xls to My documents instead of to c:\2009\Feb and then it closes the workbook. I would appreciate any help on what I am doing wrong. Here's the code: rem sitting up variable that will determine where the files will be saved. Dim strappend As String Dim strpath As String Dim str3 As String Sheets("ticket").Select strappend = ActiveSheet.Range("j8").Value strpath = ActiveSheet.Range("b200").Value str3 = ActiveSheet.Range("c8").Value Rem Setting fsavename to directory and file fsavename = strpth & strappend & str3 & ".xls" Rem Saving the file fsavename to the designated directory ThisWorkbook.SaveAs Filename:=fsavename Rem this keeps the main workbook open ActiveWorkbook.Close False end sub Thanks in advance for the help -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving 3 worksheets in a workbook to a separte workbook | Excel Programming | |||
Color Changes When Saving 2007 Workbook as 97 - 2003 Workbook | Excel Discussion (Misc queries) | |||
Saving Workbook | Excel Discussion (Misc queries) | |||
Saving a sheet in a workbook as .csv but not changing workbook name | Excel Programming | |||
Saving a Workbook: Forcing User to Rename before Saving | Excel Programming |