Macro to save file with active cell value with 3 date options

[gilisan]

Dear All,

I am looking for a macro to save a file name with active cell value(where the cursor is placed) and with 3 date options.

Date options = a pop-up should come-up with buttons asking A B and C. If I click A = save the file with today's date. Click B = yesterday's date and Click C = day before yesterday's date.

In brief the flow of the macro should be:

Open the excel file>>>Place the cursor on the cell which should be the filename(the name may be anywhere in the sheet)>>> run the macro >>> A pop with with button A B and C>>> say click on B>>> file should be saved in a drive path with the file name and yesterday's date like _xyz_20100625.xls

Could anybody help me in building this macro for me, I would be greatful to them and this may change my life in the office I work.

Thanks a lot in advance for helping me.

[mdmackillop]

Welcome to VBAX
Change the path to suit and give this a try

[gilisan]

hi, many thanks for the macro. But I have added the path and it saves the file in olny one name ActivecellDte.xls. It is not actually saving with the active cell value and the actual date.

Could you please check. Once again thanks for your help.

[gilisan]

Apologies mate, it is working fine and wonderful, it was my mistake putting the path wrongly.

I think you are going to change my life now.

One more question Can this macro be modified to also save a PDF copy along with excel? I am using Acrobat 7.0 PDF distiller and the path is C:\program files\Adobe\Acrobat 7.0\Distillr\acrodist.exe

Once again thanks for assistance.

[gilisan]

Apologies mate, it is working fine and wonderful, it was my mistake putting the path wrongly.

I think you are going to change my life now.

One more question Can this macro be modified to also save a PDF copy along with excel? I am using Acrobat 7.0 PDF distiller and the path is C:\program files\Adobe\Acrobat 7.0\Distillr\acrodist.exe

Once again thanks for assistance.

Hi mate, even the PDF thing is resolved. I have used the below and it works

[VBA]
Sub DoSave(Dte)
Dim FName As String
FName = Path & ActiveCell & Dte & ".xls"
ActiveWorkbook.SaveAs fileName:=FName, FileFormat:=xlExcel8
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
If Len(Dir(FName)) > 0 Then
MsgBox FName & " saved"
Else
MsgBox "File not saved"
End If
Unload UserForm1
End Sub
[/VBA]

[gilisan]

Hi mate, please let me know how can I define the path to save the pdf file. Currently it is saving the pdf file the last temp memory saved....say excel file is saving in defined location, but pdf is saving in the destop.

let me k ow how this can be corrected. thanks a lot.

[gilisan]

Hi mate, please let me know how can I define the path to save the pdf file. Currently it is saving the pdf file the last temp memory saved....say excel file is saving in defined location, but pdf is saving in the destop.

let me k ow how this can be corrected. thanks a lot.

Hi mate, I need your urgent assistance to save pdf along with excel...i tried the above, but it saves only in the temp/last saved drive.

Please help... it is urgent!!!

Thanks a lot for your help in advance!!!

[mdmackillop]

Add the path as follows, changing the names etc. to suit
[VBA]
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"C:\Users\MD\Desktop\Week6Assignment.pdf",
[/VBA]

[gilisan]

Thanks alot for that mate. Just a quick one, what should be the code if I want to save the files on the business or week days (Date).

For example: On Monday I want to save Monday's, Friday's and Thursday's file.

[mdmackillop]

[VBA]
Option Explicit
Const Path = "C:\AAA\"

Private Sub UserForm_Initialize()
Dim dte2 As Date, dte3 As Date
Select Case Weekday(Date)
Case 2
dte2 = Date - 3
dte3 = Date - 4
Case 3
dte2 = Date - 1
dte3 = Date - 4
Case Else
dte2 = Date - 1
dte3 = Date - 2


End Select
CommandButton1.Caption = Format(Date, "dd/mm/yy")
CommandButton2.Caption = Format(dte2, "dd/mm/yy")
CommandButton3.Caption = Format(dte3, "dd/mm/yy")
Label1.Caption = ActiveCell
End Sub
Private Sub CommandButton1_Click()
DoSave DateValue(CommandButton1.Caption)
End Sub
Private Sub CommandButton2_Click()
DoSave DateValue(CommandButton2.Caption)
End Sub
Private Sub CommandButton3_Click()
DoSave DateValue(CommandButton3.Caption)
End Sub
Sub DoSave(Dte)
Dim FName As String
FName = Path & ActiveCell & Format(Dte, "_yyyymmdd") & ".xls"
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlExcel8
If Len(Dir(FName)) > 0 Then
MsgBox FName & " saved"
Else
MsgBox "File not saved"
End If
Unload UserForm1
End Sub
[/VBA]

[gilisan]

Thanks a lot mate... YOU ARE A STAR.