+ Reply to Thread
Results 1 to 10 of 10

Error in code run a macro once a day

  1. #1
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Error in code run a macro once a day

    Dear Friends,
    I want the macro to run once daily when the workbook is open, no matter what time it is. The format of date should be 'dd mm yyyy'.

    Please correct my code to run a macro once a day.

    Thanking you in anticipation.

    Mukesh
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Error in code run a macro once a day

    Using B2 Cell as Helper cell

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Error in code run a macro once a day

    Hi, Mukesh ,

    a different approach which will store the last date the macro was run in a Name and compare that value on opening with the actual date to decide whether to run the macro or not.

    ThisWorkbook:
    Please Login or Register  to view this content.
    Module1:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Attached Files Attached Files
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Error in code run a macro once a day

    Good morning Sixthsense ,
    Thank you for solution. I am learning a lot of macro coding from you experts. It's working fine but I didn't understand the logic behind this code so that I can use it in my other files also. I tried to use it in my file by changing some code but it's not working. Please tell me the logic behind it to use it by myself in other programs easily.

    I'm attaching a sample file for your reference.

    Do guide me to write such code independently in future.

    Thank you.

    Mukesh
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Error in code run a macro once a day

    Good morning HaHoBe,
    Excellent! It's a new approach. It's working fine but I want to learn the code so that I can use it in other files independently. Please explain me how to use this code in other files and what change is required. That means by simply changing few lines it's applicable for all files.

    The code is very complicated so I didn't understand it but it's working fine.

    Thank you for solution. Please refer my attached file and guide me how to use it in that file. I'm learning a lot of macro codes from this forum and you experts.

    Thank you.

    Mukesh

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Error in code run a macro once a day

    Hi, Mukesh,

    most of what the code does is written in the comments to the Workbook_Open. So in order for the example to work the name should be listed in the names of the workbook and filled with a serial value less than today, the Workbook_Open-code must be placed in ThisWorkbook, the function to check whether the name is listed in the Names has to be placed in a standard module, and the name of the procedure to run has to be amended to suit and that procedure should be placed in a standard procedure as well.

    Ciao,
    Holger

  7. #7
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Error in code run a macro once a day

    Good morning Sixthsense ,,
    I tried to use your suggestion in my file but it is giving error 1004. Please correct my error and guide me how to avoide this error in future. Plese se the attached file.

    Mukesh
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Error in code run a macro once a day

    Good morning HaHoBe,
    Thank you for your guidance. Your code is very difficult for me to understand. Do compile it in my file attached and I will try to learn what change you have made to work it correctly. Also give some tips to understand it better.

    What is a meaning of 'holger' which is at the bottom of your mail. Is it a German word? It's just for curiosity only.

    Thanking you in anticipation.

    Mukesh
    Attached Files Attached Files

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Error in code run a macro once a day

    Hi, Mukesh ,

    I psoted the code as well where that code is supposed to go. You would need to alter the code line in Workbook_Open-code
    Please Login or Register  to view this content.
    to suit to your needs and make sure that this macro mentioned there is known for Excel VBA in the workbook (declare it as a Public Sub in a standard module).

    Any standard module must hold the Function used in order to work.

    If you save that workbook after inserting the codes and open it again the date to check will be the same as the one stored in the Name (unless you change it to say =41450 (Date of 25.06.2013) as opposed to show =41451 (Date of 26.06.2013)). So if no change has been made to the date on opening no execution of the macro code to call will be done. There isnīt even the need to implement the Name in the workbook as the Workbook_Open-event will do that if the name isnīt found.

    If you want the code to work on the first opening after implementation as well please change the code line
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    in order to insert yesterdayīs date there.

    What is a meaning of 'holger' which is at the bottom of your mail.
    Isnīt that a post as opposed to a mail? Itīs my FirstName.

    Ciao,
    Holger

  10. #10
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Error in code run a macro once a day

    Good morning Holger,
    Thank you for your guidance. I will go through it and create a macro.

    Thank you once again.

    Mukesh

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1