Checking that an external file is open 1

[LGMan]

Hi, I get sent a file each day called 'Daily_Sales_ddmmyy'.xlsb, so todays was called Daily_Sales_140218.xlsb
The code below loops though ther open workbooks until it finds the Daily_Sales file, I know to have that file open before I trigger the Procedure which pulls this file into my Master Workbook where the VBA is stored. As the Workbook Name changes each day, I've gone for the approach below as I can't hard code the Daily File name into the workbook.

Dim DHFile as Workbook 'Daily File
For Each wb in Application.Workbooks
[indent]If wb.Name Like "Daily Sales*" Then[/indent]
[indent]Set DHFile = wb[/indent]
[indent]End If[/indent]
Next wb

What could I add as a fail-safe incase the Daily File isn't open,
I've tried

If wb Is Nothing then Msgbox" The Daily Sales file must be opened before continuing":Exit Sub

but this runs to the Exit Sub even if the Daily_Sales file is open.

Thanks for any help

 

[SkipVought]

Hi

If wb.Name Like "Daily_Sales*" Then

Don’t forget the UNDERSCORE character.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[LGMan]

Hi Skip, I had to type this out manually, the code in my workbook runs as all the names are correct

 

[SkipVought]

You assume that we know what’s after the code you posted.

This is not the way I would do it.

1) you know the date so you know the name of todays’s file
“Daily_Sales_” & Format(Date, “ddmmyy”) & “.xlsb”

2) IMPORT the data via MS Query. Set up the QueryTable ONE TIME, and the programsticslly change the Connection string and refresh the query. Then copy the table to whereever in your workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Testing for [tt]Nothing[/tt]:

If [COLOR=#CC0000][b]DHFile[/b][/color] Is Nothing then Msgbox" The Daily Sales file must be opened before continuing":Exit Sub

combo

 

[SkipVought]

Good call, combo! Missed that one.

Gotta watch those shuffling shells carefully ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[LGMan]

Thanks Combo, it works like I hoped it achieve in the first place!


Skip, I take yout point that in this ocassion I would be able to use the day and use it in the filename, but there are temporary files that I download from the company IntranNet and they sometimes for reasons unknown have a load of guff added at the end of the filename.
Hence I wanted some code to apply to several data scrape processes. The trouble was that I should have said that in the first place. However your advice is good as always