ProperCase

[D Willett]

Hi. Could do with a little help with my VBA code.
I need the created folder and report to be propercase, its just a cosmetic fad of mine !!:

Code: Select all

Private Sub cmdUploadFTP_Click()
If strRole <> "SuperUser" Then
MsgBox "Only Super Users Have Access This Function", , "Restricted"
Exit Sub
End If

If Len(Dir("L:\SW-Tables\Reports\" & Me.BrowserTitle, vbDirectory)) = 0 Then
   MkDir "L:\SW-Tables\Reports\" & Me.BrowserTitle
End If

If FileExists("C:\accdata.html") Then
    
       FileCopy "C:\accdata.html", "L:\SW-Tables\Reports\" & Me.BrowserTitle & "\" & Me.BrowserTitle & "-" & Format(Me.txtStartDate, "dd-mmm-yyyy") & " To " & Format(Me.txtEndDate, "dd-mmm-yyyy") & ".html"
 

      MsgBox "Report Uploaded", , "Success"
    
    Else
    MsgBox "No Report to Upload", , "Error"
    Exit Sub
    End If

End Sub

[Rudi]

I'm no expert with VBA for Access, but give this a try..
Just my

Code: Select all

Private Sub cmdUploadFTP_Click()
    If strRole <> "SuperUser" Then
        MsgBox "Only Super Users Have Access This Function", , "Restricted"
        Exit Sub
    End If

    If Len(Dir("L:\SW-Tables\Reports\" & Me.BrowserTitle, vbDirectory)) = 0 Then
        MkDir "L:\SW-Tables\Reports\" & StrConv(Me.BrowserTitle, vbProperCase)
    End If

    If FileExists("C:\accdata.html") Then
        FileCopy "C:\accdata.html", "L:\SW-Tables\Reports\" & Me.BrowserTitle & "\" & _
            StrConv(Me.BrowserTitle & "-" & Format(Me.txtStartDate, "dd-mmm-yyyy") & _
            " To " & Format(Me.txtEndDate, "dd-mmm-yyyy"), vbProperCase) & ".html"
        MsgBox "Report Uploaded", , "Success"
    Else
        MsgBox "No Report to Upload", , "Error"
        Exit Sub
    End If
End Sub

[HansV]

Or try this, it uses strConv only once.

Code: Select all

Private Sub cmdUploadFTP_Click()
    Dim strTitle As String

    If strRole <> "SuperUser" Then
        MsgBox "Only Super Users Have Access This Function", , "Restricted"
        Exit Sub
    End If

    strTitle = StrConv(Me.BrowserTitle, vbProperCase)
    If Len(Dir("L:\SW-Tables\Reports\" & strTitle, vbDirectory)) = 0 Then
        MkDir "L:\SW-Tables\Reports\" & strTitle
    End If

    If FileExists("C:\accdata.html") Then
        FileCopy "C:\accdata.html", "L:\SW-Tables\Reports\" & _
            strTitle & "\" & strTitle & "-" & _
            Format(Me.txtStartDate, "dd-mmm-yyyy") & " To " & _
            Format(Me.txtEndDate, "dd-mmm-yyyy") & ".html"
        MsgBox "Report Uploaded", , "Success"
    Else
        MsgBox "No Report to Upload", , "Error"
    End If
End Sub

[D Willett]

Cheers guys. Problem solved.

Kind Regards