Results 1 to 5 of 5

Thread: Excel VBA to copy data, open notepad & paste, then copy and paste back to excel

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2009
    Posts
    3

    Excel VBA to copy data, open notepad & paste, then copy and paste back to excel

    I am admittedly a novice at VBA. I have a need to copy a row of excel data (dates) and copy them, then open up notepad, and paste the data. Then I need to select all from notepad and copy. Then I would like to close notepad, and paste the data back into excel. I am close, but cannot quite figure out how to focus back to excel and close notepad...

    Sub CopyAndPasteTimes()
    Range("b:b").Copy
    Shell "notepad.exe", vbMaximizedFocus
    SendKeys "^v"


    SendKeys "^a"
    SendKeys "^c"

    If Application.CutCopyMode = False Then
    MsgBox "clipboard empty"
    Exit Sub
    End If

    MsgBox "Copied Times to Notepad and now Pasting back to Excel"
    'now activate excel window

    Application.Workbooks("working daily excel with macros").Activate

    Sheets("Average Start Time data").Select
    Columns("B:B").Select
    Selection.ClearContents
    Range("B1").Activate
    SendKeys "^v"
    Range("b1").PasteSpecial Paste:=xlPasteAll
    MsgBox "Now you can close notepad"

    End Sub

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Excel VBA to copy data, open notepad & paste, then copy and paste back to excel

    Thread moved to Office Development/VBA forum (note that the "VB Editor" in Office programs is actually VBA rather than VB, so the VB6 forum is not really apt)

  3. #3
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Excel VBA to copy data, open notepad & paste, then copy and paste back to excel

    Copying data to and from Notepad seems like the hard way to do something. What is the exact purpose of doing this? There is almost certainly an easier way to achieve your desired results.

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2009
    Posts
    3

    Re: Excel VBA to copy data, open notepad & paste, then copy and paste back to excel

    I would absolutely love to find another way around this anomoly, but basically, I have a row of dates exported from another application. Some of the dates are null or blank, but most have data. I have tried every type of code recommended by the best programmers, but there was nothing I could do to avoid the div/0 error. I found that if I copied and pasted the data into another simple application (like notepad or vi) and copied the data back to excel, the averages worked. It's the strangest thing ever...

    All I really need to do from the code that I have is close the notepad application that I have already opened, and bring the excel window back to focus. It doesn't seem to work for some reason...

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2009
    Posts
    3

    Re: Excel VBA to copy data, open notepad & paste, then copy and paste back to excel

    I figured out adding SendKeys "%{F4}{TAB}~" 'ALT + F4 then tab then (Close)
    does it...

Posting Permissions

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



Click Here to Expand Forum to Full Width