Excel cell info passed to Website as input - Code provided

[WinterTT]

Hi All! I’m not quite new to VB code but I haven’t touched it in 10 years and that was just a class in high school so I’m starting over from scratch. I’ve hit a bit of a bump with this little project I’m working on and was hoping you all could assist or point me in the right direction. First let me explain what I am trying to accomplish. </SPAN>

Using Excel 2007 and windows xp</SPAN>
1)Have the user enter 7 pieces of information into an excel spreadsheet A2:G2</SPAN>
2)Have a timestamp of when the line was last updated populate on H2</SPAN>
3)Have the username populate on line I2 when the timestamp appears</SPAN>
4)If the user desires, when typing “Run” in J2 the following will happen</SPAN>
a) A website will open in a new window</SPAN>
b) The user will type in their pass code to access website(since the site logs off after a few mins)</SPAN>
c) The information in the excel row will fill in the textbox or select correct drop down option(as typed in the spreadsheet)</SPAN>
d) The “submit” button will automatically be selected</SPAN>

Problem 1) I haven’t figured out how to do 4b at all. Once I type in my pass code the site pulls up but no info on lines. However if I select run before it times out it pulls up the site will the info in the correct place. I’m guessing I’ll have to open the website tell the program to hold, then once the “real site” pulls up resume the program and input the info correctly. I have just noticed that if the site has timed out and the code must be entered manually

Set IE = CreateObject("InternetExplorer.Application")

is highlighted as an error and the site will not populate any info.</SPAN></SPAN>

Problem2) 4d, I don’t think this will be an issue and I am currently researching how to do this but since I haven’t found it yet thought I’d throw it in there.</SPAN>

Problem3) This is by far the most annoying problem. I can only pull up the top line’s information. I’ve attempted turning
</SPAN></SPAN>

If Range("A2").Value = "Manager" Then</SPAN>
        strManagerName = 11350045 'Value on website</SPAN>
  End If</SPAN>

INTO

If Range("A2:A10").Value = "Manager" Then</SPAN>
        strManagerName = 11350045 'Value on website</SPAN>
  End If</SPAN>

And all I get is Run-time error ‘13’ Type mismatch. I’m stumped with this one. I want every line’s information to be unique when entered into the website(an array?) but I’m horrible with that type of stuff. I think I’ve done ok so far but this road block is proving kinda difficult to get by. I’m not really good at any of the technical talk, since this is the first time I’ve even looked at macro before but if you explain it clearly I’m certain I can figure it out. Any assistance is appreciated. </SPAN>

Bonus Problem4) I have the intention to have the spreadsheet as a shared document so multiple users will be able to edit the doc at the same time. I’m not sure how this will affect the process overall if at all but I just want to cover my bases. This is something that will have to be addressed after everything else is done.</SPAN>


Below is the code I have concocted. You are free to use or replicate any of the code below if you like. I know making a decent timestamp appear in real time, in a macro was kinda hard so use away if you want.</SPAN></SPAN>

The code below checks if anything has been updated in the range provided(A2:A10000 for example) and if it has places the time and date in the cell cordinates provided. The userName generate part checks to see if the update has been made and then updates the next cell with who actually made the change(username). The last bit checks to see if the username has been updated, if so it calls the function "FillInternetForm".

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN>
 
  '*******************************Update Time and UserName</SPAN>
    'A</SPAN>
    If Target.Cells.Count > 1 Then Exit Sub</SPAN>
    If Not Intersect(Target, Range("A2:A10000")) Is Nothing Then</SPAN>
    With Target(1, 8) ‘Depends on # of columns and where you want the info placed</SPAN>
    .Value = Now</SPAN>
    .EntireColumn.AutoFit</SPAN>
    End With</SPAN>
    End If</SPAN>
   
    'B</SPAN>
    If Target.Cells.Count > 1 Then Exit Sub</SPAN>
    If Not Intersect(Target, Range("B2:B10000")) Is Nothing Then</SPAN>
    With Target(1, 7)</SPAN>
    .Value = Now</SPAN>
    .EntireColumn.AutoFit</SPAN>
    End With</SPAN>
    End If</SPAN>
   
    'C</SPAN>
    If Target.Cells.Count > 1 Then Exit Sub</SPAN>
    If Not Intersect(Target, Range("C2:C10000")) Is Nothing Then</SPAN>
    With Target(1, 6)</SPAN>
    .Value = Now</SPAN>
    .EntireColumn.AutoFit</SPAN>
    End With</SPAN>
    End If</SPAN>
   
    'D</SPAN>
    If Target.Cells.Count > 1 Then Exit Sub</SPAN>
    If Not Intersect(Target, Range("D2:D10000")) Is Nothing Then</SPAN>
    With Target(1, 5)</SPAN>
    .Value = Now</SPAN>
    .EntireColumn.AutoFit</SPAN>
    End With</SPAN>
    End If</SPAN>
   
    'E</SPAN>
    If Target.Cells.Count > 1 Then Exit Sub</SPAN>
    If Not Intersect(Target, Range("E2:E10000")) Is Nothing Then</SPAN>
    With Target(1, 4)</SPAN>
    .Value = Now</SPAN>
    .EntireColumn.AutoFit</SPAN>
    End With</SPAN>
    End If</SPAN>
   
    'F</SPAN>
     If Target.Cells.Count > 1 Then Exit Sub</SPAN>
    If Not Intersect(Target, Range("F2:F10000")) Is Nothing Then</SPAN>
    With Target(1, 3)</SPAN>
    .Value = Now</SPAN>
    .EntireColumn.AutoFit</SPAN>
    End With</SPAN>
    End If</SPAN>
   
    'G</SPAN>
        'F</SPAN>
     If Target.Cells.Count > 1 Then Exit Sub</SPAN>
    If Not Intersect(Target, Range("G2:G10000")) Is Nothing Then</SPAN>
    With Target(1, 2)</SPAN>
    .Value = Now</SPAN>
    .EntireColumn.AutoFit</SPAN>
    End With</SPAN>
    End If</SPAN>
 
   
    '*****************************************************************</SPAN>
    'UserName generate</SPAN>
      If Target.Cells.Count > 1 Then Exit Sub</SPAN>
    If Not Intersect(Target, Range("H2:H10000")) Is Nothing Then</SPAN>
    With Target(1, 2) ‘Depends on # of columns and where you want the info placed</SPAN>
    .Value = Environ$("UserName")</SPAN>
    .EntireColumn.AutoFit</SPAN>
    End With</SPAN>
    End If</SPAN>
   
  '*******************************End Update Time and UserName</SPAN>
 
  '*******************************Call FillInternetForm Function</SPAN>
    If Not Intersect(Target, Range("J2:J100")) Is Nothing Then</SPAN>
    Call FillInternetForm</SPAN>
    End If</SPAN>
  '******************************* End Call FillInternetForm Function</SPAN>
      
End Sub</SPAN>

Below code Found: </SPAN></SPAN>[VBA] Macro using Excel + IE possible? - Neowin Forums

Function</SPAN> FillInternetForm</SPAN>()</SPAN> 
  Dim</SPAN> IE </SPAN>As</SPAN> Object</SPAN> 
  Set</SPAN> IE </SPAN>=</SPAN> CreateObject</SPAN>(</SPAN>"InternetExplorer.Application"</SPAN>)</SPAN> 
'create new instance of IE. use reference to return current open IE if </SPAN>
'</SPAN>you want to </SPAN>use</SPAN> open IE window</SPAN>.</SPAN> Easiest</SPAN> way I know of </SPAN>is</SPAN> via title bar</SPAN>.</SPAN> 
  IE</SPAN>.</SPAN>Navigate</SPAN> "[URL="http://css-tricks.com/examples/NiceSimpleContactForm/"]http://css-tricks.com/examples/NiceSimpleContactForm/</SPAN>[/URL]"</SPAN> 
'go to web page listed inside quotes </SPAN>
  IE.Visible = True </SPAN>
  While IE.busy </SPAN>
    DoEvents  '</SPAN>wait </SPAN>until</SPAN> IE </SPAN>is</SPAN> done</SPAN> loading page</SPAN>.</SPAN> 
  Wend</SPAN> 
  IE</SPAN>.</SPAN>Document</SPAN>.</SPAN>All</SPAN>(</SPAN>"Name"</SPAN>).</SPAN>Value</SPAN> =</SPAN> Range</SPAN>(</SPAN>"A1"</SPAN>).</SPAN>Value</SPAN> 
  IE</SPAN>.</SPAN>Document</SPAN>.</SPAN>All</SPAN>(</SPAN>"City"</SPAN>).</SPAN>Value</SPAN> =</SPAN> Range</SPAN>(</SPAN>"A2"</SPAN>).</SPAN>Value</SPAN> 
  IE</SPAN>.</SPAN>Document</SPAN>.</SPAN>All</SPAN>(</SPAN>"Email"</SPAN>).</SPAN>Value</SPAN> =</SPAN> Range</SPAN>(</SPAN>"A3"</SPAN>).</SPAN>Value</SPAN> 
  IE</SPAN>.</SPAN>Document</SPAN>.</SPAN>All</SPAN>(</SPAN>"Message"</SPAN>).</SPAN>Value</SPAN> =</SPAN> Range</SPAN>(</SPAN>"A4"</SPAN>).</SPAN>Value</SPAN> 
End</SPAN> Function</SPAN>

My version of the code above

The code below first checks to see if "Run" is in column J to allow the function to go forward. It then takes the values in the cells and forwards the input to the corrisponding website input box(textbox, or dropdown box).

Function FillInternetForm()</SPAN>
  Dim IE As Object</SPAN>
 
  Dim strSegmentH As String 'Segment Hamp value</SPAN>
  Dim strSegmentNH As String 'Segment Non-Hamp value</SPAN>
  Dim strSegmentS As String 'Segment Sim value</SPAN>
  strSegmentH = "Hamp"</SPAN>
  strSegmentNH = "Non Hamp"</SPAN>
  strSegmentS = "Sim Dec"</SPAN>
 
  Dim strManagerName As String 'Manager name value</SPAN>
  strManagerName = "Manager"</SPAN>
  '*******************************Allow all input to webpage</SPAN>
  If Range("J2").Value <> "" Then ' If col J has anything run all code below</SPAN>
  
  Set IE = CreateObject("InternetExplorer.Application")</SPAN>
'create new instance of IE. use reference to return current open IE if</SPAN>
'you want to use open IE window. Easiest way I know of is via title bar.</SPAN>
  IE.Navigate "URL Address HERE"</SPAN>
'go to web page listed inside quotes</SPAN>
  IE.Visible = True</SPAN>
  While IE.busy</SPAN>
    DoEvents  'wait until IE is done loading page.</SPAN>
  Wend</SPAN>
  '*******************************Manager Name Input</SPAN>
  If Range("A2").Value = "Manager" Then</SPAN>
        strManagerName = 11350045 'Value on website</SPAN>
  End If</SPAN>
  IE.Document.All("A1710639").Value = strManagerName 'manager name must be value beside name in site source</SPAN>
  '*******************************End Manager Name Input</SPAN>
 
  '*******************************Loan # Input</SPAN>
  IE.Document.All("A1710642").Value = Range("B2").Value 'loan #</SPAN>
  '*******************************End Loan # Input</SPAN>
 
  '*******************************Bwr Last Name Input</SPAN>
  IE.Document.All("A1710643").Value = Range("C2").Value 'bwr last name</SPAN>
  '*******************************End Bwr Last Name Input</SPAN>
 
  '*******************************Segment Choice Selection Input</SPAN>
  If Range("F2").Value = "Sim Dec" Then 'Sim Dec choice</SPAN>
        strSegmentS = 10728622 'Value on website</SPAN>
  IE.Document.All("A1710644").Value = strSegmentS</SPAN>
  End If</SPAN>
 
  If Range("F2").Value = "Hamp" Then 'Hamp choice</SPAN>
        strSegmentH = 10728623 'Value on website</SPAN>
  IE.Document.All("A1710644").Value = strSegmentH</SPAN>
  End If</SPAN>
 
  If Range("F2").Value = "Non Hamp" Then 'Non Hamp choice</SPAN>
        strSegmentNH = 10728624 'Value on website</SPAN>
  IE.Document.All("A1710644").Value = strSegmentNH</SPAN>
  End If</SPAN>
  '*******************************End Segment Choice Selection Input</SPAN>
 
  End If ' Run all code above</SPAN>
  '*******************************End Allow all input to webpage</SPAN>
 
End Function</SPAN>

 

[WinterTT]

Haha! I was looking up some info to help someone with their issue and figured out how to fix one of mine. Problem 2 is now off the list.

 

[WinterTT]

Anyone think they could assist with my 3rd problem?

Someone recommended turning the excel sheet into a table and working with that but I'm not too sure how that would benifit me with this particular issue. I just need the macro to pull up info only on the current row its on(2-whatever), presently its set to only pull info from the second row.

 

[WinterTT]

I may have found my solution. I've decided to use ActiveCell.Row to get the row # and go from there.

  IE.Document.All("A1710642").Value = Range("B" & ActiveCell.Row).Value 'loan #

Instead of the original that could only pull from B2

  IE.Document.All("A1710642").Value = Range("B2").Value 'loan #

Something strange happens though. It works perfect if I press the run button when I'm looking at the code but if the Macro runs on its own, the website pulls up but no information actually populates in the form. Any ideas as to why?