This line:
sPath = "C:\Documents and Settings\acb\Desktop\My Team\CHR & MyName_"

wants to be:
sPath = "C:\Documents and Settings\acb\Desktop\My Team\CHR " & MyName & "_"

Maybe this will get you closer:

Option Explicit
Sub Save_LineAd()

Dim Response As Long
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim myName As String
Dim ans As Long
Dim myPath As String
Dim TestStr As String

'so you don't have to hardcode the path to desktop
myPath = CreateObject("WScript.Shell").SpecialFolders("Desk Top")

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
myName = Worksheets("SHEET1").Range("A2").Value
sPath = myPath & "\My Team\CHR " & myName & "_"
sFilename _
= Format(Worksheets("SHEET1").Range("A1").Value, "mmddyy") & ".xls"
ans = MsgBox("Save File As " & sPath & sFilename, Buttons:=vbYesNo)
If ans = vbYes Then
TestStr = ""
On Error Resume Next
TestStr = Dir(sPath & sFilename)
On Error GoTo 0
If TestStr < "" Then
'file already exists
Response = MsgBox(Prompt:="Overwrite existing file?", _
Buttons:=vbYesNo)
If Response = vbNo Then
MsgBox "Try later"
Else
'hide any prompt
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sPath & sFilename, _
FileFormat:=xlworkbooknormal
Application.DisplayAlerts = True
ActiveWorkbook.Close savechanges:=False 'it was just saved!
Application.StatusBar = "Application Closing."
Application.Quit
'if you've quit the application, then
'the macro has stopped!
End If
End If
End If
End If

End Sub

Personally, I wouldn't include the application.quit stuff. I wouldn't want this
workbook to interrupt any of my work on other workbooks.

al_ba wrote:

Hello, here is a code that i was able to come up with...this will save the
excel workbook on the path I specified that changes as the date changes, my
problem...

1. It saves the workbook with name.. CHR &MyName_080708
CHR is constant it should be part of the name; MyName should equal to
whatever is there in SHEET1 A2...say SHEET1 A2 is "Internet", and
SHEET1 A1 is "080808"...then I want the filename to be...CHR Internet_080808

2. This code works well if the user choose "yes" all throughout, but after
the "Save As" question, if user choose, "No" or "Cancel" or click anything
except "yes" it is giving an error.
I want the user to be able to choose either "yes", "no" or "cancel" and
not get error.

Please help with these two issues. Thanks!

Sub Save_LineAd()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim ans

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
MyName = Format(Worksheets("SHEET1").Range("A2").Value)
sPath = "C:\Documents and Settings\acb\Desktop\My Team\CHR & MyName_"
sFilename = Format(Worksheets("SHEET1").Range("A1").Value, "mmddyy")
ans = MsgBox("Save File As " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs sPath & sFilename
ActiveWorkbook.Close savechanges:=True
Application.Quit
Application.StatusBar = "Application Closing."
End If
Else
ActiveWorkbook.Activate
End If

End Sub

"Dave Peterson" wrote:

I don't use sharepoint, but you could record a macro when you saved a copy to a
shared folder (on a network drive???) to see the code.

al_ba wrote:

Hello,

I am looking for a code to save a workbook or worksheet into a shared drive
or share point?

your help is much appreciated. Thanks in advance.

--

Dave Peterson


--

Dave Peterson