Solved

VBA  one liner

Posted on 2014-09-24
4
232 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 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

688 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