Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If, Then, Copy (2002)

    I'll try and explain the best way I can, please see attached.
    The sheet is auto populated using formula's to pick up data from a basic input sheet. The layout is in blocks of four's, each 4 to be copied and pasted to a seperate application. There are 15 blocks of four totalling 60 individual transactions. The other application will not accept blanks. In the event of there being (say) only 7 transactions, then I want the first copy button to select the first four, and the second copy button to select only the next three. So I am thinking along the lines of an If statement for each copy set? Any idea's?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If, Then, Copy (2002)

    I don't see any "copy buttons".

    The "blocks" in your worksheet contain many blank rows and columns between the data. How are those to be handled?

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If, Then, Copy (2002)

    The buttons are not in the attachment, but are in the actual file,on top of the squares in column K. As an example, based on the first block:

    Complete block of 4 - B5:I14
    Partial block of 3 - B5:I11
    Partial block of 2 - B5:I8
    Single transaction - B5:I5

    The underscores in the lines between transactions are only required as part of the copy, where a transaction follows. For example if a single transaction is imput to the other application as B5:I7, the application would fail. Hope this makes sense!!

    PS. Column I in the attachment is blank, but can contain data.

    <hr>The "blocks" in your worksheet contain many blank rows and columns between the data. How are those to be handled?
    <hr>
    The layout is designed to fit the template in the other application.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If, Then, Copy (2002)

    The data are in rows 5, 8, 11, 14, 16, 19, 22, 25, 27, ... As you see, the progression isn't regular.
    It would be easier if you inserted an extra row between the "blocks of 4", so that the data are in rows 5, 8, 11, 14, 17, 20, 23, 26, 29, ...
    Would that be possible?

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If, Then, Copy (2002)

    Lord only knows why I posted "that" old version, where did I get that from???? Yes, certainly!! as per attached.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If, Then, Copy (2002)

    Try something like this:

    Sub CopyBlock(n As Integer)
    Dim intStart As Integer
    Dim intStop As Integer
    Dim i As Integer
    intStart = 12 * n - 1
    If Cells(intStart, 2) = "" Then
    MsgBox "No data", vbExclamation
    Else
    intStop = intStart
    For i = 1 To 3
    If Cells(intStart + 3 * i, 2) = "" Then
    Exit For
    Else
    intStop = intStop + 3
    End If
    Next i
    Range(Cells(intStart, 2), Cells(intStop, 9)).Copy
    End If
    End Sub

    Sub Copy1()
    CopyBlock 1
    End Sub

    Sub Copy2()
    CopyBlock 2
    End Sub

    ...

    The macros Copy1, Copy2, etc. (to be completed up to and including Copy15) are the ones to call from the command buttons.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If, Then, Copy (2002)

    Hans, the code works perfectly, Thankyou!!
    Now I appreciate that you cannot see my 'other application', so I am not expecting miracles here. The cursor within the other app would be positioned correctly to start, following the paste action, 2 enters would be required, which would again leave the cursor correctly positioned for the next copy (from excel) and paste to other app.
    My question is, can I reduce 15 copies (macro's) and 15 manual paste's, to one macro where excel could auto fill my other app? In theory??

    Any advice appreciated.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If, Then, Copy (2002)

    I have no idea whether this will work. I tried it with Word and it failed miserably.

    Sub CopyBlocks()
    Const strApp = "My application"
    Dim n As Integer
    Dim intStart As Integer
    Dim intStop As Integer
    Dim i As Integer
    For n = 1 To 15
    intStart = 12 * n - 1
    If Cells(intStart, 2) = "" Then
    Exit For
    Else
    intStop = intStart
    For i = 1 To 3
    If Cells(intStart + 3 * i, 2) = "" Then
    Exit For
    Else
    intStop = intStop + 3
    End If
    Next i
    Range(Cells(intStart, 2), Cells(intStop, 9)).Copy
    AppActivate strApp
    SendKeys "^V~~", True
    End If
    Next n
    End Sub

    Change the value of the constant strApp to the caption of the application window into which you want to paste.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If, Then, Copy (2002)

    When testing, I used

    Const strApp = "Microsoft Word"

    but

    Const strApp = "Word"

    or

    Const strApp = "Document1"

    (where Document1 is the name of the active document within Word) would have worked too. The code did activate Word and inserted paragraph marks, but failed to paste the Excel cells.

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If, Then, Copy (2002)

    Thankyou Hans, I'll give this a go at work over the next few days and let you know how I get on. When you say that this failed with Word, I presume that you were using Const strApp = "Word"??

    I am presuming that: ^V~~ is equivalent to 'Control V' to paste, enter, enter.? I know that 'Control V' to paste is not accepted in the app, but I should be able to find the correct command. I have a few preset macro's for the app already, so should be able to use the code to work out the commands, the tricky bit will be the activation of the app.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If, Then, Copy (2002)

    > I am presuming that: ^V~~ is equivalent to 'Control V' to paste, enter, enter.?

    That's correct. Click in SendKeys and press F1 to get help for this instruction. If you scroll down the help subject, you'll see a list of keycodes that you can use.

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If, Then, Copy (2002)

    <hr>Change the value of the constant strApp to the caption of the application window into which you want to paste.
    <hr>
    Hans, I have attached a snippet of code from a current macro that I use within my other app. From the code, am I right to think that 'constant strApp' would be "autECLSession"? **This is a macro that works direct with the app**

    I still need to ascertain the paste command into the app, I did'nt get time today! Though my overall requirement is:

    App: - F2
    XLS: - Copy
    App: - Paste, Enter, Enter - (Repeating these 2 lines through the loop)
    App: - F4
    App: - F9
    End Sub

    Is there anything here that would alter the macro that you kindly provided yesterday?

    Many Thanks
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If, Then, Copy (2002)

    autECLSession is the name of an Automation object, I assume it is not the caption of the window of the application - you need the latter for AppActivate.

    Apparently the application supports VBScript. Does it come with documentation on how to automate it, or does the developer provide support?

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If, Then, Copy (2002)

    Hans,
    < not the caption of the window of the application - how would I find this?

    I Know even less about VB than I know of VBA. No and No. I could ask our <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15> ever so helpful helpdesk (that until 3 weeks ago were based upstairs, they are now 600 miles away). But then I could also win the lottery tonight. end <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If, Then, Copy (2002)

    Does the application appear in the Windows taskbar or is it hidden? The caption would be the title of the taskbar button.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> The helpdesk is only 600 miles away? That's not very far - all too often nowadays it's continents away... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Page 1 of 3 123 LastLast

Posting Permissions

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