Save with dates
Following is my code, basically this report is run weekly from previous
Monday date to Sunday date. This macro does everything except add in those dates to the file name. Any suggestions? Dim wb As Workbook Dim ws As Worksheet Dim x As Variant Dim y As Variant Dim strFileName As String Set wb = ActiveWorkbook Set ws = wb.Worksheets("idrmterm") x = Format(Date - 7, mm - dd - yy) y = Format(Date - 1, mm - dd - yy) Dim strPath As String strPath = "H:\REPORTS\Every Monday Reports\" '===strFileName = x & " " & y & ws & ".xls"<<<=== ActiveWorkbook.SaveAs Filename:=strPath & strFileName Set wb = Nothing Set ws = Nothing Application.Dialogs(xlDialogSendMail).Show ActiveWorkbook.Close End Sub |
Save with dates
strPath = "H:\REPORTS\Every Monday Reports\"
strFileName = x & " " & y & ws.Name & ".xls" -- Regards, Tom Ogilvy "CV323" wrote: Following is my code, basically this report is run weekly from previous Monday date to Sunday date. This macro does everything except add in those dates to the file name. Any suggestions? Dim wb As Workbook Dim ws As Worksheet Dim x As Variant Dim y As Variant Dim strFileName As String Set wb = ActiveWorkbook Set ws = wb.Worksheets("idrmterm") x = Format(Date - 7, mm - dd - yy) y = Format(Date - 1, mm - dd - yy) Dim strPath As String strPath = "H:\REPORTS\Every Monday Reports\" '===strFileName = x & " " & y & ws & ".xls"<<<=== ActiveWorkbook.SaveAs Filename:=strPath & strFileName Set wb = Nothing Set ws = Nothing Application.Dialogs(xlDialogSendMail).Show ActiveWorkbook.Close End Sub |
Save with dates
Thanks Tom.
It works, except that the dates are not coming up as mm-dd-yy instead mm/dd/yy and it won't save the file. What do you suggest? This is how it reads now: Dim wb As Workbook Dim ws As Worksheet Dim x As Date Dim y As Date Dim strFileName As String Set wb = ActiveWorkbook Set ws = wb.Worksheets("idrmterm") x = Format(Date - 7, mm - dd - yy) y = Format(Date - 1, mm - dd - yy) Dim strPath As String strPath = "H:\REPORTS\Every Monday Reports\" strFileName = x & " " & y & ws.Name & ".xls" ActiveWorkbook.SaveAs Filename:=strPath & strFileName Set wb = Nothing Set ws = Nothing Application.Dialogs(xlDialogSendMail).Show ActiveWorkbook.Close |
Save with dates
Try
x = Format(Date - 7, "mm - dd - yy") y = Format(Date - 1, "mm - dd - yy") Putting in "" worked for me on asimilar thing "CV323" wrote: Thanks Tom. It works, except that the dates are not coming up as mm-dd-yy instead mm/dd/yy and it won't save the file. What do you suggest? This is how it reads now: Dim wb As Workbook Dim ws As Worksheet Dim x As Date Dim y As Date Dim strFileName As String Set wb = ActiveWorkbook Set ws = wb.Worksheets("idrmterm") x = Format(Date - 7, mm - dd - yy) y = Format(Date - 1, mm - dd - yy) Dim strPath As String strPath = "H:\REPORTS\Every Monday Reports\" strFileName = x & " " & y & ws.Name & ".xls" ActiveWorkbook.SaveAs Filename:=strPath & strFileName Set wb = Nothing Set ws = Nothing Application.Dialogs(xlDialogSendMail).Show ActiveWorkbook.Close |
All times are GMT +1. The time now is 02:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com