Member Avatar for smi1in

Hi,
I want to make a macro in MSExcel which can copy data from one window to another. Can any one help me in this.

  • 2 Contributors
  • 2 Replies
  • 109 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by smi1in

Dani AI

Generated

As clarified (and as asked), the goal is to move data from Excel into another application window. Two reliable strategies are recommended: use COM automation when the target app exposes an object model (Word, Outlook, etc.), or use the clipboard + window-activation + SendKeys for apps without automation. Prefer automation whenever possible — it is deterministic and does not rely on screen focus.

A compact COM example for pasting a copied range into Word (works even if Word is not already running):

Sub PasteRangeToWord()
    Dim wd As Object
    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then Set wd = CreateObject("Word.Application")
    On Error GoTo 0
    wd.Visible = True
    ThisWorkbook.Sheets("Sheet1").Range("A1:B10").Copy
    wd.Documents.Add
    wd.Selection.PasteExcelTable False, False, False
End Sub

When the target app has no automation interface, use the clipboard plus Windows API to bring the other window forward, then send a paste keystroke. The declarations below support both 32-bit and 64-bit Office:

#If VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hWnd As LongPtr) As Long
#Else
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long
#End If

Sub CopyRangeToOtherApp()
    ThisWorkbook.Sheets("Sheet1").Range("A1:B10").Copy
#If VBA7 Then
    Dim h As LongPtr
#Else
    Dim h As Long
#End If
    h = FindWindow(vbNullString, "Untitled - Notepad")
    If h = 0 Then MsgBox "Target not found": Exit Sub
    SetForegroundWindow h
    Application.Wait Now + TimeSerial(0,0,1)
    SendKeys "^v", True
End Sub

Notes and troubleshooting: match the target window title exactly; add small delays (Application.Wait) if paste fails; SendKeys is fragile (user input or UAC prompts can break it). If both apps are Excel workbooks, avoid any of the above and copy values directly with Workbooks("Dest.xlsx").Sheets("Sheet1").Range("A1:B10").Value = .... Also ensure macros are enabled and, when possible, prefer COM automation for stable, robust solutions.

Recommended Answers

All 2 Replies

Member Avatar for bushman_222

are you meaning from one excel document to another or between sheets?

Member Avatar for smi1in

No, From One Application to another.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.