Open a workbook with only partial name known

[TekillaSunrize]

Hello all,

I have this macro:

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

 

[Marc L]

Hi, according to the Dir VBA help - a must read ! - a demonstration for starters :​

Sub Demo1()
    Dim P$, A$, D$
        P = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
        A = Dir$(P & "all-samples-*.csv")
        D = Dir$(P & "documentSearch_*.csv")
        MsgBox "A :  " & A & vbLf & vbLf & "D :  " & D, 64, "Files found"
End Sub

 

[TekillaSunrize]

I appreciate the help! will definitely also look into Dir VBA help, for more information.
Thank you again!

 

[TekillaSunrize]

Hi, according to the Dir VBA help - a must read ! - a demonstration for starters :​

Sub Demo1()
    Dim P$, A$, D$
        P = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
        A = Dir$(P & "all-samples-*.csv")
        D = Dir$(P & "documentSearch_*.csv")
        MsgBox "A :  " & A & vbLf & vbLf & "D :  " & D, 64, "Files found"
End Sub

ok, so I've been playing a little with what you've sent me and what I've found on the internet and slowly all this words are starting to make a sense :D

For opening the files what you're sent me worked like a charm. I've also find different methods of obtaining the same thing, like:

Sub testmacro()
'
Dim sFound As String
Dim sFound2 As String


sFound = Dir(ActiveWorkbook.Path & "\documentSearch*.csv")
sFound2 = Dir(ActiveWorkbook.Path & "\all-samples*.csv")
If sFound <> "" Then
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & sFound
    End If
    
If sFound2 <> "" Then
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & sFound2
End If

Application.ScreenUpdating = False
    Windows("all-samples.csv").Activate
    Columns("G:G").Select
    Selection.Copy
    Windows("documentSearch.csv").Activate
    Columns("C:C").Select

Now I'm stuck at

 Windows("all-samples.csv").Activate

and

 Windows("documentSearch.csv").Activate

And kind of make sense getting stuck there because the workbooks that opened are not named anymore only "all-samples.csv" and "documentSearch.csv" . Is there a way to fix this in order to activate the files that are opened with

sFound = Dir(ActiveWorkbook.Path & "\documentSearch*.csv")
sFound2 = Dir(ActiveWorkbook.Path & "\all-samples*.csv")
If sFound <> "" Then
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & sFound
    End If
    
If sFound2 <> "" Then
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & sFound2
End If

Again, many thanks for the support

 

[Marc L]

Just use the Dir results you have stored in variables sFound & sFound2 or like in my demonstration variables A & D …​

 

[Marc L]

In fact any good enough VBA procedure does not need to activate or select anything just working directly with the appropriate objects …​

In the next demonstration if located in a standard module the With codeline 'targets' the sheet of the all-samples*.csv file found​

and as the documentSearch*.csv file found is last opened to its worksheet is the active sheet.​

So, without activating neither selecting, a dot before any range statement refers to the With codeline​

and a range without any dot before just refers to the active sheet :​

Sub Demo1a()
    Dim P$, A$, D$
        P = CreateObject("WScript.Shell").specialfolders("Desktop") & "\"
        A = Dir$(P & "all-samples-*.csv")
        D = Dir$(P & "documentSearch_*.csv")
        If A = "" Or D = "" Then Beep: Exit Sub
        Application.ScreenUpdating = False
    With Workbooks.Open(P & A).Sheets(1).UsedRange
         Workbooks.Open P & D
        .Columns(7).Copy
         Columns(3).Insert xlToRight
        
    End With
        Application.ScreenUpdating = True
End Sub

 

[TekillaSunrize]

In fact any good enough VBA procedure does not need to activate or select anything just working directly with the appropriate objects …​

In the next demonstration if located in a standard module the With codeline 'targets' the sheet of the all-samples*.csv file found​

and as the documentSearch*.csv file found is last opened to its worksheet is the active sheet.​

So, without activating neither selecting, a dot before any range statement refers to the With codeline​

and a range without any dot before just refers to the active sheet :​

Sub Demo1a()
    Dim P$, A$, D$
        P = CreateObject("WScript.Shell").specialfolders("Desktop") & "\"
        A = Dir$(P & "all-samples-*.csv")
        D = Dir$(P & "documentSearch_*.csv")
        If A = "" Or D = "" Then Beep: Exit Sub
        Application.ScreenUpdating = False
    With Workbooks.Open(P & A).Sheets(1).UsedRange
         Workbooks.Open P & D
        .Columns(7).Copy
         Columns(3).Insert xlToRight
       
    End With
        Application.ScreenUpdating = True
End Sub

Man, you are my hero! :D You managed to explain so much in a simple way so it can be understood even by someone who doesn't now nothing about that.

I've resolved everything with the file location(With Workbooks.Open(P & A).Sheets(1).UsedRange
Columns("G:G").Select), added
If A = "" Then MsgBox "All-Samples file not found, please download!": Exit Sub
If D = "" Then MsgBox "File from SIM-Ticketing not found, please download!": Exit Sub
in case there is no file and some little changes.

Now I'm trying to improve it a little, like informing if there were all duplicates to stop, close the file and delete it.

    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!": Exit Sub

and the close it and delete it, but i'm doing something wrong bucause when i write after that

With Workbooks.Open(P & D).Sheets(1).UsedRange
ActiveWindow.Close SaveChanges:=False
Kill P & D

it doesn't do nothing. but that's another story :D

Thanks again and wish you the best!

 

[Marc L]

​

It seems you exit the Sub before to kill it ?​

 

[Marc L]

​

Correction of previous post as I wanna say « It seems you exit the sub before killing the workbook ? »​

 

[TekillaSunrize]

yep, i noticed that after. stupid me ) i've been trying with this, also:

 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:     Exit Sub
    
    End With

I was wondering how could I add to "CloseAndDelete: Exit Sub" someting like: CloseAndDelete: Exit Sub AND Kill P & A and P & D
stil searching :D