Capturing the current workbook and another workbook with a filename mask

shday98

New Member
Joined
Oct 21, 2020
Messages
2
Good evening! This is my first post, hopefully I get it right.

I have a .XLSM with my macros in it, but as part of my monthly update I need the user to download data from a website that results in a .csv in the downloads folder that users typically click which opens it in excel.

While I can capture my .XLSM file as an object, I'm not sure how a dynamically named file that follows a bit of a mask can be selected and captured as a second object my VBA can work with.

Dim WB1 As Workbook
Dim WB2 As Workbook

' Capture current workbook that is my static .xlsm, not a problem
Set WB1 = ActiveWorkbook

' Open new workbook... this is my problem, the file mask will be... "agency*_alerts_*.csv"... but I'm not sure how to dynamically find that other open spreadsheet to capture it as an object
Workbooks.Open Filename:="C:\Users\[user]\Downloads\agency*_alerts_*.csv"

' Capture new workbook
Set WB2 = ActiveWorkbook

' Go back to original workbook
WB1.Activate


Thanks in advance for any shared wizadry!!
 

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.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows
This is how I usually do
VBA Code:
' Define this Workbook as wbA
Set wbA = ActiveWorkbook
' Define working sheet in wbA. Change sheet name accordingly
Set ws1 = wbA.Sheets("Sheet1")

' Search destination Workbook
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked

' Define opened Workbook as wbB while opening it.
Set wbB = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
' Define working sheet in wbB. Change sheet name accordingly
Set ws2 = wbB.Sheets("Sheet1")
 

shday98

New Member
Joined
Oct 21, 2020
Messages
2
This is how I usually do
VBA Code:
' Define this Workbook as wbA
Set wbA = ActiveWorkbook
' Define working sheet in wbA. Change sheet name accordingly
Set ws1 = wbA.Sheets("Sheet1")

' Search destination Workbook
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked

' Define opened Workbook as wbB while opening it.
Set wbB = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
' Define working sheet in wbB. Change sheet name accordingly
Set ws2 = wbB.Sheets("Sheet1")
Very quick, very efficient, thank you very much!!
 

Forum statistics

Threads
1,148,244
Messages
5,745,596
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