Solved

VBA  one liner

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

831 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