Coverting 2010 macro to work on 2016

3

30136353

Board Regular
Joined
Aug 14, 2019
Messages
105
Hi Guys,

I have the below Macro which doesn't seem to work on excel 2016, I believe its because the new user may be on 64 bit? Can anyone advise how to covert this to work?

Thanks.

VBA Code: 
    Public Declare Function SetForegroundWindow Lib "user32" (ByVal HWND As Long) As Long
 
    Sub Automate_IE_Enter_Data()
  
    Dim i As Long
    Dim URL As String
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim HWNDSrc As Long
    
    Set IE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")
 
    IE.Visible = True
 
    URL = "testwebsite/"
 
    IE.Navigate URL
    
    Do While IE.ReadyState = 4: DoEvents: Loop
    Do Until IE.ReadyState = 4: DoEvents: Loop
    
    HWNDSrc = IE.HWND
    SetForegroundWindow HWNDSrc
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T1").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T1").Value = Range("B2")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T3").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T3").Value = Range("E2")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T5").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T5").Value = Range("F2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T7").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T7").Value = Range("G2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T9").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T9").Value = Range("H2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T2").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T2").Value = Range("C2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T4").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T4").Value = Range("I2")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T6").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T6").Value = Range("J2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T10").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T10").Value = Range("K2")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_D11").Selectedindex = 2
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_D11").FireEvent ("onchange")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_D8").Selectedindex = 2
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_D8").FireEvent ("onchange")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T59").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T59").Value = Evaluate("=INDEX(Sheet6!$D:$E,MATCH($A$2,Sheet6!$D:$D,0),2)")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O13").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O15").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O16").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O22").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_C26").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_C38").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O51").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O54").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_C58").Click
    
    IE.Height = 1000
    IE.Width = 1500
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T1").Focus
    
    End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Try this:

VBA Code: 
#If VBA7 then
Declare PtrSafe Function SetForegroundWindow Lib "user32" Alias "SetForegroundWindow" (ByVal hwnd As LongPtr) As Long
#else
    Public Declare Function SetForegroundWindow Lib "user32" (ByVal HWND As Long) As Long
#end if
 
    Sub Automate_IE_Enter_Data()
  
    Dim i As Long
    Dim URL As String
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
#If VBA7 then
    Dim HWNDSrc As LongPtr
#else
    Dim HWNDSrc As Long
#end if
    
    Set IE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")
 
    IE.Visible = True
 
    URL = "testwebsite/"
 
    IE.Navigate URL
    
    Do While IE.ReadyState = 4: DoEvents: Loop
    Do Until IE.ReadyState = 4: DoEvents: Loop
    
    HWNDSrc = IE.HWND
    SetForegroundWindow HWNDSrc
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T1").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T1").Value = Range("B2")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T3").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T3").Value = Range("E2")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T5").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T5").Value = Range("F2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T7").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T7").Value = Range("G2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T9").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T9").Value = Range("H2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T2").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T2").Value = Range("C2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T4").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T4").Value = Range("I2")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T6").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T6").Value = Range("J2")

    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T10").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T10").Value = Range("K2")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_D11").Selectedindex = 2
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_D11").FireEvent ("onchange")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_D8").Selectedindex = 2
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_D8").FireEvent ("onchange")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T59").Focus
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T59").Value = Evaluate("=INDEX(Sheet6!$D:$E,MATCH($A$2,Sheet6!$D:$D,0),2)")
    
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O13").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O15").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O16").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O22").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_C26").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_C38").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O51").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_O54").Click
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_C58").Click
    
    IE.Height = 1000
    IE.Width = 1500
    IE.document.getelementbyid("ctl00_ctl35_g_5626f8e0_8785_484a_8963_ec2994d57f5d_FormControl0_V1_I1_T1").Focus
    
    End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Macro works on my Laptop, and runs fine on other when i run step by step, but does not run when i play it.
Replies
1
Views
679
SMFERNANDO
S
P
  • Question
VBA GoTo: Will this work?
Replies
1
Views
478
Peter h
P
P
  • Question
Code using IE fails for one user only
Replies
3
Views
782
Peter h
P
P
  • Question
Need some help with this DoEvents loop. Please.
2
Replies
14
Views
1K
Peter h
P
M
  • Question
Code Works On Windows 10 Machine But Not Windows 8.1:
Replies
6
Views
2K
MattDW
M

Forum statistics

Threads
1,214,819
Messages
6,121,737
Members
449,050
Latest member
excelknuckles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top