-
May 28th, 2009, 12:23 PM
#1
Thread Starter
New Member
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
-
May 28th, 2009, 12:34 PM
#2
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)
-
May 28th, 2009, 04:09 PM
#3
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.
-
May 28th, 2009, 04:56 PM
#4
Thread Starter
New Member
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...
-
May 28th, 2009, 08:42 PM
#5
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|