Prev: Run-time error '380' Invalid property value.
Next: Custom button image not loading on custom ribbon
From: ram on 16 Apr 2010 11:27
HI All,

I would like help with the following:

I would like to create a folder with the name of the current month less one.
Next, I would like to Save As the active file I'm working on with the name
Audit Report for current month less one. I would like to save this audit
report in the folder I just created above.

The code I'm trying to use is below. I think my problems are with the items
in blue

MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & Format(((Now) - 1), "mmm_yy")

ActiveWorkbook.SaveCopyAs
Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\Folder name from above
\AuditReport_" & Format(Now, "mmm_yy") & ".xls"

Thanks in advance for any help

From: JLatham on 16 Apr 2010 13:08
I assume that the path all the way out to \MonthlyReport\ actually exists -
otherwise you have to create the sub-folders one at a time. Assuming that -
then here's how I'd probably do it

'early in the module
Dim newName As String

newName = Format(DateSerial(Year(Now()), Month(Now()) - 1, 1), "mmm-yy")
MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & newName
and later
ActiveWorkbook.SaveCopyAs _
Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\" & newName & _
Application.PathSeparator & newName & ".xls"


"ram" wrote:

> HI All,
>
> I would like help with the following:
>
> I would like to create a folder with the name of the current month less one.
> Next, I would like to Save As the active file I'm working on with the name
> Audit Report for current month less one. I would like to save this audit
> report in the folder I just created above.
>
> The code I'm trying to use is below. I think my problems are with the items
> in blue
>
> MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & Format(((Now) - 1), "mmm_yy")
>
> ActiveWorkbook.SaveCopyAs
> Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\Folder name from above
> \AuditReport_" & Format(Now, "mmm_yy") & ".xls"
>
> Thanks in advance for any help
>
From: ram on 16 Apr 2010 13:17
Thank you very much this is just what I needed

"JLatham" wrote:

> I assume that the path all the way out to \MonthlyReport\ actually exists -
> otherwise you have to create the sub-folders one at a time. Assuming that -
> then here's how I'd probably do it
>
> 'early in the module
> Dim newName As String
>
> newName = Format(DateSerial(Year(Now()), Month(Now()) - 1, 1), "mmm-yy")
> MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & newName
> and later
> ActiveWorkbook.SaveCopyAs _
> Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\" & newName & _
> Application.PathSeparator & newName & ".xls"
>
>
> "ram" wrote:
>
> > HI All,
> >
> > I would like help with the following:
> >
> > I would like to create a folder with the name of the current month less one.
> > Next, I would like to Save As the active file I'm working on with the name
> > Audit Report for current month less one. I would like to save this audit
> > report in the folder I just created above.
> >
> > The code I'm trying to use is below. I think my problems are with the items
> > in blue
> >
> > MkDir "\\NS-WARQVFS01\Audit\MonthlyReport\" & Format(((Now) - 1), "mmm_yy")
> >
> > ActiveWorkbook.SaveCopyAs
> > Filename:="\\NS-WARQVFS01\Audit\MonthlyReport\Folder name from above
> > \AuditReport_" & Format(Now, "mmm_yy") & ".xls"
> >
> > Thanks in advance for any help
> >
 | 
Pages: 1
Prev: Run-time error '380' Invalid property value.
Next: Custom button image not loading on custom ribbon