Solved

(~) multipart upload from excel VBA

Posted on 2010-11-25
17
1,123 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

815 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

13 Experts available now in Live!

Get 1:1 Help Now