×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Checking that an external file is open

Checking that an external file is open

Checking that an external file is open

(OP)
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.

CODE

Dim DHFile as Workbook 'Daily File
For Each wb in Application.Workbooks
If wb.Name Like "Daily Sales*" Then
Set DHFile = wb
End If
Next wb
What could I add as a fail-safe incase the Daily File isn't open,
I've tried

CODE

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

RE: Checking that an external file is open

Hi

CODE

If wb.Name Like "Daily_Sales*" Then 

Don’t forget the UNDERSCORE character.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Checking that an external file is open

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

RE: Checking that an external file is open

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Checking that an external file is open

Testing for Nothing:

CODE -->

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

combo

RE: Checking that an external file is open

Good call, combo! Missed that one.

Gotta watch those shuffling shells carefully winky smile

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Checking that an external file is open

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close