0

I'm wondering if it is possible if you could help me with an issue I'm having with my excel workbooks.

Lets say for example I have 2 workbooks in the same folder as the book with the running VBA code.

main.xlsm
20160909- Beef v22_1.xlsx
20161015- Stew v21_0.xlsx

I want to open these books, and copy data from a cell range in a sheet - the key identifiers here being "beef" and "stew" since these wont change. This is very similar to what Pierre was attempting to do here, but I don't need to go that far, I know the worksheet name and cell range I want to copy and I almost know how to copy them with 

Workbooks(beefWB).Sheets("Sheet2").Range("A1:E20").Copy _
    Destination:=Workbooks("main.xlsm").Sheets("combined").Range("E2")

The main issue I am having is the wildcard searching with Dir;

Dim beefWB As String

beefWB = Dir(ThisWorkbook.Path & "\*beef*.xlsx")
Do While beefWB <> ""
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & beefWB
    beefWB = Dir
Loop

Excel can't seem to find the sheets. Since all the sheets start with "201" it will open them with "*201*.xlsx", it will even open the beef workbook with "*609*.xlsx" but it will not open with the actual keywords "beef" or "stew". I have attempted to switch tactic by using 

If InStr(beefWB, "beef") > 0 Then
    Workbooks.Open Filename:=ActiveWorkbook.Path & "\" & beefWB

But I'm quite new to this so I'm probably messing that up one way or another.

Bonus- If it were possible to open the most recent file of each type (beef and stew) that would be even better, but it is not nessessary.

Apologies since this seems to be a common question, but I hope you can help.

Community
  • 1
  • 1
Memphinstein
  • 13
  • 2
  • `Dir("D:\temp\*beef*.xlsx")` worked fine for me with a file named "20160909- Beef v22_1.xlsx" – Tim Williams Nov 13 '16 at 17:20
  • 1
    Are you using this macro on a Mac? This might explain the problems as Macs don't support wildcards: https://msdn.microsoft.com/en-us/library/office/gg278779.aspx – Ralph Nov 13 '16 at 17:30
  • Thanks for your responses - @Ralph and @tim-williams - Seems to work fine at home but not at work - ill investigate that later. Just another question though, how do I refer to the workbook afterwards for use with `Workbooks(beefWB).Sheets("Sheet2").Range("A1:E20")...` I think I have to grab the workbook name before the `Do While` and assign it to a workbook variable for use later on but the answer [here](https://stackoverflow.com/questions/19134518/how-to-convert-string-to-workbook-name) uses definitive file paths so i'm not sure on how to do it. Thanks again! – Memphinstein Nov 13 '16 at 18:58
  • @Memphinstein If you have a new question then you should post a new question. We do not encourage multiple questions per post. This is normally done by new users and often leads to "endless" questions / answers / questions / answers / questions / answers... all in **one** post. This kind of resolving problems is typical for discussion sites. This website is different and tries to cut the chit-chat. [Take the tour](http://stackoverflow.com/tour) and read how this site is different. – Ralph Nov 13 '16 at 20:52

1 Answers1

0

How about trying this AddIn?

http://www.rondebruin.nl/win/addins/rdbmerge.htm

That should do all you want, and more.