excel


How to exclude weekends while opening file in VBA?


everyday one file will create with date as previous working day.
Example, (05/16/2017, Tuesday) file will create with date as 05/15/2017, Monday.
whereas on 05/15/2017, Monday file will create with date as 05/12/2017.
I am trying to open file using VBA with the below code,
file name Format="C:\users\Duke\report'05/15/2017'.XLS"
sub OpenFile()
Const fpath As String = "C:\users\Duke\Report"
Dim fname As String
fname = Format(Date - (Weekday((Date),Vbmonday) - 1), "yyyy-mm-dd")
fname = "'" & fname & "'" & ".XLS"
Dim path As String
path = fpath & fname
end ()
But it is not working, Please suggest.
VBA does not have a built in method to handle workdays. But the following might compute the desired date, if your code only runs M-F and SS are weekends:
Dim X As Long
X = IIf(Weekday(Date) = vbMonday, 3, 1)
fname = Format(Date - X, "yyyy-mm-dd")
If your code might also run on the weekends, then you will have to define what you want to happen, in that event

Related Links

Find average of top and bottom n percent of range with extra condition
Sorting only positive numbers – Largest to Smallest & Sorting only negative numbers – Smallest to largest in excel
VBA Data Download From Web w/out Affecting Other Tasks?
Implement Paste Link for this code
Code to Combine Sheets Does Not Finish Combining
select data of one column on basis on another column in excel
In a multi-sheet workbook, how do I code a multi-column sort in VBA?
How to merge multiple workbooks into one based on workbooks names
Changing range in a lookup formula based upon a value
VBA error on “Loop Until” statement
Possible to use QUARTILE function over a conditional range in Excel? [closed]
How to transpose the value in excel based on condition?
“Cannot insert object” Error when inserting PDF files/objects into Microsoft Excel
Referencing the selection in deactivated Workbook
Excel - Export cells identified by countif to new file
Attempting to search for strings and having syntax errors

Categories

HOME
iframe
less
g2o
houndify
put
sage
quandl
soundcloud
c#-3.0
fluent-assertions
worksheet-function
firebreath
jquery-gmap3
rinsim
4d-database
pyside
spring-statemachine
build-automation
nattable
fork
amazon-lightsail
gembox-document
actionlink
eureka-forms
fxml
vim-syntax-highlighting
openfiledialog
street-address
polymer-serve
azure-virtual-machine
responsive-filemanager
bootstrapping
google-tv
webmin
envelope
smartassembly
bspline
3d-modelling
scalacheck
ranorex
burn
wistia
xquery-sql
javac
proof-of-correctness
wso2-appm
shared-ptr
equation-solving
opensmtpd
dnspython
embedded-sql
erlang-shell
c#-to-vb.net
synopsys-vcs
cscore
dbx
sequencing
htsql
audacity
spoon
rouge
lunrjs
xcode4
sonarqube-5.4
http-accept-header
bullseye
xcode-bots
adobe-dps
xmlpullparser
protractor-net
approximation
wunderlist
allocation
magnetometer
sapb1
navigationbar
avaloq-script
ember-qunit
ransac
nxt-python
numpad
sine-wave
bonjour
raii
linq-to-lucene
updatemodel
qglviewer
inter-process-communicat
stencils
dbase
fubumvc
mat-file
tandem
set-theory
dotfuscator
visual-assist-x
nsformatter
wind-river-workbench
fibonacci-heap
trackback
maintaining-code
zero-pad

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App