Open a workbook with only partial name known

TekillaSunrize

New Member
Joined
Sep 24, 2021
Messages
12
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hello all,

I have this macro:

VBA Code:
Sub AndurilUploadSample(control As IRibbonControl)
'
' Anduril Macro
'

'
    Application.ScreenUpdating = False
    Workbooks.Open Filename:=(CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "all-samples.csv")
    Workbooks.Open Filename:=(CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "documentSearch.csv")
    Windows("all-samples.csv").Activate
    Columns("G:G").Select
    Selection.Copy
    Windows("documentSearch.csv").Activate
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Columns("C:D").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$I$15138").AutoFilter Field:=4, Criteria1:=RGB(255 _
        , 199, 206), Operator:=xlFilterCellColor
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.EntireRow.Delete
    ActiveSheet.Range("$A$1:$I$14937").AutoFilter Field:=4
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("recalls_emea-sample-upload-template.csv").Activate
    Range("F2").Select
    ActiveSheet.Paste
    Windows("documentSearch.csv").Activate
    Range(Selection, Selection.End(xlUp)).Select
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("recalls_emea-sample-upload-template.csv").Activate
    Range("G2").Select
    ActiveSheet.Paste
    Windows("documentSearch.csv").Activate
    Range(Selection, Selection.End(xlUp)).Select
    Range("H2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("recalls_emea-sample-upload-template.csv").Activate
    Range("D2").Select
    ActiveSheet.Paste
    Windows("documentSearch.csv").Activate
    Range(Selection, Selection.End(xlUp)).Select
    Range("I2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("recalls_emea-sample-upload-template.csv").Activate
    Range("H2").Select
    ActiveSheet.Paste
    Columns("H:H").Select
    Selection.Replace What:="T", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=".*", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("D:D").Select
    Selection.Replace What:="asin research", Replacement:=" ", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="asin research", Replacement:=" ", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "yyyy/mm/dd hh:mm:ss"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("I2").Select
    Selection.Copy
    Columns("I:I").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveSheet.Paste
    Range("B2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "IAS"
    Range("B2").Select
    Selection.Copy
    Columns("B:B").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveSheet.Paste
    Range("A2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=MID(RC[6],FIND("" - "",RC[6],1)+3,10)"
    Range("A2").Select
    Selection.Copy
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Windows("all-samples.csv").Activate
    ActiveWindow.Close
    Windows("documentSearch.csv").Activate
    ActiveWindow.Close SaveChanges:=False
    Range("A1").Select
    Kill (CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "all-samples.csv")
    Kill (CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "documentSearch.csv")
    MsgBox "Sample File Updated!"
End Sub

The 2 workbooks that are opened at the beginning of the scrip are "all-samples.csv" and "documentSearch.csv" the problem is that the files are found on the desktop with a different name ( "all-samples-xxxx-1234-xxxx.csv" or "documentSearch_xxxx.csv" having always the same only the a part of the name, so i always have to rename them in order to get found by the macro. Is there a way to change the macro in order to open this files based on partial name? Like adding the wildcard after "all-samples" and "documentSearch" so that i don't have to rename the files every time?

Also, if this is possibile, something has to be changed also in different parts of the macro, no? For example:
- Windows("documentSearch.csv").Activate - same problem with workbookname, it will have to activate any file starting with "documentSearch" found on desktop
- Kill (CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & "documentSearch.csv") - same thing


Very sorry if the questions is simple or stupid, I'm very new at this and still trying to figure out everything :D

Many thanks
 

TekillaSunrize

New Member
Joined
Sep 24, 2021
Messages
12
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
VBA Code:
On Error GoTo CloseAndDelete
    Selection.Copy
    With .Range("D2", .Range("D2").End(xlDown).End(xlToRight))
    If WorksheetFunction.CountA(.Cells) = 0 Then MsgBox "No new SIM-Tickets found, please try again later!"
    
CloseAndDelete: With Workbooks.Open(P & A).Sheets(1).UsedRange
    ActiveWindow.Close
    End With
    With Workbooks.Open(P & D).Sheets(1).UsedRange
    ActiveWindow.Close SaveChanges:=False
    End With
    Kill P & A
    Kill P & D
    Exit Sub

Resolved it with this :)
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,148,244
Messages
5,745,606
Members
423,964
Latest member
Rayds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top