none
visual basic excel programming first attempt at a program need help

    Question

  • hi 

    I'm trying to create my first visual basic program in excel I'm trying to create a timesheet that will replicate it's self each year so I don't have to make the changes myself 

    anyway this is my code below it's not running for some reason 

    this is in the workbook  

    Private Sub OnTime1()

    'set timesheet to activate code in main

    'calls replication produre 
    'date numbers to replicate on

    Application.OnTime 39904, DateValue, 73141

    Dim x As Integer
    x = 365

    '365 days in a year

    Dim startdateto As Integer

    For startdateto = 39904 To 73141
    startdateto 39904 + x
    Next


    'trying to add 365 days till next run date
    Call main

    Do Until startdateto >= 73141
    If startdateto >= 73141 Then Exit Do

    End Sub

    and the rest is in a module 

    Sub main()

    'where all the code is called from
        
        create_directory
        save
        yearly_changes_to_timesheet
        save_as
        
    End Sub



    Sub create_directory()

    'make place for new timesheet
    'created by james payne

    Dim startdate As Integer

    For startdate = 2009 To 2100
    startdate 2009 + 1
    Next

    Do

    ChDir "G"

    ChDir ("timesheet startdate")
        Range("Parent_path").Value
        
        startdate = Range("start_variable").Value
        enddate = Range("end_varibale").Value
        
        Range("variable_part").Value = startdate
        
        For datebegin = startdate To enddate
        
        Mkdir_
        
        Range("new_directory").Value
        
        Range("variable_part").Value = Range("variable_part").Value + 1

        Next

    End Sub

    Sub save()
    'save master copy so the year_changes_to_timesheet are saved before preforming the next yearly_changes_to_timesheet sub
    'created by james payne

    ActiveWorkbook.save

    End Sub


    Sub yearly_changes_to_timesheet()

    'place to do changes to work sheet it's self
    'created by james payne

    Dim dayyears As Integer
    dayyears = 365

    Dim startdateto As Integer
    startdateto1 = 39904

    For startdateto = 39904 To 73141
    startdateto1 39904 + dayyears
    Next

    Cells(8, 1) = =TEXT(startdateto,"DD/MMMM/YYYY")

    Sub save_as()
    'new worksheet producted from master copy (which is updated each year)
    'created by james payne

    Dim start_workbook_names As Integer

     For start_workbook_names = 2009 To 2100
     start_workbook_names 2009 + 1
     Next
            
            ActiveWorkbook.SaveAs Filename:=timesheetstart_workbook_names.xls
            "C:\Documents and Settings\Games\Desktop\james documents\excel programming\timesheet start_workbook_names.xls" _
            , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
            
    End Sub

    any help would be much Apprehended
    Sunday, February 08, 2009 6:44 PM

Answers