How to convert a command click event into a macro

[rollingzep]

Hi,

I have a command click event which imports all Outlook attachments into a shared folder and works fine.
But I need to schedule this as a job on a Task Scheduler to Import before Users log before 9.00 am.
I think an autoexec macro is needed for the Task Scheduler to trigger the import. I am not sure.
How to convert my command click event code into a macro?
I know that the code is to be as a function to be run from a macro.
But not sure how to convert the click event code as a function.

Private Sub cmdOutlook_Click()
Dim olApp As Object
Dim MYFOLDER As Object
Dim OlItems As Object
Dim OlMail As Object
Dim x As Integer
Dim strFile As String
Dim strFolderpath As String

Set olApp = GetObject(, "Outlook.Application")
If Err.Number = 429 Then
    Set olApp = CreateObject("Outlook.Application")
End If

strFolderpath = "C:\Users\pg\Testing"
'On Error Resume Next

' Set the Attachment folder.
strFolderpath = strFolderpath & "\Attachments\"

    Set MYFOLDER = olApp.GetNamespace("MAPI").Folders("WeeklyProceedings Mailbox").Folders("Inbox")
     Set OlItems = MYFOLDER.Items

    For Each OlMail In OlItems
        strFile = OlMail & ".XML"
        strFile = strFolderpath & strFile
        If OlMail.Attachments.Count > 0 Then
            For x = 1 To OlMail.Attachments.Count
                OlMail.Attachments.item(x).SaveAsFile strFile
            Next x
        End If
    Next
    
Set MYFOLDER = Nothing
Set OlMail = Nothing
Set OlItems = Nothing
Set olApp = Nothing
End Sub

TIA

 

[welshgasman]

I just used to run a function that called the sub?

Sub CreateBackup(Optional strDBType As String)
    Dim strDBpath As String, ext As String, tmp As String
    Dim strPath As String, strBackupPath As String, strDB As String
    
    
    'tmp = CurrentDb.Name    'or maybe this should be the name of your BE
    'strDBType = "FE"
    strDBpath = GetAccessBE_PathFilename("tbl-version_fe_master")
    strPath = Left(strDBpath, InStrRev(strDBpath, "\"))
    strBackupPath = strPath & "Backup\"
    
    'Will now backup front and back end database
    If strDBType = "FE" Then
        strDBpath = CurrentDb.Name
    End If
    strDB = Right(strDBpath, Len(strDBpath) - InStrRev(strDBpath, "\"))
    
    With CreateObject("Scripting.FileSystemObject")
        'ext = "." & .GetExtensionName(tmp)
        tmp = strBackupPath & Format(Now(), "yyyymmdd_hhnnss") & "_" & strDB
        .CopyFile strDBpath, tmp
    End With
    MsgBox strDBType & " Database saved as " & tmp
    
    
End Sub

Function CreateBackupFE()
' Have to do it this way as Switchboard does not allow parameters.
CreateBackup ("FE")
End Function
Function CreateBackupBE()
' Have to do it this way as Switchboard does not allow parameters.
CreateBackup ("BE")
End Function

 

[rollingzep]

I just used to run a function that called the sub?

Sub CreateBackup(Optional strDBType As String)
    Dim strDBpath As String, ext As String, tmp As String
    Dim strPath As String, strBackupPath As String, strDB As String
   
   
    'tmp = CurrentDb.Name    'or maybe this should be the name of your BE
    'strDBType = "FE"
    strDBpath = GetAccessBE_PathFilename("tbl-version_fe_master")
    strPath = Left(strDBpath, InStrRev(strDBpath, "\"))
    strBackupPath = strPath & "Backup\"
   
    'Will now backup front and back end database
    If strDBType = "FE" Then
        strDBpath = CurrentDb.Name
    End If
    strDB = Right(strDBpath, Len(strDBpath) - InStrRev(strDBpath, "\"))
   
    With CreateObject("Scripting.FileSystemObject")
        'ext = "." & .GetExtensionName(tmp)
        tmp = strBackupPath & Format(Now(), "yyyymmdd_hhnnss") & "_" & strDB
        .CopyFile strDBpath, tmp
    End With
    MsgBox strDBType & " Database saved as " & tmp
   
   
End Sub

Function CreateBackupFE()
' Have to do it this way as Switchboard does not allow parameters.
CreateBackup ("FE")
End Function
Function CreateBackupBE()
' Have to do it this way as Switchboard does not allow parameters.
CreateBackup ("BE")
End Function

I am not sure how to convert my code into a function. What would be the parameters?

 

[Bosquedeguate]

Why does it need to be a Function?
Macros are generally Subs

 

[Micron]

Macros in Excel are like procedures in Access. An Access macro is nothing like an Excel "macro". I prefer to refer to vba code as a procedure regardless of which application it is contained in. Procedures can be a Sub or Function and there are reasons to use one or the other.

Now that I'm off my soap box, the problem with an AutoExec (Access) macro is that it runs when the db opens. In that case, if it's the same db file for users and Task Scheduler, when TS opens the db the AutoExec will run, but everyone who opens the db at 9:00 AM and every time after will cause it to run, so not so good. One thing you can do is have TS use a shortcut and the command line for the shortcut can contain command line switches. A useful switch is the cmd (command) switch, which will pass whatever the switch value is to the db being opened. In your db startup code you can check for the existence of the db Command property. If it is non-existent, then a user has opened the db. If it is there, then TS has opened the db. You then code to do this and not that accordingly.

The only rub is that TS must use a shortcut, which is not so bad because users would not be using it to open the db. You'd store the shortcut file in a different directory to prevent that. All the above is based on methods that I used in the past to auto update db tables before users arrived at work.

 

[welshgasman]

I am not sure how to convert my code into a function. What would be the parameters?

That is the point, you do not convert your sub.
You create a function that calls the sub.

DId you even look at the code?

I needed a parameter so I could back up either the FE or BE with the same code. You likely do not need any parameter?

 

[Bosquedeguate]

You apparently do not need anything returned to you, so a Function is not necessary (so it appears).

So you could write two Subs that call your backup Sub

Sub CreateBackupBE()
   CreateBackup("BE")
End Sub

Sub CreateBackupFE()
   CreateBackup("FE")
End Sub

Using welshgasman's Sub CreateBackup from prior post.

 

[Micron]

I am not sure how to convert my code into a function.

At the risk of repeating the question again I'll also ask why you think you need a function? There are cases where you need a function even if it doesn't return anything but I'm not seeing any sign of those cases here.

 

[welshgasman]

You apparently do not need anything returned to you, so a Function is not necessary (so it appears).

So you could write two Subs that call your backup Sub

Sub CreateBackupBE()
   CreateBackup("BE")
End Sub

Sub CreateBackupFE()
   CreateBackup("FE")
End Sub

Using welshgasman's Sub CreateBackup from prior post.

The RunCode option of the switchboard form will not run a sub, has to be a function?

 

[rollingzep]

The RunCode option of the switchboard form will not run a sub, has to be a function?

Thanks Guys for the pointers. I renamed the sub as a function in a module used the RunCode macro.