CODEWITHSUNDEEP
vbaexcel
Duraholiday
Duraholiday

Reputation: 111

Save as date and time not working

It is supposed to save as file name: Folder\test location 'what ever is in cell C27' and then data and time. I am getting :'008 11 2015 00 00 00'. How do I clean this up with out using "/" and ":"? Note the first 0 is just the test number I used.

Also this macro is in a template that the Testing software uses that is why it has to use Auto_open but the other problem is that when it saves as a non template file, upon opening it tries to run the macro in the non template file. How can I make it so the macro does not save in or is disabled in the save as files/ non template files?

Sub Auto_Open()

Dim FileName    As String
Dim FilePath    As String
Dim FileDate    As String

MyNote = "Is Cell 'C27' Overview Information" & SavePath & " Location_1,2,3,or 4?"

Answer = MsgBox(MyNote, vbQuestion + vbYesNo)

If Answer = vbYes Then

    FilePath = "C:\Users\aholiday\Desktop\FRF_Data_Macro_Insert_Test"
    FileName = Sheets("Data").Range("C27").Text
    ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName

    Dim FileCopyName    As String
    Dim FileCopyPath    As String

    FilePath = "C:\Users\aholiday\Desktop\Backup"
    FileName = Sheets("Data").Range("C27").Text
    FileDate = Format(Date, "mm dd yyyy hh mm ss")
    ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName & FileDate

    MsgBox "File was saved!"

    MyNote = "Open FRF Data Sheet?(After Forth Test Only)"

    Answer = MsgBox(MyNote, vbQuestion + vbYesNo)

    If Answer = vbYes Then

        Workbooks.Open ("FRF_Data_Sheet_Template.xlsm")

    Else
        MsgBox "Ready for Next Test, Please Exit."
    End If

Else
    MsgBox "File was not saved, Please Use Location_1,2,3or,4 Durring SIG ATM Test"
End If

End Sub

Solved:

  Sub Auto_Open()

  With Range("A30")
  .Value = Time
  .NumberFormat = "h-mm-ss AM/PM"
  End With


    Dim FileName    As String
    Dim FilePath    As String
    Dim FileDate    As String

    MyNote = "Is Cell 'B27' Overview Information" & SavePath & " Location1,2,3,or 4?"

    Answer = MsgBox(MyNote, vbQuestion + vbYesNo)

    If Answer = vbYes Then

        FilePath = "C:\Users\aholiday\Desktop\FRF_Data_Macro_Insert_Test"
        FileName = Sheets("Data").Range("C27").Text
        ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName

        Dim FileCopyName    As String
        Dim FileCopyPath    As String
        Dim FileTime        As String

        FilePath = "C:\Users\aholiday\Desktop\Backup"
        FileName = Sheets("Data").Range("B27").Text
        FileTime = Sheets("Data").Range("A30").Text


        ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName & FileTime & ".xlsx", FileFormat:=xlOpenXMLWorkbook

        MsgBox "File was saved!"
        MsgBox "Ready for Next Test, Please Exit."


    Else
    MsgBox "File was not saved, Please Use Location_1,2,3or,4 Durring SIG ATM Test"
    End If

 End Sub

Upvotes: 0

Views: 453

Answers (1)

MatthewD
MatthewD

Reputation: 6761

You can't have a \ in a filename.

For the date part, use the format function. You can define the date format if you want by using "MM-dd-yyy"

ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName & Format(FileDate, "MM-dd-yyyy") & ".xlsx", FileFormat:=xlOpenXMLWorkbook

Use the FileFormat:=xlOpenXMLWorkbook to save it as a workbook without macros.

Upvotes: 1

Related Questions