SaveAs file lost

A

Aaron

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??
 
G

Gary L Brown

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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top