Question

我是新鲜的,可以撰写宏观文章,我可以利用一些帮助,把这 out成真! 我有一张Excel电子表格,用于为我的工作转变提供日常的“核心”说明。我的目标是让一个子可以点击该星期的装载量,让人们知道他们的任务是什么。有一个文件夹,载有Excel .csv文件,每个班的每周时间表。有多个档案,名称相似,但根据时间安排的哪个星期进行最后改动。举例来说,这套文件可能有以下文件:

1st_Shift_01Jan2023
1st_Shift_08Jan2023
2nd_Shift_01Jan2023
2nd_Shift_09Jan2023

我想有一个宏观,在被点击时,打开了适用于我轮班(第2班)的最新档案,以便我能够复制最新时间安排档案中的内容,并将其贴在我们的每日说明中。

此前,我们只有一本附有更新时间表的工作手册。这已不再是我们用于时间表,因此我不得不改变宏观。现在有多份档案,我不敢知道如何获得最近关于相关转变的手册。这里使用的是前宏观法典:

Sub ScheduleTuesday()

    Dim HuddleNotes As Workbook
    Dim ThisWeekSchedule As Workbook
    
    Set HuddleNotes = ThisWorkbook
    Application.DisplayAlerts = False
    Set ThisWeekSchedule = Workbooks.Open("S:SystemsSchedules)
    Application.DisplayAlerts = True
    
    ThisWeekSchedule.Activate
        Sheets("Schedule Input").Activate
        Range("A1:E29").Select
            Selection.Copy
            
    HuddleNotes.Activate
        Sheets("Schedule").Activate
            Range("A1:E29").Select
            ActiveSheet.Paste
            
    ThisWeekSchedule.Activate
        Application.DisplayAlerts = False
        ActiveWorkbook.Close
        Application.DisplayAlerts = True

End Sub

我也许必须利用名录功能吗? 但是,我确实是新老会的,过去曾使用过,而且不知道如何使用。事先感谢你能够提供的任何帮助!

Answer 1

这里是要求转变的宏观最新版本,然后为这一转变装上最现有的CSV文档:

Sub LoadScheduleForShift()

    Dim FolderPath As String
    Dim FileName As String
    Dim FileDate As Date
    Dim MostRecentFile As String
    Dim CurrentDate As Date
    Dim Shift As String
    
      Define the folder path where your CSV files are located
    FolderPath = "S:SystemsSchedules"
    
      Prompt the user to enter their shift
    Shift = InputBox("Enter your shift (e.g., 2nd_Shift):", "Shift Selection")
    
      Check if the user canceled the input
    If Shift = "" Then Exit Sub
    
      Initialize variables
    MostRecentFile = ""
    CurrentDate = Date
    
      Find the most recent CSV file for the specified shift
    FileName = Dir(FolderPath & Shift & "*.csv")
    Do While FileName <> ""
        FileDate = FileDateTime(FolderPath & FileName)
        If FileDate > CurrentDate Then
            MostRecentFile = FolderPath & FileName
            CurrentDate = FileDate
        End If
        FileName = Dir
    Loop
    
      Check if a file was found
    If MostRecentFile <> "" Then
          Open the most recent CSV file
        Workbooks.Open Filename:=MostRecentFile
          Now you can manipulate the opened workbook as needed
    Else
        MsgBox "No matching schedule file found for " & Shift
    End If

End Sub

When you run the macro with this modification, it will prompt you to input your shift (e.g., "2nd_Shift") and then import the most current CSV file for that shift.

友情链接