?
Solved

VBA  one liner

Posted on 2014-09-24
4
Medium Priority
?
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

777 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