Macro to Save As to a specific location
 

I have a Macro that I want to save the file using the date that's listed in a
specific cell, however I want the file to be placed in a specific folder.
Below is the code I'm using, however It does not saving it in
"T:\MBRProjects\Reports\Weekly Hour Forms\" it saves the file to the last
folder I was in. How do I correct this?

Sub FridayUploadPrep()
'
' FridayUploadPrep Macro
' Macro recorded 9/28/2006 by Charlene Parker
'

'
Sheets("Friday").Select

Dim wb As Workbook
Dim ws As Worksheet
Dim varVal As Variant
Dim strFileName As String
Dim strPath As String

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Friday")
varVal = ws.Range("N1").Value
strPath = "T:\MBRProjects\Reports\Weekly Hour Forms\"

If IsDate(varVal) Then
strFileName = "NPBR " & Format(CStr(varVal), "mm-dd-yyyy") & ".xls"
Else
strFileName = "NPBR " & Format(CStr(Date), "mm-dd-yyyy") & "saved.xls"
End If

ActiveWorkbook.SaveAs Filename:=strPahtName & strFileName

Set wb = Nothing
Set ws = Nothing

End Sub

Macro to Save As to a specific location
 

strPahtName
is a typo.

Check your spelling of your variables.

If you're lazy (like me), you'll want to add:

Option Explicit
to the top of the module. Then VBA won't even run your code as long as there's
an error like this.

Char4500 wrote:

I have a Macro that I want to save the file using the date that's listed in a
specific cell, however I want the file to be placed in a specific folder.
Below is the code I'm using, however It does not saving it in
"T:\MBRProjects\Reports\Weekly Hour Forms\" it saves the file to the last
folder I was in. How do I correct this?

Sub FridayUploadPrep()
'
' FridayUploadPrep Macro
' Macro recorded 9/28/2006 by Charlene Parker
'

'
Sheets("Friday").Select

Dim wb As Workbook
Dim ws As Worksheet
Dim varVal As Variant
Dim strFileName As String
Dim strPath As String

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Friday")
varVal = ws.Range("N1").Value
strPath = "T:\MBRProjects\Reports\Weekly Hour Forms\"

If IsDate(varVal) Then
strFileName = "NPBR " & Format(CStr(varVal), "mm-dd-yyyy") & ".xls"
Else
strFileName = "NPBR " & Format(CStr(Date), "mm-dd-yyyy") & "saved.xls"
End If

ActiveWorkbook.SaveAs Filename:=strPahtName & strFileName

Set wb = Nothing
Set ws = Nothing

End Sub

--

Dave Peterson

Macro to Save As to a specific location
 

Charlene, from what I can tell you have a typo. strPath does not =
strPathName. Just a note, to prevent some errors like this, you could require
variable declaration. which is found under Tools... Options... Editor Tab
"Require Variable Declaration". This will place the statement "Option
Explicit" at the top of every new module, which will generate a variable not
defined error when ever your code is compiled and you don't have all your
variables named the same.
--
Charles Chickering

"A good example is twice the value of good advice."


"Char4500" wrote:

I have a Macro that I want to save the file using the date that's listed in a
specific cell, however I want the file to be placed in a specific folder.
Below is the code I'm using, however It does not saving it in
"T:\MBRProjects\Reports\Weekly Hour Forms\" it saves the file to the last
folder I was in. How do I correct this?

Sub FridayUploadPrep()
'
' FridayUploadPrep Macro
' Macro recorded 9/28/2006 by Charlene Parker
'

'
Sheets("Friday").Select

Dim wb As Workbook
Dim ws As Worksheet
Dim varVal As Variant
Dim strFileName As String
Dim strPath As String

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Friday")
varVal = ws.Range("N1").Value
strPath = "T:\MBRProjects\Reports\Weekly Hour Forms\"

If IsDate(varVal) Then
strFileName = "NPBR " & Format(CStr(varVal), "mm-dd-yyyy") & ".xls"
Else
strFileName = "NPBR " & Format(CStr(Date), "mm-dd-yyyy") & "saved.xls"
End If

ActiveWorkbook.SaveAs Filename:=strPahtName & strFileName

Set wb = Nothing
Set ws = Nothing

End Sub