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

VBA: Loop does not include last 2 rows
How to use variable string in index formula in VBA?
Calculate SUM based on date
Need to transpose multiple excel rows to columns [duplicate]
Text to Time with Milliseconds
Exclude rows with formulas from range in macro
Formating excel percentage decimals
Excel VBA - Looking for Strings in “Text Block” , then look in the next
make one cell value 0 if other cell value > 0 using excel
Conditional AGGREGATE/Median in Excel 2010
Excel VBA filtering a column and then copying each item in the area collection to a matching row
Excel addition of multiple numbers in single cell
How do I autofill code in VBA?
Use excel VBA to update master list based on user input data
Change number format of a cell which inlude currecny using visual basic
Access reading Excel VBA date in US format

Categories

HOME
excel
github
rabbitmq
refactoring
wildfly
uwp-xaml
imageview
dropzone.js
reactor-netty
axis2
terraform
protege
delaunay
lokijs
snapshot
salt-stack
firebreath
smt
android-constraintlayout
react-boilerplate
image-comparison
cpu-registers
apprtcdemo
jquery-animate
packet-capture
jqxwidgets
face-api
actionlink
ebcdic
servicestack.redis
protobuf-c
slamdata
submenu
polymer-serve
lektor
outlook-web-app
youtube-analytics
google-nearby
meta-tags
fancytree
android-optionsmenu
libphonenumber
evosuite
xquery-sql
bandwidth
sqlitestudio
jedi-vim
lab-color-space
embedded-sql
prime-factoring
csla
fixed
istanbul
nslog
local-variables
helium
dbx
code-collaborator
metamodel
dde
owner
idfa
oql
jquery-ui-resizable
kdiff3
nscoding
listboxitem
ng-template
mongoose-web-server
singularitygs
android-context
mpi4py
merit-gem
ms-query
unique-constraint
smslib
cordova-chrome-app
lightroom
orchardcms-1.6
curator
zend-http-client
filefield
unassigned-variable
data-sharing
bonsaijs
graphael
activator
alassetslibrary
dynamic-html
contactscontract
filtered-lookup
hierarchyid
jnetpcap
payflowlink
c89
mbprogresshud
launching-application
mfi
ext-direct
google-ad-manager
actinic
in-app
background-music
dft
stack-unwinding
cck
blogml

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