Replace IF staement with string

[Dom606]

I have code behind a timesheet form that I want to modify. I currently have an IF statement that allows the strTemplatePath to save timesheets depending on which company work is being performed. I need a way to modify the strTemplatePath so that it uses the CompanyName along with the date and .xlsm when it saves.

I tried this but it does not work:
strTemplatePath = CurrentProject.Path & "\TimeSheetTemplate & CompanyName & .xlsm"

This is the current code using an IF statement

Private Sub subTransferDataToExcel()
    On Error Resume Next
    Dim sCriteria As String
    Dim db As Database
    Dim rst As Recordset
    Dim objApp As Excel.Application
    Dim objBook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    Dim strTemplatePath As String
    Dim newFile As String
    Dim fDt As String
    Dim strSavePath As String
    Dim strCompany As String
    

    sCriteria = " 1 = 1 "

    If CompanyName <> "" Then
        sCriteria = sCriteria & " AND qryTransferDataToExcel.CompanyName = """ & CompanyName & """"
    End If

    If BeginDate <> "" And EndDate <> "" Then
        sCriteria = sCriteria & " AND qryTransferDataToExcel.DateWorked between #" & Format(BeginDate, "dd-mmm-yyyy") _
                    & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
    End If

    Set db = CurrentDb()
    strCompany = CompanyName
    
    [highlight #FCE94F]If strCompany = "CPSI" Then
        strTemplatePath = CurrentProject.Path & "\TimeSheetTemplateCPSI.xlsm"

    Else:
        strTemplatePath = CurrentProject.Path & "\TimeSheetTemplateGLOBAL.xlsm"

    End If[/highlight]
    Set objApp = New Excel.Application
    Set objBook = objApp.Workbooks.Add(strTemplatePath)
   
 'Name of sheet you want to export to
    Set objApp = objBook.Parent
    Set objSheet = objBook.Worksheets("TimeSheet")
    objBook.Windows(1).Visible = True
   
 'Opens the recordset and sets the variable
    Set rst = db.OpenRecordset("SELECT [Time Card Hours].DateWorked, [Sum Of BillableHours] FROM qryTransferDataToExcel WHERE " _
                               & sCriteria, dbOpenSnapshot)
    With objSheet
        .Select
        'Clears the current contents in the workbook range
        .Range("C16:D22").ClearContents
        'rst Copies the recordset into the worksheet
        .Range("C16").CopyFromRecordset rst
    End With
    rst.Close
    objApp.Visible = True
  
   'Saves filename with the date value in C22
    fDt = Range("C22").Value
  
 'Change the date format
    newFile = "time sheet" & Format$(fDt, "mmddyyyy")
   
' Where to save the new workbook
    strSavePath = "C:\Documents and Settings\Dominic Fino\My Documents\CPSI\TimeBilling2010\TimeSheets\"
    ActiveWorkbook.SaveAs FileName:=strSavePath & newFile

    Set rst = Nothing
    Set db = Nothing
    Set objSheet = Nothing
    Set objBook = Nothing
    Set objApp = Nothing

End Sub

 

[SkipVought]

Hi,

strTemplatePath = CurrentProject.Path & "\" & TimeSheetTemplate & CompanyName & ".xlsm"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dom606]

Thanks Skip,
I tried that but it says TimeSheetTemplate not defined. If I put TimeSheetTemplate in quites it compiles but the result does not include the date.

 

[SkipVought]

So in your original question, you stated,

I tried this but it does not work:
strTemplatePath = CurrentProject.Path & "\TimeSheetTemplate & CompanyName & .xlsm"

The implication of having the ampersand between TimeSheetTemplate, CompanyName and .xlsm is that TimeSheetTemplate and CompanyName are both VARIABLES, but you never assign values AFAIK. That is why I posted my first suggestion.

So please explain what's going on!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dom606]

Each company has their own excel template with their logo. One is called TimeSheetTemplateCPSI.xlsm and the other is called TimeSheetTemplateGLOBAL.xlsm. When the data base is open time is recorded to each company seperatly. The variable CompanyName is either CPSI or GLOBAL.

What I am trying to accomplish is to Open the correct timesheet template where the CompanyName shown in the CompanyName variable is used to select the correct template.
Hope that makes sence.
Dom

 

[SkipVought]

    strTemplatePath = CurrentProject.Path & "\TimeSheetTemplateCPSI" & strCompany & ".xlsm"

then you don't need any if statement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dom606]

Thank you skip

 

[SkipVought]

Oops

strTemplatePath = CurrentProject.Path & "\TimeSheetTemplate" & strCompany & ".xlsm"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}