Solved

VBA  one liner

Posted on 2014-09-24
4
225 Views
Last Modified: 2014-09-25
Hello,
There will always be one file in a folder. The only thing is that the file will have a date stamp in it from day to day. What is one liner code in vba that  lets me set an object to that one and noly file?
Like
Set wb = folder.files(*.xls)
0
Comment
Question by:Rayne
  • 2
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40343258
Here you go:
Set wb = Application.Workbooks.Open(strPath + Dir(strPath))

You can either assign strPath to a string that represents the full drive\directory path or replace 'strPath" with that path twice.  

Personally, I prefer assigning the path to a variable to keep the code simpler, otherwise you get something like:
   Set wb = Application.Workbooks.Open (Filename:="C:\Folder1\SubFolder2\SubSubFolder3\singlefilefolder\" + Dir("C:\Folder1\SubFolder2\SubSubFolder3\singlefilefolder\"))

nasty...

Regards,
-Glenn
0
 

Author Comment

by:Rayne
ID: 40343842
thank you Glenn :)
I did it this way

strpath = "C:myfolder\thisFolder\\"
        Set wb = Application.Workbooks.Open(strpath + Dir(strpath & "*.xlsx"))

Looks ok to you?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40343922
1) remove the extra backslash for strpath assignment
2) Dir(strpath) should return the full filename of the single file in the folder; no need to add the "*.xlsx" part

-Glenn
0
 

Author Comment

by:Rayne
ID: 40344066
thnak you Glenn :)
I will fix it
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
Outlook Free & Paid Tools
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question