VBA wildcard, copy from open file

[Vladgs]

Hi everyone,

I am trying to copy some data from an open file. I have some code, works great but i'd like to make it work for different file names.
The "Supplier_report.csv" file is folowed by a date (like Supplier_report_211215.csv ), I always need tot remove it manually in order for the macro to work.
Any sugestions on the below code to cvhange that ? I have tried wildcards and a few other things but my VBA is quite basic.

Any help is highly apreciated !

Sub GetData()

    Windows("Supplier_Report.CSV").Activate
    Range("A:A,C:C,E:E,F:F,G:G,H:H,I:I,J:J,L:L,u:u,w:w,x:x,y:y,z:z,aa:aa,ab:ab,ad:ad,ae:ae,af:af,ag:ag,am:am,an:an,ap:ap,ar:ar,av:av,aw:aw,ay:ay,az:az,ba:ba,bb:bb,bc:bc,bf:bf,bh:bh,bi:bi,bj:bj,bl:bl,bm:bm,bu:bu").Select
    Range("BU1").Activate
    Selection.Copy
    Windows("VSU Pivot file.xlsb").Activate
    Worksheets("VendorExtract").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[MichaelS94]

Doesn't directly answer your question, but a work-around which I use often. The following code would sit within your code at the start, and enables you to choose the file you want to use as "select file" message box.

I'm not sure a wildcard-like function would work, as, say you have two different supplier reports from different weeks, the code wouldn't know which one you would want to use.

There may be a better way, but my solution may be of interest!

Dim FileName As Variant
FileName = Application.GetOpenFilename("Excel Files (*.xls;*.xlsx; *.xlsm; *.csv)," & "*.xls; *.csv; *.xlsx; *.xlsm", Title:="Open data") 'select data file location
If FileName = False Then Exit Sub

Set wbTarget = Application.Workbooks.Open(FileName) 'stores workbook you have selected for future reference
wbTarget.Activate

 

[steve the fish]

The book must be open for your code to work. So try this:

For Each wb In Application.Workbooks
    If wb.Name Like "Supplier_Report*" Then wb.Activate
Next

 

[Vladgs]

Thank you Steve, Michael

I've used Steve's code, was easyer to intergrate with my scenario.

Michael like you anticipated there are more files in that folder, that's why I want to copy from an opened work book.

Thank you both for your interest and solutions !

Best regards,