Excel VBA Automated IE File Load

Question

I am trying to automate a file load to a website via Excel VBA but I am struggling to interact properly with the file load part of the webpage. I have read many different webpages and answers both on here and other sources on how to do this but thus far my attempts are failing.

Admittedly I am not really that clued up on interacting with IE via VBA and the various HTML (or other) elements and need some help.

The part of the webpage I am struggling to interact with is this:

<LABEL ACCESSKEY=U CLASS=filelabel>Upload a file to current older:
<INPUT TYPE="file" NAME="eftupload" CLASS=filefield/></LABEL>
<INPUT TYPE="submit" value="Upload" CLASS=submitbutton>

The VBA I have managed to code so far is as follows ( a bit messy I know as I am still working on various way to get it working) the web element I am trying to use is located on "my URL 2":

Sub File2Web()

'dimension (declare or set aside memory for) our variables
Dim objIE As InternetExplorer 'special object variable representing the IE browser
Dim aEle As HTMLLinkElement 'special object variable for an <a> (link) element

'initiating a new instance of Internet Explorer and asigning it to objIE
Set objIE = New InternetExplorer

'make IE browser visible (False would allow IE to run in the background)
objIE.Visible = True

'navigate IE to this web page (a pretty neat search engine really)
objIE.Navigate "my URL"

'wait here a few seconds while the browser is busy
Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'in the userid box put value
objIE.Document.getElementById("username").Value = "userID"

'in the password box put value
objIE.Document.getElementById("password").Value = "PassWD"

'click the 'go' button
objIE.Document.getElementById("loginSubmit").Click
 'wait again for the browser
Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'navigate IE to this web page
'Upload file using input type=file
Dim sFormData As String, d As String
Dim FileName As String, FieldName As String
FieldName = "File"
FileName = "C:\FTP Temp\Test.txt"

'Boundary of fields. Be sure this string is Not In the source file
Const Boundary As String = ""

'Get source file As a string.
sFormData = GetFile(FileName)

FieldName = "frmUpload"

'Build source form with file contents
d = d + "Content-Disposition: form-data; name=""" + FieldName + """;"
d = d + " filename=""" + FileName + """" + vbCrLf
d = d + "Content-Type: application/upload" + vbCrLf + vbCrLf
d = d + sFormData
d = d + vbCrLf


Dim URL As String, FormData As String
FormData = d
Dim bFormData() As Byte
ReDim bFormData(Len(FormData) - 1)
bFormData = StrConv(FormData, vbFromUnicode)

URL = "my URL 2"

objIE.Navigate URL, , , bFormData, "Content-Type: multipart/form-data; boundary=" + vbCrLf

'wait here a few seconds while the browser is busy
Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

End Sub

'read binary file As a string value
Function GetFile(FileName As String) As String

Dim FileContents() As Byte, FileNumber As Integer
ReDim FileContents(FileLen(FileName) - 1)
FileNumber = FreeFile
Open FileName For Binary As FileNumber
Get FileNumber, , FileContents
Close FileNumber
GetFile = StrConv(FileContents, vbUnicode)

End Function

Everything works apart from the file load. When it runs, it just ends up navigating to a web page not found screen and the file does not upload but I do not get any VBA errors etc.

Any help would be greatly appreciated. I have no other way to upload the file due to network, application and other working restrictions so I need to interact with this page/form.

Edit: The full page code is as follows...

<html>
<head><title>sftp.mysite.com</title>
<link href="/EFTClient/efthtmladvanced.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="/EFTClient/efthtmladvanced.js">
</script>
<script type="text/javascript" src="/EFTClient/Shared/PTCs.js">
</script>
</head>
<body onload="javascript:init();"><div class=header><H1 CLASS=cwd>sftp.mysite.com - /Input/Applications/UKC Apps/</H1></div>       
<h3><i>NOTE: Web Transfer Client Access has been disabled for this user or container. Reverting to basic client.</i></h3>
<hr />
<FORM NAME="frmUpload" METHOD="POST" ACTION="/Input/Applications/UKC Apps/" ENCTYPE="multipart/form-data"><table border=0 width='90%'>
<tr>
    <td width='70%'>
        <LABEL ACCESSKEY=U CLASS=filelabel>Upload a file to current folder:&nbsp;<INPUT TYPE="file" NAME="eftupload" CLASS=filefield/></LABEL>
        <INPUT TYPE="submit" value="Upload" CLASS=submitbutton>
    </td>
    <td width='30%' align='right'>
        <INPUT TYPE='button' value='Change Password' onclick="setChangePassDivPosition();showDiv('ChangePass', true);">&nbsp;
        <INPUT style="visibility:visible;valign='top';" TYPE='button' value='Log Out' onclick='doLogout(); window.location.reload(true); return false;'>
    </td>
</tr>
</table>
</FORM><hr />

<pre>
<a href="/Input/Applications/;html">[To Parent Directory]</a><br><br>
<input type="button" value="Delete" name="btnDelete" id="btnDelete" onClick="javascript:deleteFiles();"><BR>
<input type='checkbox' name='folderitem' value="/Input/Applications/UKC Apps/my file.txt" id='1'>     <a href='javascript:void(0);' onclick="rename('/Input/Applications/UKC Apps/my file.txt');">Rename</a>             18 Dec 2017    14:02     56955145 <a href="/Input/Applications/UKC Apps/myfile.txt;html">my file.txt</a><BR>
</pre><hr>
<form name="changePassForm" id="changePassForm" method="post"><div id="ChangePass" class="floatingDiv" style="{ visibility:hidden; }"><table cellpadding="5" cellspacing="0" width="100%" style="border:1px solid black; border-bottom:0px"><tr class="divHeader"><td><table cellpadding=0 cellspacing=0 width="100%%"><tr><td class="formCopy"><b>Change Password</b></td><td class="formCopy" align="right"><a title="Click here to close this dialog" href="javascript:void(0)" onclick="showDiv('ChangePass', false);" style="font-family:verdana; font-size:10px; color:black; text-decoration:none">X</a></td></tr></table></td></tr></table><table cellpadding="5" cellspacing="0" width="100%%" style="border:1px solid black;"><tr><td class="formCopy">Current Password:</td><td class="formCopy"><input type="password" value="" name="oldpass" id="oldpass"></td></tr><tr><td class="formCopy">New Password:</td><td class="formCopy"><input type="password" value="" name="newpass" id="newpass"></td></tr><tr><td class="formCopy">Confirm Password:</td><td class="formCopy"><input type="password" value="" name="confirmpass" id="confirmpass"></td></tr><tr><td class="formCopy">&nbsp;</td><td class="formCopy">&nbsp;</td></tr></table><table cellpadding="5" cellspacing="0" style="border:1px solid black; border-top:2px solid #aaaaaa;" class="divHeader" width="100%%"><tr><td colspan="2" align="right"><input type="button" value="Change Password" onclick="changePassword(this.form);">&nbsp;<input type="button" value="Cancel" onclick="showDiv('ChangePass', false);"></td></tr></table></div></form><br /><br />
<center><font face="arial" size="1" color=#3366CC><em>Powered by the <b>Globalscape EFT Server</b></em></font></center>
<br />
</body>
</html>

I would imagine that he webpage in question might have some front-end to prepare the file for upload. Perhaps some Javascript event that is not firing due to your manipulation of the DOM elements. Without looking in-depth that would be my guess... Does this normally fire off of a mouse click event for example? Just something to look into until someone with the time to take this on can come help out (too busy i'm afraid) — Glitch_Doctor, Dec 19 '17 at 12:31
In the web page, when you click the element it automatically opens a file browser window and when you select your file it populates a box (however you cannot click in the back and type anything manually). Then you have to press the submit button and the file loads and the pafe refreshes. I'll add the full page code to assist perhaps. — Matt_Roberts, Dec 19 '17 at 12:49
Not an expert on this topic, but this link: https://stackoverflow.com/questions/4007969/application-x-www-form-urlencoded-or-multipart-form-data seems to point out that for `multipart/form-data` forms you can't include the boundary (vbCrlf) in the form data. I see you included the vbCrlf in variable `d`, maybe that's the issue? You included the boundary when you can't? — Ryan Wildry, Dec 19 '17 at 13:41
@RyanWildry - thanks for the link. Removed the line feeds but to no avail. Though I dont consider myself stupid, I have to admit the rest of the anwer on that link threw me and I don't really know where to go from here. — Matt_Roberts, Dec 19 '17 at 14:05
@Matt_Roberts Make sure that `Content-Type: application/upload` is right one, maybe it should be something generic like `application/octet-stream` or [other](https://en.wikipedia.org/wiki/Media_type)? Anyway, better to check the actual request data with browser developer tools on network tab. `ReDim bFormData(Len(FormData) - 1)` should be `ReDim bFormData(LenB(FormData) - 1)` as unicode char consists of 2 bytes. Also not sure regarding that first you make concatenation, and then use `StrConv(, vbFromUnicode)`, you may try to use `ADODB.Stream` instead. — omegastripes, Dec 19 '17 at 22:51

score 1 · Answer 1 · answered Mar 10 '18 at 00:54

Several security enhancements were added to Internet Explorer around the file input control.

The value sent to the server will likely contain a "fake path" so as not to expose to the server where the file came from
The input is disabled from users being able to type in a value (this was being snooped on by keyloggers on pages)
The field (or button) needs to be clicked by the user vs. triggering a click event on it with JavaScript

As such, I would fully expect both #2 and #3 to be blockers from any automated tool interacting with the field... unless that tool can fully mimic the user from the Windows world, actually clicking the button in the browser, then also selecting the file from the file chooser dialog.

Excel VBA Automated IE File Load

1 Answers1