Results 1 to 5 of 5
  1. #1
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402

    How to use a sub procedure

    Hi all,

    I am trying to limit using the same similar code by putting the common code in a sub procedure, when i do this i get an error.

    the code below is the start of the excel sheet creation, I will have several different ways i need to do this,
    and then run the main code, and then finish with code for each type of sheet required

    i hoped it was just a matter of moving the code to a sub procedure and calling it from were it was cut from.
    This starts to work but as soon as it gets to the variable gg it fails with invalid procedure call or argument.

    how can i increase the scope of the variables into this sub test1. there is more than just the gg variable.

    SAMPLE ONLY, not all code shown.

    Private Sub Command10_Click()
    Dim strPath As String
    Dim rst As DAO.Recordset
    Dim stDocName As String
    Dim Excel_Application As Excel.Application
    Dim Excel_Workbook As Excel.Workbook
    Dim Current_Worksheet As Excel.Worksheet
    Dim Data_Range
    Dim Worksheet_Name
    Dim db As Database
    Dim rs As Recordset
    Dim headercell, firstcellref, nextname, FirstName, linecount, nextcellref, bb, CC, dd, rangestart, rangeend, Mt
    Dim aa As Integer

    nRecords = DCount("*", "Employee Time Report Output with lunch")
    If nRecords = 0 Then
    MsgBox "No data for selected period, change report dates and try agian.", vbOKOnly, "Error"
    Exit Sub
    End If
    sdt = Format(start_date, "dd-mm-yy")
    edt = Format(End_date, "dd-mm-yy")
    If IsNull(Me.employee_selected) Then
    t = MsgBox("Please select an Employee First", vbOKOnly, "Missing Information")
    Exit Sub
    Else
    xx = Me.employee_selected
    fn = DLookup("[first name]", "[employees]", "[barcode] = " & Me.employee_selected)
    Ln = DLookup("[last name]", "[employees]", "[barcode] = " & Me.employee_selected)
    ns = DLookup("[Normal Start Time]", "[employees]", "[barcode] = " & Me.employee_selected)
    ne = DLookup("[Normal End Time]", "[employees]", "[barcode] = " & Me.employee_selected)
    End If
    gg = "C:\aaa\Employee Time Report for - " & fn & ", " & Ln & ", " & sdt & " to " & edt & ".xls"

    t = Len(Dir(gg))
    If t = 0 Then
    GoTo keepgoing1
    Else
    t = MsgBox("File already exists, Delete file and continue ?.", vbYesNo, "")
    If t = vbYes Then
    Kill
    gg
    Else
    Exit Sub
    End If
    End If
    keepgoing1:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch",
    gg, True

    call test1

    End Sub


    sub test1()
    Set Excel_Workbook = GetObject(
    gg)
    Set Excel_Application = Excel_Workbook.Parent
    Excel_Application.WindowState = xlMinimized


    Excel_Application.Visible = True
    Excel_Workbook.Windows(1).Visible = True
    Excel_Workbook.Worksheets(1).Name = "Employee Time Report"
    Set Current_Worksheet = Excel_Workbook.Worksheets("Employee Time Report")
    Excel_Workbook.Worksheets("Employee Time Report").Select
    Excel_Workbook.Worksheets("Employee Time Report").Tab.ColorIndex = 37
    Set Current_Worksheet = Excel_Workbook.Worksheets("Employee Time Report")
    Excel_Workbook.Worksheets("Employee Time Report").Select
    Excel_Application.FormulaBarHeight = 1
    Current_Worksheet.Cells.Select
    With Selection
    Current_Worksheet.Cells.HorizontalAlignment = xlRight
    Current_Worksheet.Cells.Font.Name = "Times New Roman"
    End With
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Pass values to the procedure with arguments:

    Sub test1(gg As String)
    ...
    End Sub

    Call the procedure:

    test1("workbook file path/name here")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Thanks for the reply, I will try to do that, if I don't get it going I may just go back to using GoTo and Return.

  4. #4
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    I tried your suggestion, as below,
    the line Sub Main_Code(gg As String) returns - Object required, I guess it can't see the excel application link out of the main procedure. I am trying this because I have to have 3 types of reports - Blank, Manual selection or All, seemas like to much work if know i can do it with goto and return in the same procedure, I'll worry about subs if i really have to. unless it can be done quick i don't have time at the moment to learn how.
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch", gg, True
    On Error GoTo 0
    Set Excel_Workbook = GetObject(gg)
    Set Excel_Application = Excel_Workbook.Parent
    Excel_Application.WindowState = xlMinimized
    Excel_Application.Visible = True
    Excel_Workbook.Windows(1).Visible = True
    Excel_Workbook.Worksheets(1).Name = "Employee Time Report"
    Set Current_Worksheet = Excel_Workbook.Worksheets("Employee Time Report")
    Excel_Workbook.Worksheets("Employee Time Report").Select
    Excel_Workbook.Worksheets("Employee Time Report").Tab.ColorIndex = 37
    Set Current_Worksheet = Excel_Workbook.Worksheets("Employee Time Report")
    Excel_Workbook.Worksheets("Employee Time Report").Select
    Excel_Application.FormulaBarHeight = 1

    Call Main_Code("C:\aaa\Employee Time Report for - " & fn & ", " & Ln & ", " & sdt & " to " & edt & ".xls")

    Excel_Workbook.SaveAs "C:\aaa\Employee Time Report for - " & fn & ", " & Ln & ", " & sdt & " to " & edt & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Kill "C:\aaa\Employee Time Report for - " & fn & ", " & Ln & ", " & sdt & " to " & edt & ".xls"
    End Sub

    '************************************************* ************************************************** ********************************************
    Sub Main_Code(gg As String)

    Current_Worksheet.Cells.Select
    With Selection
    Current_Worksheet.Cells.HorizontalAlignment = xlRight
    Current_Worksheet.Cells.Font.Name = "Times New Roman"
    End With


    code continues...

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It is possible to pass objects in arguments but I've never tried that with Excel.

    Why open the Excel app and workbook objects in the first procedure? Open them in the Main_Code sub. Pass as many arguments as needed to do whatever with each of the 3 types.

    Sub Main_Code(gg As String, tt As String)
    'code that sets Excel app and workbook objects
    If tt = "Blank" Then
    'do this code
    ElseIf tt = "Manual" Then
    'do this code
    ElseIf tt = "All" Then
    'do this code
    End If
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. log in procedure
    By pedjvak in forum Programming
    Replies: 1
    Last Post: 04-16-2013, 11:24 AM
  2. create Procedure
    By rsl_mov in forum Programming
    Replies: 1
    Last Post: 08-07-2012, 10:40 AM
  3. After_Update Procedure
    By Nad_user in forum Programming
    Replies: 3
    Last Post: 04-29-2010, 05:06 AM
  4. How to run VBA procedure
    By bkelly in forum Programming
    Replies: 8
    Last Post: 09-26-2009, 06:08 PM
  5. how to call a sub procedure?
    By dollygg in forum Access
    Replies: 1
    Last Post: 08-18-2009, 05:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums