FileUpload, WinHttpRequest and Excel VBA RSS

• • Reply
  

  carlo perato...

  0 Points

  1 Post

  FileUpload, WinHttpRequest and Excel VBA

  Jul 25, 2009 04:02 PM|LINK

  I am new to the forum, and new to web programming, but have managed to piece together some almost working code.  I have exhausted all of the internet content I can find and could use some help...

  Here is a short description:  I need a routine that can upload an xls file from excel to the web, and download the file back down.   (I initially started with FTP, which works fine internally on our network, and works fine on my systems outside the firewall, but a large amount of our clients have FTP disabled, so it is not a workable option.   I decided to look for other options and selected URLDownloadToFile method for the download and that works great.  The upload is a little more complicated, and i settled on FileUpload and WinHttpRequest methods. 

  I have everything almost working, but could use a little help.

  FileUpload issues:

  The FileUpload textbox is greyed out and i cannot manually enter a file name.  The browse function works ok.  (I believe that the greyed out box is part of my problem on the Excel side.)

  Here is the ASPX code and ASPX.CS Code and a screen shot:

  ASPX

  ===========================================================================================

  
   

  ASPX.CS

  ===========================================================================================

  using System;
  using System.Collections.Generic;
  using System.Web;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System;
  using System.Web.UI.WebControls;
  
  public partial class P3_Default : System.Web.UI.Page
  {
  
      protected void Submit_Click(object sender, EventArgs e)
      {
          Response.Write("We have received the file: " + FileUpload.FileName);
          FileUpload.PostedFile.SaveAs(Request.PhysicalApplicationPath + ServerFileUploadPath.Text + "/" + FileUpload.FileName);
  }
     
      protected void Page_Load(object sender, EventArgs e)
      {
  
  }
  
  }
  

  Screen Shot ===========================================================================================

   

  

   

   

   

   

   

   

   

   

   

   

   

  Note that the Filename text box is greyed out.   You can try the same at http://ww.tri-simulation.com/P3/   Make sure you keep the path as P3, otherwise it will fail.

  *******************************************************************************

  What am I doing that disables the manual entry of the filename?  Any suggestions are appreciated.

  *******************************************************************************

   

  Part 2.  Excel VBA

  I have written the following VBA routine to automatically post the file to the web with no user intervention.   The code seems to work ok no errors, but does not accomplish the upload.   I have not found any good tools to help me troubleshoot the issues.  The failure may purely be a function of the greyed out input box, but I am not sure....   (I tried just keeping the path box and the submit button and also did not get any indication that the routine is interacting with the form.

  Sub HTTPInternetPutFile()
      Dim WinHttpReq As Object
      Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

      Dim strURL As String
      Dim StrFileName As String
      Dim FormFields As String
      
         StrFileName = "c:\temp\ctc1output.xls"
         strURL = "http://www.tri-simulation.com/P3/"
     

         WinHttpReq.Open "POST", strURL, False
        

         ' Set the header
        WinHttpReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
     

         FormFields = """fileulp=" & StrFileName & """"
         FormFields = FormFields + "&"
         FormFields = FormFields + """sfpath=P3"""
      
         WinHttpReq.Send FormFields
     
      
         ' Display the status code and response headers.
         MsgBox WinHttpReq.GetAllResponseHeaders
         MsgBox WinHttpReq.ResponseText

  End Sub

   

  Here are the issues I think are relevant here..

  If i change the HTTP verb to GET it works as expected.   If I change to POST it will only work when the url has a trailing slash.  (I have read conflicting reports on whether the / is needed or not.  I do not understand the headers, so i put in something that looked correct.  I may not have the header set properly given that I am uploading a file.   (I have seen far more complicated headers.)

  I am not sure that I have the Send command working properly.   (This code runs fine, but the issue is that the response text does not indicate that the file has been transferred, and the file is not loaded to the folder on the website.....

  Any help or expertise here is welcomed...

   

  Thanks,

  Carlo

   

  FileUpload WinHTTP Excel VBA