Solved

(~) multipart upload from excel VBA

Posted on 2010-11-25
17
1,078 Views
Last Modified: 2012-05-10
I am trying to do a multi part upload of a file from a excel VBA to a webservice.

I have produced the code in the attached file based on a link I found on the internet.

This works for a text file but for an xls file I get the error below.

Now the developer of the webservice can upload either of my test files text or xls using something like

<form enctype="multipart/form-data" action="http://urlforwebservice/services/ndb/?action=upload" method="POST">
 <input name="file" type="file" />
 <input type="submit" value="Upload File" />
</form>

and as he can get it to work is not being particularly helpful.

Any ideas?

Attached also are the http string that is sent in each case.

TIA
 uploadTest.vba.txt
"<!DOCTYPE HTML PUBLIC ""-//IETF//DTD HTML 2.0//EN"">

<html><head>

<title>500 Internal Server Error</title>

</head><body>

<h1>Internal Server Error</h1>

<p>The server encountered an internal error or

misconfiguration and was unable to complete

your request.</p>

<p>Please contact the server administrator,

 root@localhost and inform them of the time the error occurred,

and anything you might have done that may have

caused the error.</p>

<p>More information about this error may be available

in the server error log.</p>

</body></html>

"

Open in new window

httpoutxls.txt
httpouttext.txt
0
Comment
Question by:Philip Pinnell
  • 9
  • 8
17 Comments
 
LVL 33

Assisted Solution

by:Norie
Norie earned 500 total points
ID: 34218324
Have you checked or enquired about the configuration of the server?

What's in the Excel files you want to upload?

Is it just data?

Or do you actually want to upload the entire file?
0
 
LVL 13

Author Comment

by:Philip Pinnell
ID: 34218371
I have spoken to the guy who developed the web service for help. He is on a unix environment and can upload either. I don't know how he does this but obviously not from an excel macro. He doesn't think there is a problem at the server or his webservice (we he would think that I suppose).

I can upload a text file but not xls, doc, bmp. Basically anything that is not straightforward text.

It is just data in the xls but the formatting is important as they are to be used as xls files. It is not simply the data that is important. So yes the idead is to upload an entire file.

I suspect it must be something to do with the reading of the binary xl file into a string or putting that together with the rest of the multipart Post

0
 
LVL 33

Expert Comment

by:Norie
ID: 34218933
Have you tried changing the file/content type?

You seem to be have a variable for the MIME type but you don't seem to use it and it only seems to be for text/xml anyway.

Shouldn't that be different for Excel files?

I'm not really an expert but when I've created code for downloading data to Excel I'm sure I've used something like application/excel or application/xls.

That of course is for downloading and there are other types - there might even be an xml 'type' for Excel, later versions of course.

PS When you mean the formatting is important what exactly do you mean?
0
 
LVL 13

Author Comment

by:Philip Pinnell
ID: 34219586
Yes tried various different mime-types and it is just the same text works but nothing else.

Formatting := excel formatting, column widths, date and number formats colouring etc etc
0
 
LVL 33

Expert Comment

by:Norie
ID: 34220671
I must be missing something I think, you've declared and set a value ( for a variable called aMimeType but you don't seem to use it anywhere.

Also in this line of code:

    strHttp = BuildFileUploadRequest(strFile, DestUrl, "file", realFilename, "text/xml", strBoundary, sUrl)

You seem to be passing "text/xml" as the value for the MimeType argument.

0
 
LVL 13

Author Comment

by:Philip Pinnell
ID: 34228516
imnorie

there may well be little buglets in the code this is still only a prototype but I have experimented with different mime-types and this has made no difference.

The mime-type does end up in the string that is sent as part of the post

"POST /services/ndb/?action=upload&file_url=C:\testfile.txt HTTP/1.0
Host: admin.businessmonitor.uat
Content-Type: multipart/form-data, boundary=wKWSkuOUErwE2cBq0v4HmQZvTwN51E6J
Content-Length: 3625172

--wKWSkuOUErwE2cBq0v4HmQZvTwN51E6J
Content-Disposition: form-data; name=""file""; filename=""C:\testfile.txt""
Content-Type: image/bmp

this one was when i was testing an image which also did not work. Only text has been successful

If you can see that there is something wrong with th mime-type, please point it out

thanks. Still scratching my head
0
 
LVL 33

Expert Comment

by:Norie
ID: 34229940
I'm getting confused here.

Is testfile.txt a bitmap file?

I can see that the MIME type is getting included but is it the right one for the file you are trying to upload?
0
 
LVL 13

Author Comment

by:Philip Pinnell
ID: 34230028
I can see that the MIME type is getting included but is it the right one for the file you are trying to upload?

As far as uploading text files it doesn't seem to matter, text files upload ok whatever the mime type I include.

There seem to be a few different mime type for excel files. I have tried a few different ones but I believe application/vnd.ms-excel is the one I should use.

I have tried uploading other file types using the relevant mime type for each but all these fail

Sorry if this is confusing but I am trying lots of different things to get it to work.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 33

Expert Comment

by:Norie
ID: 34230150
Well I honestly can't think of anything else.

Who exactly 'developed' this webservice?
0
 
LVL 13

Author Comment

by:Philip Pinnell
ID: 34230190
Effectively a contractor based in Romania. I'll probably have to get my boss to lean on him a bit. But the thing is that it works for him posting in a unix environment so we can't test like for like but it does point to what I am generating. It is just a matter of identifying what is not quite right
0
 
LVL 33

Expert Comment

by:Norie
ID: 34230299
Well, could that be it - UNIX?

It is kind of a completely different operating system, though I suppose most web stuff is supposed to be OS independent.
0
 
LVL 13

Author Comment

by:Philip Pinnell
ID: 34230345
A webservice shouldn't know, or need to know, or care which OS created the request. However there may something subtly wrong with the request I am creating
0
 
LVL 13

Author Comment

by:Philip Pinnell
ID: 34230858
If I save an excel file with a txt extension and use text/xml mime type. it fails

so it seems that something in the content isn't being expected.

That may mean the content is wrong or I am not supplying the relevant informatiuon for the content to be expected.


But which and what?
0
 
LVL 13

Accepted Solution

by:
Philip Pinnell earned 0 total points
ID: 34319062
I found I needed to base64 encode the text before uploading. Should have been obvious I suppose
0
 
LVL 33

Expert Comment

by:Norie
ID: 34321044
Andy

Well done, I kind of thought it might be some sort of setting.
0
 
LVL 13

Author Closing Comment

by:Philip Pinnell
ID: 34358753
Thanks for your efforts
0
 
LVL 33

Expert Comment

by:Norie
ID: 34361976
No problem - I'll admit I'm not quite an 'expert' with this sort of thing.

Can't even set up a 2 machine wireless network at home.:)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now