Need help with "save as" macro

[MegaDETH]

I get a .xls file sent to me via email everyday that needs to be saved to a certain folder with the days date so I have a macro (taken from this site) that does everything I want BUT I need the filename to be the date I save it 070705.xls

Sorry I am a complete noob at macros as I am just learning Excel so if anyone can tell me the line I need to replace and what it should be I would appreciate it.


edit: also I do not want the xls file to close.



Thanks



Sub indy()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim ans

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
sPath = "g:\indymicro\"
sFilename = Format(Worksheets("Sheet1").Range("A1").Value, "yyyy-mm-dd") "mm-dd-yyyy")
ans = MsgBox("Save File As " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs sPath & sFilename
ActiveWorkbook.Close savechanges:=True
Application.Exit
Application.StatusBar = "Application Closing."
End If
Else
ActiveWorkbook.Activate
End If

End Sub

[Henry]

Mega,

If you only want the filename to be today's date then
sFilename = Date

If you want some text in there
sFilename = "Your text" & Date

Also
ActiveWorkbook.Close savechanges:=True
saves the workbook a second time. Do you need to do this?
ActiveWorkbook.Close savechanges:=False is probably what you need.

Henry


"MegaDETH" <[email protected]> wrote in
message news:[email protected]...
>
> I get a .xls file sent to me via email everyday that needs to be saved
> to a certain folder with the days date so I have a macro (taken from
> this site) that does everything I want BUT I need the filename to be
> the date I save it 070705.xls
>
> Sorry I am a complete noob at macros as I am just learning Excel so if
> anyone can tell me the line I need to replace and what it should be I
> would appreciate it.
>
>
> edit: also I do not want the xls file to close.
>
>
>
> Thanks
>
>
>
> Sub indy()
> Dim Response As String
> Dim msg As String
> Dim Style As String
> Dim sPath As String
> Dim sFilename As String
> Dim ans
>
> msg = "Are you sure you want to Exit the application and Close Excel?"
> Style = vbYesNo + vbInformation + vbDefaultButton2
>
> Response = MsgBox(msg, Style)
> If Response = vbYes Then
> sPath = "g:\indymicro\"
> sFilename = Format(Worksheets("Sheet1").Range("A1").Value,
> "yyyy-mm-dd") "mm-dd-yyyy")
> ans = MsgBox("Save File As " & sFilename)
> If ans = vbOK Then
> ActiveWorkbook.SaveAs sPath & sFilename
> ActiveWorkbook.Close savechanges:=True
> Application.Exit
> Application.StatusBar = "Application Closing."
> End If
> Else
> ActiveWorkbook.Activate
> End If
>
> End Sub
>
>
> --
> MegaDETH
> ------------------------------------------------------------------------
> MegaDETH's Profile:
> http://www.excelforum.com/member.php...o&userid=25004
> View this thread: http://www.excelforum.com/showthread...hreadid=385289
>

[Henry]

Mega,

That should read
sFilename = Date & ".xls"

&

sFilename = "Your text" & Date & ".xls"

Henry


"Henry" <[email protected]> wrote in message
news:[email protected]...
> Mega,
>
> If you only want the filename to be today's date then
> sFilename = Date
>
> If you want some text in there
> sFilename = "Your text" & Date
>
> Also
> ActiveWorkbook.Close savechanges:=True
> saves the workbook a second time. Do you need to do this?
> ActiveWorkbook.Close savechanges:=False is probably what you need.
>
> Henry
>
>
> "MegaDETH" <[email protected]> wrote
> in message news:[email protected]...
>>
>> I get a .xls file sent to me via email everyday that needs to be saved
>> to a certain folder with the days date so I have a macro (taken from
>> this site) that does everything I want BUT I need the filename to be
>> the date I save it 070705.xls
>>
>> Sorry I am a complete noob at macros as I am just learning Excel so if
>> anyone can tell me the line I need to replace and what it should be I
>> would appreciate it.
>>
>>
>> edit: also I do not want the xls file to close.
>>
>>
>>
>> Thanks
>>
>>
>>
>> Sub indy()
>> Dim Response As String
>> Dim msg As String
>> Dim Style As String
>> Dim sPath As String
>> Dim sFilename As String
>> Dim ans
>>
>> msg = "Are you sure you want to Exit the application and Close Excel?"
>> Style = vbYesNo + vbInformation + vbDefaultButton2
>>
>> Response = MsgBox(msg, Style)
>> If Response = vbYes Then
>> sPath = "g:\indymicro\"
>> sFilename = Format(Worksheets("Sheet1").Range("A1").Value,
>> "yyyy-mm-dd") "mm-dd-yyyy")
>> ans = MsgBox("Save File As " & sFilename)
>> If ans = vbOK Then
>> ActiveWorkbook.SaveAs sPath & sFilename
>> ActiveWorkbook.Close savechanges:=True
>> Application.Exit
>> Application.StatusBar = "Application Closing."
>> End If
>> Else
>> ActiveWorkbook.Activate
>> End If
>>
>> End Sub
>>
>>
>> --
>> MegaDETH
>> ------------------------------------------------------------------------
>> MegaDETH's Profile:
>> http://www.excelforum.com/member.php...o&userid=25004
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=385289
>>
>
>

[MegaDETH]

Thank you so much, this is what I needed.

[MegaDETH]

OK I have a minor problem with the saving as the date.

Using sFilename = Date & ".xls"


It tried to save it in this format 7\11\2005.xls. This would be fine but it won't save this way as it thinks it is a folder with the \

Do I have any other options or is it a setting in Excel that has to do with the way the date is saved?



thanks

[Henry]

MegaDeth,

Sorry,
I forgot that the slashes can't be used in a filename.

2 ways around that.

1) Control Panel>Regional and Language Options>Regional
Options>Customize>Date>Date Separator = .
This will change your date format to 7.11.2005 (throughout windows)

2)sFilename = Month(Date) & "." & Day(Date) & "." & Year(Date) & ".xls"

Henry



"MegaDETH" <[email protected]> wrote in
message news:[email protected]...
>
> OK I have a minor problem with the saving as the date.
>
> Using sFilename = Date & ".xls"
>
>
> It tried to save it in this format 7\11\2005.xls. This would be fine
> but it won't save this way as it thinks it is a folder with the \
>
> Do I have any other options or is it a setting in Excel that has to do
> with the way the date is saved?
>
>
>
> thanks
>
>
> --
> MegaDETH
> ------------------------------------------------------------------------
> MegaDETH's Profile:
> http://www.excelforum.com/member.php...o&userid=25004
> View this thread: http://www.excelforum.com/showthread...hreadid=385289
>

[MegaDETH]

That worked !!


Thank you again for your help

MegaDeth,

Sorry,
I forgot that the slashes can't be used in a filename.

2 ways around that.

1) Control Panel>Regional and Language Options>Regional
Options>Customize>Date>Date Separator = .
This will change your date format to 7.11.2005 (throughout windows)

2)sFilename = Month(Date) & "." & Day(Date) & "." & Year(Date) & ".xls"

Henry



"MegaDETH" <[email protected]> wrote in
message news:[email protected]...
>
> OK I have a minor problem with the saving as the date.
>
> Using sFilename = Date & ".xls"
>
>
> It tried to save it in this format 7\11\2005.xls. This would be fine
> but it won't save this way as it thinks it is a folder with the \
>
> Do I have any other options or is it a setting in Excel that has to do
> with the way the date is saved?
>
>
>
> thanks
>
>
> --
> MegaDETH
> ------------------------------------------------------------------------
> MegaDETH's Profile:
> http://www.excelforum.com/member.php...o&userid=25004
> View this thread: http://www.excelforum.com/showthread...hreadid=385289
>

[David McRitchie]

I would suggest you save files that have dates in their filename
that you use for format to name the file so that you have
year first, followed by month then day of month so that
they will sort nicely as filenames regardless of your last update date.
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"MegaDETH" <[email protected]> wrote in message
news:[email protected]...
>
> That worked !!
>
>
> Thank you again for your help
>
>
> Henry Wrote:
> > MegaDeth,
> >
> > Sorry,
> > I forgot that the slashes can't be used in a filename.
> >
> > 2 ways around that.
> >
> > 1) Control Panel>Regional and Language Options>Regional
> > Options>Customize>Date>Date Separator = .
> > This will change your date format to 7.11.2005 (throughout windows)
> >
> > 2)sFilename = Month(Date) & "." & Day(Date) & "." & Year(Date) &
> > ".xls"
> >
> > Henry
> >
> >
> >
> > "MegaDETH" <[email protected]>
> > wrote in
> > message news:[email protected]...
> > >
> > > OK I have a minor problem with the saving as the date.
> > >
> > > Using sFilename = Date & ".xls"
> > >
> > >
> > > It tried to save it in this format 7\11\2005.xls. This would be
> > fine
> > > but it won't save this way as it thinks it is a folder with the \
> > >
> > > Do I have any other options or is it a setting in Excel that has to
> > do
> > > with the way the date is saved?
> > >
> > >
> > >
> > > thanks
> > >
> > >
> > > --
> > > MegaDETH
> > >
> > ------------------------------------------------------------------------
> > > MegaDETH's Profile:
> > > http://www.excelforum.com/member.php...o&userid=25004
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=385289
> > >
>
>
> --
> MegaDETH
> ------------------------------------------------------------------------
> MegaDETH's Profile: http://www.excelforum.com/member.php...o&userid=25004
> View this thread: http://www.excelforum.com/showthread...hreadid=385289
>