none
VBA Code for saving daily Excel 2010 file under prior busniess days date RRS feed

  • Question

  • Hello,

    I'm trying to write a macro which will save a daily excel file out to SharePoint, saving as the prior business days date. Below is the code. I'm not getting an error message, however it's saving the file with the date 123099.

    Below is the code I've written, what am I doing wrong??

    Dim NetworkDays As Date

    ActiveWorkbook.SaveAs FileName:= _
            "https://one.site.companyname/sites/CarrierHandlingDatabase/Direct Bill CMS/Site 84 Cash File/Site 84 Cash Receipts File_" & Format(NetworkDays, "mmddyy") & ".xlsx" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False


    Lorac1969

    Thursday, April 30, 2015 2:12 PM

Answers

  • Hi, I think you are missing the part where you assign todays date to NetworkDays. So perhaps you would do something like this:
    Dim NetworkDays as Date
    
    'Set NetworkDays to be todays date minus one
    NetworkDays = date - 1
    
    'Take off a further one or two days if NetworkDays is now a 'Saturday or Sunday
    Select Case Weekday(NetworkDays)    
        Case vbSunday        
            NetworkDays= NetworkDays- 2    
        Case vbSaturday        
            NetworkDays= NetworkDays- 1    
    End Select

    • Marked as answer by Lorac1969 Wednesday, May 6, 2015 5:26 PM
    Wednesday, May 6, 2015 3:14 PM

All replies

  • Hi,

    You have declared NetworkDays as a Date but you haven't given it a value, so it is using the default 30-Dec-99.

    Thursday, April 30, 2015 2:22 PM
  • Hi Pete, as you can probably tell I'm a newbie to VBA. How do I set the value without referencing a specific date? I've tried adding <<<<   Set NetworkDays = DateValue("mmddyy")  >>>>> to the macro, but am getting a compile error/object required?

    Dim NetworkDays As Date

    Set NetworkDays = DateValue("mmddyy")


    ActiveWorkbook.SaveAs FileName:= _
            "https://one.companyname.net/sites/CarrierHandlingDatabase/Direct Bill CMS/Site 84 Cash Receipts/Site 84 Cash Receipts Report_" & Format(NetworkDays, "mmddyy") & ".xlsx" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False


    Lorac1969

    Thursday, April 30, 2015 3:52 PM
  • Hi,

    If you do simply want the previous day then 'NetworkDays = date - 1' will do it (no need for the 'set'). 'Date' is todays date. 

    If you need the last business day, there's lots of ways but generally, if you aren't considering public holidays then you just want to test whether the previous day is a Saturday or Sunday and deduct 1 or 2 further days. Perhaps something like

    Select Case Weekday(NetworkDays)    
        Case vbSunday        
            NetworkDays= NetworkDays- 2    
        Case vbSaturday        
            NetworkDays= NetworkDays- 1    
    End Select
    Hope that helps

    Pete

    Thursday, April 30, 2015 4:34 PM
  • Hi Pete,

    Now it's pulling the date 122999. Any ideas?


    Lorac1969

    Wednesday, May 6, 2015 3:08 PM
  • Hi, I think you are missing the part where you assign todays date to NetworkDays. So perhaps you would do something like this:
    Dim NetworkDays as Date
    
    'Set NetworkDays to be todays date minus one
    NetworkDays = date - 1
    
    'Take off a further one or two days if NetworkDays is now a 'Saturday or Sunday
    Select Case Weekday(NetworkDays)    
        Case vbSunday        
            NetworkDays= NetworkDays- 2    
        Case vbSaturday        
            NetworkDays= NetworkDays- 1    
    End Select

    • Marked as answer by Lorac1969 Wednesday, May 6, 2015 5:26 PM
    Wednesday, May 6, 2015 3:14 PM
  • Just to expand a little, you could have a function that returns the previous working day and use that:
    public Sub MainSub()
    
        Dim NetworkDays as Date
    
        NetworkDays = PreviousWorkingDay(date)
        'More stuff here around saving the file
    
    End sub
    
    
    
    Public Function PreviousWorkingDay(NetworkDays As Date) As Date
    
        'Set NetworkDays to be todays date minus one
        NetworkDays = NetworkDays - 1
        
        'Take off a further one or two days if NetworkDays is now a 'Saturday or Sunday
        Select Case Weekday(NetworkDays)
            Case vbSunday
                NetworkDays = NetworkDays - 2
            Case vbSaturday
                NetworkDays = NetworkDays - 1
        End Select
        PreviousWorkingDay = NetworkDays
    End Function

    Wednesday, May 6, 2015 3:29 PM
  • That worked perfectly!! Thanks for being so patient with a newbie :)

    Lorac1969

    Wednesday, May 6, 2015 5:25 PM