Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Save File to specific location when closing | Excel Discussion (Misc queries) | |||
Auto save Spread sheet as htm to a specific location | Excel Discussion (Misc queries) | |||
Macro - save to current location vs excel default location | Excel Discussion (Misc queries) | |||
Macro uses "Save As" to a specific location | Excel Programming | |||
Save to specific location | Excel Discussion (Misc queries) |