VBA Help - Defining 2nd Open Excel Workbook without knowing File Name - Excel 2016

[Johnny Thunder]

Hello All,

I have come across a project that I am not sure how to finish, here is the request details.

Have a macro in File A to copy details from a daily/weekly run query with a changing file name.

So essentially, Macro is in File A, and query is in File B. I need a way to Dim File B as a variable to run my code.


Here is where I am at,

Sub DownloadReport()


Dim Wb1     As Workbook, Wb2 As Workbook
Dim Wb2Sht  As Worksheet


Set Wb1 = ThisWorkbook
Set Wb2 = ActiveWorkbook 'Not Working
Set Wb2Sht = Wb2.Worksheets(1)

Wb2.Activate
Wb2Sht.Cells.Copy


Wb1.Sheets("Report").Range("A1").PasteSpecial xlPasteFormats
Wb1.Sheets("Report").Range("A1").PasteSpecial xlPasteValues


Wb2.Close False

End Sub

I thought I could use the Activeworkbook but this is not working since the user would have to click on the query before triggering the macro and the users who will be using the file don't follow directions well. So I want to make this more fool proof.

Is there a way to maybe find all the excel sheets that are open and eliminate "ThisWorkbook" from the available options and Dim the remaining file as Wb2?

 

[Fluff]

You can do that quite easily, BUT what if the user has more than 2 workbooks open?
Will file B have a specific naming convention that could be used to identify it?

 

[Johnny Thunder]

The users have been instructed to only have two files open when running.

And Yes/No in regards to the name of the Downloaded Query, the file name is structured like so "Collections Report - xxxxxx" The x's represent random numbers the query generates when exporting.

 

[Fluff]

Ok, how about

Sub GetWb()
   Dim wbk As Workbook, Wbk2 As Workbook
   For Each wbk In Workbooks
      If LCase(wbk.Name) Like "collections report*" Then Set Wbk2 = wbk
   Next wbk
End Sub

 

[Johnny Thunder]

Woohoo!

That worked like a charm! Thanks for the help on this. I am sure I will be able to adapt this to other projects if they come up.

Here is your code parsed in with my original project.

Sub DownloadReport()


Dim wbk     As Workbook, Wbk2 As Workbook, wB1 As Workbook
Dim Wb2Sht  As Worksheet
   
For Each wbk In Workbooks
   If LCase(wbk.Name) Like "collections report*" Then Set Wbk2 = wbk
Next wbk
   
MsgBox Wbk2.Name


Set wB1 = ThisWorkbook
Set Wb2Sht = Wbk2.Worksheets(1)


Wbk2.Activate
Wb2Sht.Cells.Copy


wB1.Sheets("Report").Range("A1").PasteSpecial xlPasteFormats
wB1.Sheets("Report").Range("A1").PasteSpecial xlPasteValues


Wbk2.Close False
   
End Sub

 

[Fluff]

Glad to help & thanks for the feedback

 

[JohnR1962]

Found this searching and got what I needed.

 

[Fluff]

Glad it helped