Results 1 to 33 of 33

Thread: [RESOLVED] Save Workbook

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Resolved [RESOLVED] Save Workbook

    Hi,

    I wish to save down a particular workbook to a particular path with an exact name and yesterday's date.

    91 Daily Revenue 13 05 2019_KM

    I wish to save the file as the above, but with the current variables constantly updating.

    91 represents the current workday in the year and the date save down as the previous days date.

    My current code only saves a blank workbook in the below path.

    I searched all websites but couldn't find anything that matches my exact requirements.

    Sub SaveWorkbookSet()


    Dim wkb As Workbook
    Set wkb = Workbooks.Add


    wkb.SaveAs "N:\My Documents\Daily Revenue.xls"


    Worksheets("Control").Cells(31, 5) = Now

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Save Workbook

    The date for yesterday is not that hard.

    That's:
    Code:
    Dim dtYesterday As Date, sYesterday As String
    
    dtYesterday = Date - 1
    sYesterday = Format(dtYesterday, "DD MM YYYY")
    The number of workdays that's something different.
    What do you describe as the number of workdays?

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    he number of workdays depends on how many weekdays and holidays are counted
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    Workday represents the business day in the year.

    for example.

    Friday = 94
    Saturday = 0
    Sunday = 0
    Monday = 95
    Tuesday = 96
    etc etc

    I also want to save down the workbook to a particular path.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    as your holidays are different to mine and many others hard to answer

    from your other thread if the workdays are already in a cell just read from that

    Code:
    mypath = "N:\My Documents\ "
    myfilename = mypath  & range ("i8") & " Daily Revenue " & format(date, "dd mm yyyy") & "_KM.xls"
    debug.print myfilename
    wkb.SaveAs myfilename
    this is untested, so give it a try
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    I get a run time error 424 on this? :-(

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    I get a run time error 424 on this
    presumably because wkb is not assigned, it needs to be part of your original code
    you may also need to specify the worksheet containing the cell with workday
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    Nearly worked :-)

    Name:  Capture 2.JPG
Views: 830
Size:  11.2 KB

    Workday is missing before the word - Daily

    Also. Why does it only save down a blank workbook?

    I want to save down the whole work book I'm working from

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    (forgot to add this)

    Need the file name to look like this

    80 Daily Revenue 25 04 2019_KM

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    Also. Why does it only save down a blank workbook?
    try changing to
    Code:
    mypath = "N:\My Documents\ "
    wrkday =  80     ' you need to get this value from some source, see below
    myfilename = mypath  & wrkday & " Daily Revenue " & format(date, "dd mm yyyy") & "_KM.xls"
    debug.print myfilename
    thisworkbook.SaveAs myfilename
    as you already have a formula in some worksheet to calculate the work day number you could get the value from there, or duplicate the look up table and formula in this workbook then get the value from there, else you will need a function which considers an array all the holidays to return the number of working days
    there is a worksheetfunction that can help to do this
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    Hi,


    The workday is currently worked out via a formula in cell I8.

    How do I include this cell in the above code?

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    i made a function to return the number of workdays
    Code:
    Function workdays() As Integer
        hols = Array(CDbl(CDate("01/01/2019")), CDbl(CDate("19/04/2019")), CDbl(CDate("22/04/2019")), CDbl(CDate("06/05/2019")), CDbl(CDate("27/05/2019")), CDbl(CDate("26/08/2019")), CDbl(CDate("25/12/2019")), CDbl(CDate("26/12/2019")))
        workdays = WorksheetFunction.NetworkDays(CDate("01/01/2019"), Date, hols)
    End Function
    use like
    Code:
    mypath = "N:\My Documents\ "
    myfilename = mypath  & workdays & " Daily Revenue " & format(date, "dd mm yyyy") & "_KM.xls"
    debug.print myfilename
    thisworkbook.SaveAs myfilename
    as it is, the values in the array have to be updated every year, it should be possible to calculate the values for all years, easter being the most difficult and whether some other day is allowed as holiday when some proclaimed holiday falls on a weekend

    The workday is currently worked out via a formula in cell I8.

    How do I include this cell in the above code?
    you would put a reference to the range i8, as in post #5
    BUT if i8 is not on the active sheet you would need to qualify the range with the worksheet, like sheets("mysheet").range("i8")
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    OK that all worked. However it just renames the file I'm working on. How do I save down an additional file with the new filename as described in #10

    (Basically just want to copy the workbook I'm working on and save in a new folder with the above filename)

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    thisworkbook is always the workbook containing the code that is running

    i do not know if you already have a workbook object for the workbook you are working on, try changing to activeworkbook
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    I tried the code below....

    and it came back with an error message like this...

    ---------------------------
    Microsoft Visual Basic for Applications
    ---------------------------
    400

    Never seen this error message before.


    Sub Saveworkbookset()


    Dim wkb As Workbook
    Dim Wks As Worksheet
    Set wkb = Workbooks.Add

    myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
    Debug.Print myfilename
    ActiveWorkbook.SaveAs myfilename

    Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    and it came back with an error message like this...
    which line?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Save Workbook

    Code:
    Sub Saveworkbookset()
    
    
    Dim wkb As Workbook
    Dim Wks As Worksheet
    Set wkb = Workbooks.Add
    
    myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
    Debug.Print myfilename
    ActiveWorkbook.SaveAs myfilename
    
    Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")
    Again you are mixing implicit and explicit references.
    You start with:
    Code:
    Set wkb = Workbooks.Add
    wkb is then never used

    Next
    Code:
    ActiveWorkbook.SaveAs myfilename
    Another implicit reference.
    Should it be wkb.SaveAs myFilename ??

    Then out of nowhere
    Code:
    Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")
    Where Wks defined and assigned a value?

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    Sorry I'm bit of beginner at this and learning as I go along.


    Then out of nowhere
    Code:
    Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")
    Where Wks defined and assigned a value?


    What should it be instead?



    The reason I added active workbook because my original code was saving down a blank workbork. I wanted to create a copy of the work book I was working from and save it down to a particular path.

    Are you saying this part of code will work in stead?


    Next
    Code:
    ActiveWorkbook.SaveAs myfilename
    Another implicit reference.
    Should it be wkb.SaveAs myFilename ??

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    Should it be wkb.SaveAs myFilename ??
    NO, that would still be an empty workbook

    Wks("Control").Cells(30, 5) = Format(Now, "dd/mm/yy hh:nn:ss")
    i could see this causing an error, but not the error yo specify

    you still did not tell which line the error happens
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  20. #20

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    Quote Originally Posted by westconn1 View Post
    NO, that would still be an empty workbook

    i could see this causing an error, but not the error yo specify

    you still did not tell which line the error happens


    Its bizarre. I run the code and it just breaks. It doesn't highlight the line it breaks on. Has that happened before?

  21. #21
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    Has that happened before?
    not uncommon in VBA, you need to step through code line by line til you find the line that errors
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    Code is breaking with a run time error '1004

    Application-defined or object defined error

    Breaks on this line....ActiveWorkbook.SaveAs myfilename

    I forgot to mention that I don't want to change the name of my current file. Only the file that I'm saving down in to my folder.


    Sub Saveworkbookset()


    Dim Thiswb As Workbook
    Set Thiswb = ActiveWorkbook
    Workbooks.Add

    myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
    Debug.Print myfilename
    ActiveWorkbook.SaveAs myfilename

  23. #23
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Save Workbook

    What is the content of filename as printed in the Debug.Print ??

  24. #24
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    once you saveAs that is then the current file (active workbook) the file before saving is closed without changes

    maybe you want saveCopyAs

    myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
    if the current file is not an .xlsm, that could cause an error, all previous indications showed the file to .xls, if you want to change the file type yo would need to specify the file type
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  25. #25

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    Hello.

    Apologies for late replay.

    Changed the code to the below and still not working.

    Error message on last line.. Run time error 1004.

    Still not saving a copy of the work book to the drive I want. :-(


    Sub Saveworkbookset()


    Dim Thiswb As Workbook
    Set Thiswb = ActiveWorkbook
    Workbooks.Add

    myfilename = mypath & wrkday & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
    Debug.Print myfilename
    ActiveWorkbook.SaveCopyAs


    End Sub

  26. #26
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    Debug.Print myfilename
    as posted in #23 what is the result in the immediate window?

    see the note in the previous post about the file type!!
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  27. #27

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    Quote Originally Posted by westconn1 View Post
    as posted in #23 what is the result in the immediate window?


    Sorry. Don't know what you mean by immediate window?

    see the note in the previous post about the file type!!
    Current file is .xlsm and I want to save as a .xls

  28. #28
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    Code:
    Current file is .xlsm and I want to save as a .xls
    then you need to specify the file type when saving

    if it was working the code as posted in #25 would try to save an empty workbook with no file name specified and no path information in the filename variable, that is not used
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  29. #29

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    ok I've been testing this code....


    Sub Savev1()

    Dim Thiswb As Workbook
    Set Thiswb = ActiveWorkbook
    Workbooks.Add

    myFilename = myPath & Range("A1").Value & " Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm"
    ActiveWorkbook.SaveCopyAs Filename:=myFilename


    End Sub

    But it just opens a new work... Why doesn't it save it with the specified filename?

  30. #30
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    i tested your exact code, it opened a new workbook, then saved the copyas file (" Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm" with leading space as specified in the code) into documents folder (default folder, for me, when path not supplied), exactly as specified in the code
    in this case, both mypath and range A1 are empty values

    i tested in excel 2010 windows 10
    probably for some reason you think the code should be doing something different to this

    also as mentioned above if you saveAs or saveCopyas without specifying the file type it will save a .xlsx or .xlsm, regardless of if .xls is used in filename, this will give warning whenever the file is opened
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  31. #31

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook


    Ok sorry there was a typo in my coding.

    I can now see a file saved down in my folder. However its still saving down an empty work book. Do I need to specify what worksheets to save?

    Also, as well as saving down a file to the folder, it also opens a new blank workbook. Do you know why its doing this?


    Quote Originally Posted by westconn1 View Post
    i tested your exact code, it opened a new workbook, then saved the copyas file (" Daily Revenue " & Format(Date, "dd mm yyyy") & "_KM.xlsm" with leading space as specified in the code) into documents folder (default folder, for me, when path not supplied), exactly as specified in the code
    in this case, both mypath and range A1 are empty values

    i tested in excel 2010 windows 10
    probably for some reason you think the code should be doing something different to this





    How do I get around the warning message?

    also as mentioned above if you saveAs or saveCopyas without specifying the file type it will save a .xlsx or .xlsm, regardless of if .xls is used in filename, this will give warning whenever the file is opened

  32. #32
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save Workbook

    Do I need to specify what worksheets to save?
    NO, you just added a workbook then save so the empty workbook is the active book, i have no idea why you add a workbook as part of the code

    it also opens a new blank workbook
    remove the line that adds the empty workbook


    How do I get around the warning message?
    specify the file format when saving, to match the extension, just do a search on excel fileformat to get the list of constants
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  33. #33

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save Workbook

    Ok finally got it sorted. Thank you for your patience and help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width