Saving a workbook

[bigjim]

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

 

[Joel]

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]

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?

 

[Dave Peterson]

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]

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]

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.

 

[Dave Peterson]

AddinG (with a G!).

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.