Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2011
    Posts
    8

    Unanswered: Advice needed with macro programming (specifically SENDKEYS)

    Hi all, thanks for your time.

    I have just started a new job (my first in a year thanks to the recession), and have been given a massive menial task. I have other work to do, and reckon I can so this with a macro. I have worked out exactly what keystrokes are needed and how to open a file that needs opening at one point, but I can't put it all together.

    I also read something about a wait command, which may be beneficial if the macro gets ahead of itself.

    Here is the code I have so far...

    Code:
    Do
    
    
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{SHIFTDOWN}")
    SendKeys ("{DOWN}")
    SendKeys ("{CTRLUP}")
    SendKeys ("{UP}")
    SendKeys ("{RIGHT 24}")
    SendKeys ("{SHIFTUP}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{C}")
    SendKeys ("{CTRLUP}")
    
    
    
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\mjksspf5\Desktop\Dump\Template.xls"
    
    
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{LEFT}")
    SendKeys ("{UP}")
    SendKeys ("{CTRLUP}")
    SendKeys ("{DOWN}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{V}")
    SendKeys ("{CTRLUP}")
    SendKeys ("{LEFT}")
    SendKeys ("{F2}")
    SendKeys ("{SHIFTDOWN}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{LEFT}")
    SendKeys ("{SHIFTUP}")
    SendKeys ("{C}")
    SendKeys ("{CTRLUP}")
    SendKeys ("{ENTER}")
    SendKeys ("{ALTDOWN}")
    SendKeys ("{O}")
    SendKeys ("{H}")
    SendKeys ("{R}")
    SendKeys ("{ALTUP}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{V}")
    SendKeys ("{CTRLUP}")
    SendKeys ("{ENTER}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{PGDN}")
    SendKeys ("{ALTDOWN}")
    SendKeys ("{O}")
    SendKeys ("{H}")
    SendKeys ("{R}")
    SendKeys ("{ALTUP}")
    SendKeys ("{RIGHT}")
    SendKeys ("{SHIFTDOWN}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{LEFT}")
    SendKeys ("{SHIFTUP}")
    SendKeys ("{V}")
    SendKeys ("{CTRLUP}")
    SendKeys ("{ENTER}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{PGUP}")
    SendKeys ("{CTRLUP}")
    SendKeys ("{ALTDOWN}")
    SendKeys ("{F2}")
    SendKeys ("{ALTUP}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{V}")
    SendKeys ("{CTRLUP}")
    SendKeys ("{ENTER}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{W}")
    SendKeys ("{CTRLUP}")
    SendKeys ("{LEFT}")
    SendKeys ("{CTRLDOWN}")
    SendKeys ("{DOWN}")
    SendKeys ("{CTRLUP}")
    
    
    
    Loop end
    I've no idea if it needs a header or anything, I'm new to this. If this is a huge problem and can't easily be overcome, I apologise.

    Thanks in advance,

    Pete

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Pete,

    Where is this code located?

  3. #3
    Join Date
    Jan 2011
    Posts
    8
    It's located in the text document where I was logging my keystrokes. I thought because I learnt a bit of basic HTML easily I'd be able to find a code example and figure out what to put around it, but it's not been so easy. I'm stuck now .

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Okay. Do you want to automate this process using VBA (the visual basic language that comes integrated within Excel)?

  5. #5
    Join Date
    Jan 2011
    Posts
    8
    Yeah, that's my aim. Unfortunately the macro recorder doesn't log keystrokes, only the results...

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Excel has a rich object model which is exposed to VBA. This means that you hardly ever have to (or should) use Sendkeys at all. Sendkeys can be quite unreliable so most programmers avoid it whenever possible.

    It looks like you just want to automate opening a workbook and copying/pasting some data. This can be done quite easily with a few lines of VBA code - very similar to that which the macro recorder would generate. Why aren't you happy with the code the macro recorder has produced (once you have recorded it you can view it in the visual basic editor)?

  7. #7
    Join Date
    Jan 2011
    Posts
    8
    Basically, I need to copy sections with a varying number of lines (denoted by an "activity code" in the first column) out of one spreadsheet and into another template, rename several things (by copying the activity code from a cell and renaming a few tabs) then save the template with an appropriate name (the activity code). When I recorded the macro, it would only do the first selection, but these keystrokes would do the whole sheet correctly.

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Trust me, you need to forget the keystrokes - it's completely the wrong way to do it. If you zip and attach a sample workbook with some simple instructions, I'll try to demonstrate how to do it using the Excel object model. Alternatively, post the macro recorder code and we can try to work out why it wouldn't do all of the selections for you.

  9. #9
    Join Date
    Jan 2011
    Posts
    8
    Here is an attached zip. Thanks for your help!
    Attached Files Attached Files

  10. #10
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Great, thanks for uploading it. I'll try to have a proper look at it by the end of the weekend.

  11. #11
    Join Date
    Jan 2011
    Posts
    8
    Thanks! I really appreciate your help!

  12. #12
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    This isn't quite how I would do it but I've tried to accomodate your report lay out and keep the code as simple as possible.

    So this code goes in a 3rd workbook and it will open up the example and template workbooks and create those reports. You need to change the file paths / names in the code so it is looking in the right place.

    Code:
    Sub CreateActivityReports()
        Dim wkbSource As Workbook, wkbTarget As Workbook
        Dim wstSource As Worksheet, wstTarget As Worksheet, wstJournal As Worksheet
        Dim rngActivityArea As Range, rngActivityFirstCell As Range, rngActivityLastCell As Range
        Dim rngTarget As Range
     
        Dim lngSourceLastRow As Long, lngSourceLastCol As Long
        Dim lngActivities As Long, lngActivity As Long
     
        Dim strActivityCode As String
     
        'change the paths here so they are correct (where your template/example is saved down)
        Set wkbTarget = Workbooks.Open("C:\Documents and Settings\****\Template***.xls")
        Set wkbSource = Workbooks.Open("C:\Documents and Settings\****Example***.xls")
     
     
        Set wstSource = wkbSource.Worksheets(1)
        Set wstTarget = wkbTarget.Worksheets(1)
        Set wstJournal = wkbTarget.Worksheets(2)
     
        'determine how many activity areas there are
        lngActivities = Application.CountA(wstSource.Range("A:A"))
     
        'less one to allow for the column header
        lngActivities = lngActivities - 1
     
        'determine the size of the entire range we want to extract the data from
        lngSourceLastRow = GetLastRow(wstSource.UsedRange)
        lngSourceLastCol = GetLastCol(wstSource.UsedRange)
     
        'we need to loop through each area of activity, transfer the area's activity into the template
        'and then save a copy of the template using the activity code
        For lngActivity = lngActivities To 1 Step -1
     
            'work out the last cell of the activity area
            If rngActivityFirstCell Is Nothing Then
                Set rngActivityLastCell = wstSource.Cells(lngSourceLastRow, lngSourceLastCol)
            Else
                Set rngActivityLastCell = wstSource.Cells(rngActivityFirstCell.Row - 1, lngSourceLastCol)
            End If
     
            'work out the first cell of the activity area
            Set rngActivityFirstCell = wstSource.Cells( _
                wstSource.Cells(rngActivityLastCell.Row, 1).End(xlUp).Row, 1)
     
            'get a reference to the activity area
            Set rngActivityArea = wstSource.Range(rngActivityFirstCell, rngActivityLastCell)
     
            'get a reference to the target area in the template workbook
            Set rngTarget = wstTarget.Range("A6").Resize( _
                                rngActivityArea.Rows.Count, _
                                rngActivityArea.Columns.Count)
     
            'assign across the values
            rngTarget.Value = rngActivityArea.Value
     
            'rename the tabs in the template
            strActivityCode = CStr(rngActivityArea.Cells(1).Value)
     
            wstTarget.Name = strActivityCode
            wstJournal.Name = "Journal " & strActivityCode
     
            'save a copy of the template
            wkbTarget.SaveCopyAs "C:\Documents and Settings\mjksspf5\Desktop\Dump\" & strActivityCode
     
            'clear the template ready for the next cycle
            rngTarget.ClearContents
     
        'move on to the next activity area
        Next lngActivity
     
        'close the workbooks we opened
        wkbTarget.Close savechanges:=False
        wkbSource.Close savechanges:=False
     
    End Sub
     
    Public Function GetLastRow(ByVal rngToCheck As Range) As Long
        Dim rngLast As Range
     
        Set rngLast = rngToCheck.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious)
     
        If rngLast Is Nothing Then
            GetLastRow = rngToCheck.Row
        Else
            GetLastRow = rngLast.Row
        End If
     
    End Function
     
    Public Function GetLastCol(ByVal rngToCheck As Range) As Long
        Dim rngLast As Range
     
        Set rngLast = rngToCheck.Find(what:="*", searchorder:=xlByColumns, searchdirection:=xlPrevious)
     
        If rngLast Is Nothing Then
            GetLastCol = rngToCheck.Column
        Else
            GetLastCol = rngLast.Column
        End If
     
    End Function

  13. #13
    Join Date
    Jan 2011
    Posts
    8
    Thanks! I'll try it out this afternoon.

  14. #14
    Join Date
    Jan 2011
    Posts
    8

    Talking

    Thanks! Works great. I have to manually put the ".xls" on each file, but that doesnt take long, really.

    Thanks again!

  15. #15
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Quote Originally Posted by PedroPietro View Post
    Thanks! Works great. I have to manually put the ".xls" on each file, but that doesnt take long, really.

    Thanks again!
    Great stuff. To fix the .xls file extension, just amend this line to include the red bit at the end:

    Code:
    wkbTarget.SaveCopyAs "C:\Documents and Settings\mjksspf5\Desktop\Dump\" & strActivityCode & ".xls"
    Hope that helps...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •