SaveAs file lost
 

Hello,

I have to following code which does a save as and saves the file in the
directory listed.

Sub SaveWithDate()
'
Dim wb As Workbook
Dim ws As Worksheet
Dim varVal As Variant
Dim strFileName As String

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("$ Reference")
varVal = ws.Range("C2").Value

If IsDate(varVal) Then
strFileName = "Glue Sched_" & Format(CStr(varVal), "mm-dd-yyyy") &
".xls"
Else
strFileName = "Glue Sched_" & Format(CStr(Date), "mm-dd-yyyy") &
".xls"
End If
ChDir "\\Lan1\Supervisor\Departments\Foam\SCHEDULING \Old Schedules\"
ActiveWorkbook.SaveAs Filename:=strFileName


Set wb = Nothing
Set ws = Nothing

End Sub

The part am not understanding is that when I run my workbook and this macro
from my computer, it works perfect and saves the file in the listed
directory. When a fellow employee ran this, it saved the file to my docs
under his profile. I know he has access to the above directory, so I don't
understand what might be causing that. It's almost like it automatically
changes directories again when it performs the SaveAs. Any ideas??

SaveAs file lost
 

Instead of...
ChDir "\\Lan1\Supervisor\Departments\Foam\SCHEDULING \Old Schedules\"
ActiveWorkbook.SaveAs Filename:=strFileName
try...
ActiveWorkbook.SaveAs Filename:="\\Lan1
\Supervisor\Departments\Foam\SCHEDULING\Old Schedules\"
& strFileName

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Aaron" wrote:

Hello,

I have to following code which does a save as and saves the file in the
directory listed.

Sub SaveWithDate()
'
Dim wb As Workbook
Dim ws As Worksheet
Dim varVal As Variant
Dim strFileName As String

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("$ Reference")
varVal = ws.Range("C2").Value

If IsDate(varVal) Then
strFileName = "Glue Sched_" & Format(CStr(varVal), "mm-dd-yyyy") &
".xls"
Else
strFileName = "Glue Sched_" & Format(CStr(Date), "mm-dd-yyyy") &
".xls"
End If
ChDir "\\Lan1\Supervisor\Departments\Foam\SCHEDULING \Old Schedules\"
ActiveWorkbook.SaveAs Filename:=strFileName


Set wb = Nothing
Set ws = Nothing

End Sub

The part am not understanding is that when I run my workbook and this macro
from my computer, it works perfect and saves the file in the listed
directory. When a fellow employee ran this, it saved the file to my docs
under his profile. I know he has access to the above directory, so I don't
understand what might be causing that. It's almost like it automatically
changes directories again when it performs the SaveAs. Any ideas??