Refer to changing workbook names (which include a date range)

Diskutiere und helfe bei Refer to changing workbook names (which include a date range) im Bereich Microsoft Office im Windows Info bei einer Lösung; Hi, I need to copy/paste data to an OverviewWorkbook from several workbooks every month. The file name of every workbook used includes a date, eg... Dieses Thema im Forum "Microsoft Office" wurde erstellt von NoeCollins, 26. April 2019.

  1. NoeCollins
    NoeCollins Gast

    Refer to changing workbook names (which include a date range)


    Hi,


    I need to copy/paste data to an OverviewWorkbook from several workbooks every month.


    The file name of every workbook used includes a date, eg something like "SpecificWorkbook_20191228_to_20190426"

    "SpecificWorkbook_20191228_to_20190329"

    "SpecificWorkbook_20191228_to_20190228"


    In the OverviewWorkbook, the date is entered in a user form upon opening of the workbook.


    The VBA code I need must refer to the SpecificWorkbook which file name includes exactly the date entered in the user form.


    How can I do this?
     
  2. Andreas Killer Win User

    Refer to changing workbook names (which include a date range)

    If the file is already open use a FOR EACH loop and the LIKE operator to compare all filenames if they match.

    Sub Test()

    Dim Wb As Workbook



    For Each Wb In Workbooks

    If Wb.Name Like "*20190426*" Then

    'Reference found

    Exit For

    End If

    Next

    End Sub

    Otherwise you have to search for all files in the specific directory and compare that names. If all files are in a single directory you can use DIR to find the files.

    Sub Test()

    Dim Wb As Workbook

    Dim Path As String, FName As String



    Path = "Z:\"

    FName = Dir(Path & "*.xlsx")

    Do While FName <> ""

    If FName Like "*20190426*" Then

    'Reference found, open the file

    Set Wb = Workbooks.Open(Path & FName)

    Exit Do

    End If

    FName = Dir

    Loop

    End Sub

    Otherwise I suggest that you download my FileSearch class to search for the file.

    https://www.dropbox.com/sh/tuqw83m802x6jn7/AAALmSmkkRszpreiM8AUjx7va?dl=1

    Andreas.
  3. Claus Busch Win User

    Datum Filtern / eingrenzen und in eine neue Tabelle kopieren

    Hallo Muharrem,



    dann probiere es so:



    With Workbooks("Arbeitsplan2013").Sheets("Arbeitsplan")

    .Range("$A$24:$Q$65178").AutoFilter Field:=17, _

    Criteria1:=">=" & Start, Operator:=xlAnd, _

    Criteria2:="<" & Ende

    .Range("$A$24:$Q$65178").AutoFilter Field:=7, _

    Criteria1:="A"

    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    .Range("A25:Q" & LRow).SpecialCells(xlCellTypeVisible).Copy

    Workbooks("Daten Holen").Worksheets("Daten").Range("D1") _

    .PasteSpecial xlPasteValuesAndNumberFormats

    .AutoFilterMode = False

    End With



    Mit freundlichen Grüßen

    Claus
  4. Claus Busch Win User

    Datum Filtern / eingrenzen und in eine neue Tabelle kopieren

    Hallo,



    ändere den Bereich mit den Filteranweisungen ab:



    With Workbooks("Arbeitsplan2013").Sheets("Arbeitsplan")

    .Range("$A$24:$Q$65178").AutoFilter Field:=17, _

    Criteria1:=">=" & Start, Operator:=xlAnd, _

    Criteria2:="<" & Ende

    .Range("$A$24:$Q$65178").AutoFilter Field:=7, _

    Criteria1:="A"

    .Range("A25:Q65178").SpecialCells(xlCellTypeVisible).Copy _

    Workbooks("Daten Holen").Worksheets("Daten").Range("D1")

    .AutoFilterMode = False

    End With



    Mit .AutoFilterMode=false wird der Filter wieder entfernt.



    Mit freundlichen Grüßen

    Claus
  5. Myk Ylaya Win User

    Microsoft Defender for Cloud Apps: Check which policy applied at event

    Hello Ms. M, thanks for coming into forums. I'm also a user like you and I'll be more than happy to help you to the best of my knowledge.

    Yes, you can check which policy was applied to a user in Microsoft Defender for Cloud Apps by reviewing the audit logs.

    To access the audit logs, follow these steps:

    -Go to the Microsoft 365 security center.
    -Click on the "Threat management" section, and then click on "Audit log search".
    -In the "Audit log search" page, you can select "Cloud App Security" as the source and then specify the date range and user account to search for events related to Defender for Cloud Apps.
    -Once the events are displayed, you can look for events related to "File upload blocked" or "Policy applied". These events will include information on which policy was triggered and applied.

    You can also filter the events by policy name to quickly find the specific policy that was triggered.

    Hope this info helps. Feel free to let us know.

    Warm Regards,
    Myk
  6. Claus Busch Win User

    Datum Filtern / eingrenzen und in eine neue Tabelle kopieren

    Hallo nochmals,



    das Filtern geht wesentlich schneller als die Tabelle Zelle für Zelle zu durchlaufen. Trage den vollständigen Namen der Mappe "Arbeitsplan" im Code ein und passe folgenden Code für dich an:



    Private Sub Daten_Click()

    On Error Resume Next

    If Workbooks("Arbeitsplan2013") Is Nothing Then

    MsgBox "Die Mappe Arbeitsplan ist nicht geöffnet.", _

    vbExclamation, "Programmabbruch"

    Exit Sub

    End If

    With Workbooks("Arbeitsplan2013").Sheets("Arbeitsplan")

    .Range("$Q$24:$Q$65178").AutoFilter Field:=1, Criteria1:= _

    ">=" & .Range("R21").Value2, Operator:=xlAnd, _

    Criteria2:="<" & .Range("R22").Value2

    .Range("A25:A65178,Q25:Q65178").SpecialCells(xlCellTypeVisible).Copy _

    Workbooks("Daten Holen").Worksheets("Daten").Range("D1")

    End With

    End Sub



    Spalte A wird nach Spalte D und Spalte Q nach Spalte E kopiert.



    Mit freundlichen Grüßen

    Claus
  7. User Advert


    Hi,

    willkommen im Windows Forum!
Thema:

Refer to changing workbook names (which include a date range) - Microsoft Office

Die Seite wird geladen...

Refer to changing workbook names (which include a date range) - Similar Threads - Refer changing workbook

Forum Datum

Folgende Fehlermeldung, was kann ich machen: The request is blocked. Ref A:...

Folgende Fehlermeldung, was kann ich machen: The request is blocked. Ref A:...: Ich komme auf meinem Notebook mit Windows 11 nicht mehr auf OneDrive und bekomme folgende Fehlermeldung:The request is blocked.Ref A: 460D7E9CFC6F4585816B457B6F41C0F8 Ref B: LON21EDGE0312 Ref C:...
Microsoft Office 22. Februar 2024

Ref A: EB70555114614A31883B6AFFF2AE3044 Ref B: AMS231032604037 Office.com Ref A:...

Ref A: EB70555114614A31883B6AFFF2AE3044 Ref B: AMS231032604037 Office.com Ref A:...: Hallo zusammen, seit kurzem erhalte ich beim Versuch, die Office.com-Seite auf meinem Laptop zu öffnen oder einem Einladungslink zu einer Teams-Sitzung zu folgen, folgende Fehlermeldungen. Auf...
Microsoft Office 11. Dezember 2023

INCLUDE Textfeld aktualisieren

INCLUDE Textfeld aktualisieren: Wie kann man auf Mac OS "Includetext" Felder in Word automatisch aktualisieren?
Microsoft Office 4. Oktober 2023

Sharepoint Online List - Changing order within a string

Sharepoint Online List - Changing order within a string: Hi Guys,I am trying to solve following problem in sharepoint list:I have a Name field. It can contain like "Example Name" or "Example Middle Name" or "Example Middle Middle Name-Name" or whatever....
Microsoft Office 15. November 2022

Using Vlookup on other Workbook whichs path is stored in a Cell

Using Vlookup on other Workbook whichs path is stored in a Cell: Good Day Im Trying to do Vlookup on a closed Workbook whichs path is stored in a Cell. For that im writing a Function which opens the Workbook by the path given, and then does the VLookup on a...
Microsoft Office 26. August 2020

Workbook-Textkonverter

Workbook-Textkonverter: Ich möchte in Word einen Serienbrief schreiben. Dafür will ich die Adressdatei aus dem Excel einfügen. Wenn ich das Dokument aufrufe erhalte ich die Meldung: "Diese Datei muss mithilfe des...
Microsoft Office 7. März 2020

Changing the header look on a communication site

Changing the header look on a communication site: Hi there, I am trying to change the header look on my newly created communication site. the result should look like in the example here (color background and sub-menus):...
Microsoft Office 29. Januar 2019
Refer to changing workbook names (which include a date range) solved
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.