时间戳

首页 > 解决方案 > 使用 VBA 以特定格式转换日期/时间

vba - 使用 VBA 以特定格式转换日期/时间

问题描述

所以，这是我正在尝试做的某种示例，VBA 正在做类似循环，打开一些文件，过滤它们并保存它们..但是我想DD.MM.YYYY hh:mm 在 A 列中指定日期格式（完整）作为输出。我不知道该怎么做，尝试了一些东西，但总是错误的输出......

Sub Convert()

    Dim FileSystem As Object
    Dim HostFolder As String

    ' Folder with systems
    HostFolder = "C:\Users\MirzaV\Desktop\Converter"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)

End Sub


Sub DoFolder(Folder)
    Dim Workbook
    Dim SubFolder
    Dim date_test As Integer

    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        ' Operate on each file
        Set Workbook = Workbooks.Open(File)

        'MsgBox (Workbook.FileFormat)

        If Workbook.FileFormat = -4158 Then
            Set Workbook = Workbook.ActiveSheet
            Workbook.Columns("D:R").EntireColumn.Delete
            Workbook.Columns("F:H").EntireColumn.Delete
            Workbook.Rows("1:2").Delete
            Dim FLDR_NAME As String
            FLDR_NAME = Application.ActiveWorkbook.Path & "_converted"
            Dim fso As Object
            Set fso = CreateObject("Scripting.FileSystemObject")
            FLDR_NAME = Application.ActiveWorkbook.Path
            FLDR_NAME = Replace(FLDR_NAME, "Converter", "Converter_Converted")
            If Not fso.FolderExists(FLDR_NAME) Then
                fso.CreateFolder (FLDR_NAME)
            End If
            Dim newFileName As String
            newFileName = FLDR_NAME & "\" & Workbook.Name & "_converted.txt"
            Workbook.SaveAs Filename:=newFileName


            'Close + Save
            Application.ActiveWorkbook.Close
        End If
    Next
End Sub

标签： vbaexcel

解决方案

如果你需要给你的日期格式，那么你应该这样做

new_date = Format(date_var, "dd.mm.yyyy hh:mm")

您使用代码获得的日期在哪里date_var（但我们看不到）。

推荐阅读