CODEWITHSUNDEEP
vbaexcel
0x90
0x90

Reputation: 40982

How to call `Import text file` option on a specific cell in excel?

What I try to do is:

1. goto `worksheet 4`
2. right mouse click on cell `D32`
3. if (first time): 
      a. choose import text file.
   else:
      b. choose the Refresh

====The SOURCE CODE from the link to simulate the mouse events: ====

Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10

Private Sub SingleClick()
  SetCursorPos 100, 100 'x and y position
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

Private Sub DoubleClick()
  'Simulate a double click as a quick series of two clicks
  SetCursorPos 100, 100 'x and y position
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

Private Sub RightClick()
  'Simulate a right click
  SetCursorPos 200, 200 'x and y position
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

Upvotes: 1

Views: 340

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Is there any more elegant way to do it except of using mouse_events ?

Yes there is. Use the Worksheet_BeforeRightClick event

Also in the below example, i am checking if there is any contents in Cell D32 to decide whether to import or to refresh. You may use a boolean variable if you want to.

The code goes in the sheet code area of the relevant sheet. See screenshot.

SCREENSHOT

enter image description here

CODE

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    On Error GoTo Whoa

    Application.EnableEvents = False

    '~~> Check if the right click happened on D32
    If Not Intersect(Target, Range("D32")) Is Nothing Then
        '~~> Check if cell is empty
        If Len(Trim(Target.Value)) = 0 Then
            '~~> If empty, import file
        Else
            '~~> if not empty, refresh data
        End If
        Cancel = True
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

HTH

Upvotes: 1

Related Questions