Code to replace SendKeys in My Code

**jforbes** · Jun 23 '16, 02:15 PM

What is the message that is popping up that requires the SendKeys?

I do something very similar and do not get a message that requires sendkeys.

Here is an example of some of the code with most of the non-important code stripped out. Maybe it can help determine the cause of the Message:

Code:

Public Function generateDrawingBOMPDF(ByRef sDrawingName As String, ByRef sJobNumber As String) As String
    
    Dim sFileName As String
    Dim sReportName As String
    Dim sDateTime As String
    Dim sTempDirectory As String
    
    sDateTime = getStringDateTime()
    sFileName = sDrawingName & "_" & sJobNumber & "_" & sDateTime & ".PDF"
    sReportName = "JobDrawingBOM"
    sTempDirectory = getApplicationDirectory() & "\Temp\"
    
    ' Create BOM
    DoCmd.OpenReport sReportName, acViewPreview, , "JobNumber='" & sJobNumber & "' AND DrawingName='" & sDrawingName & "'"
    DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sTempDirectory & sFileName, True
    DoCmd.Close acReport, sReportName
    
End Function

**DJRhino** · Jun 23 '16, 03:10 PM

Its a pop up box that asks me if I want to overwrite the file that is there, which I do everytime.

**jforbes** · Jun 23 '16, 05:26 PM

If you know you are going to overwrite the output, you could delete it, if it exists, before attempting to create the .PDF:

Code:

If fileExists(sFileName) Then Kill sFileName

The above will wipeout the file in the sFileName variable without so much as a how do you do, so be careful with the Kill method. The above also uses this cool function by Allen Browne. There are other ways of doing this, but this function works quite well:

Code:

Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
    'Purpose:   Return True if the file exists, even if it is hidden.
    'Arguments: strFile: File name to look for. Current directory searched if no path included.
    '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
    'Note:      Does not look inside subdirectories for the file.
    'Author:    Allen Browne. http://allenbrowne.com June, 2006.
    Dim lngAttributes As Long

    'Include read-only files, hidden files, system files.
    lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

    If bFindFolders Then
        lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
    Else
        'Strip any trailing slash, so Dir does not look inside the folder.
        Do While Right$(strFile, 1) = "\"
            strFile = Left$(strFile, Len(strFile) - 1)
        Loop
    End If

    'If Dir() returns something, the file exists.
    On Error Resume Next
    FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function

Function FolderExists(strPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function

Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

The only thing that I don't fully understand with your code is the CodeContextObje ct. It actually makes me a little nervous.

**DJRhino** · Jun 23 '16, 06:05 PM

CodeContextObje ct: I don't understand it either. I took a macro and converted it into VBA using the wizard, and this is how it wrote it for the most part, I did need to make a few changes, like adding code to loop through. Everything works as it stands but I can not use my PC while this is running or it messes up the process which takes a long time in this Database.

With the code you posted, where would I put this in my code? Would I paste it over the sendkeys code? I'm very green when it comes to VBA, but trying to learn.

**jforbes** · Jun 23 '16, 07:15 PM

This may seem a bit harsh, but it's not in my nature to be mean, so please don't take offense as none is intended...

Being new and green when it comes to Programming is perfectly normal; every programmer has to start with no knowledge. The attitude that will get you past the point of being green is one of Ownership. The code you have in front of you is your code even though you may not have initially written it. Take ownership of that code by understanding each line and what it does, and what all the lines of code together are supposed to accomplish.

Once you take ownership and know all the pieces, how they fit together, and the overall goal of the code, you will know exactly where to place the Kill statement. If you don't take ownership the code and then ask for help with it, your basically asking for someone else to fix your code.

**zmbd** · Jun 25 '16, 04:53 AM

For context this thread is related to:
home > topics > microsoft access / vba > questions > code worked as macro but not completely as vba
We've already worked thru a lot related to the converted macro>VBA.

In post#6 in the above thread; I've provided the information related to how to check for the existence of the old file ( Allen Browne FileExists() and FolderExists() functions ), and the suggestion about how to delete the file if found ( Kill Statement )

As you can see, jforbes Post#4 has provided basically the exact same solution to the issue as already suggested in code worked as macro but not completely as vba >post#6
This may not the only solution; however, it is one of the more straight forward methods and doesn't involve setting additional library references or "late-binding" for the file-scripting library.

As for CodeContextObje ct(read more) Normally I've seen this used for error logging. It returns a reference to the object (i.e. a command button on a form or maybe the timer_event of the form - then CodeContextObje ct=Form (not the Control nor the event) from which you can pull the name ) that called for the execution of the code (either VBA or Macro).

Therefor, I can only guess, that the macro that DJRhino converted to VBA using the wizard might be used in more that one calling macro or form so the wizard converted in the most conservative manner so that all of the objects could execute the code. The conversion wizard isn't always the most elegant coder. :)

DJRhino, you will find that we do not typically write code.

Even my own questions here are often answered with pointers to where to find the information or suggestions about how to approach the task from a different perspective resulting in a lot of "AHHA!" or "Doh!" moments for me; however, this has made me a much stronger database designer and coder.

**DJRhino** · Jun 27 '16, 02:32 PM

I took what jforbes put for code and made a new module and saved it. If my thinking is correct I would need to do a runcode "Module Name Here") in place of where I the sendkeys at now. Is this the correct thinking?

**jforbes** · Jun 27 '16, 02:55 PM

The line:

Code:

If fileExists(sFileName) Then Kill sFileName

will delete the file specified by sFileName. So you would want to delete the File before you attempt to write to that location.

This might work, but I still don't fully trust CodeContextObje ct:

Code:

' Operator Instruction
If fileExists(.[Web Output]) Then Kill .[Web Output
DoCmd.OpenReport "Inactive Instruction", acViewPreview, "", "[Process Part Number]=[Forms]![Instructions 1 Update]![Process Part Number]", acNormal

**NeoPa** · Jun 27 '16, 10:12 PM

Although I haven't followed the thread perfectly it seems to me that the best advice here, supported even by JForbes who's also given alternative code assistance, is simply to use the code provided to delete the file before you try to write to it. That way the prompt you're working to respond to should never occur.

I know things can sometimes seem complicated - especially when coding is not really your thing, but I'm reading a consistent message from both experts here that are trying to help you. Sometimes we forget that what appears straightforward to us can be complicated for those not so familiar with the territory.

I can only suggest that you give it a try. Everyone here believes it's the easiest and simplest, not to say most reliable, way to go.

**zmbd** · Jun 27 '16, 10:24 PM

DJRhino - I've forwarded a copy of some resources to your Bytes inbox. There are a couple of links there to VBA tutorials that you might find helpful :)

**DJRhino** · Jun 28 '16, 02:08 PM

Thank you zmbd for the link

Code to replace SendKeys in My Code

Code to replace SendKeys in My Code

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment