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