Error 70 in Excel VBA

Question

Hi,

I am trying to upload directly a picture/chart from excel to a Sharepoint group URL. Here is the script:

---

Sub ExportChartJPG()

ActiveChart.Export Filename:="http://sharepoint.ap.xxxxxxxxxxxxxx.com/xxxxxx/xxxxxxxxxxxxxx/Pictures/MyChart.jpg", _FilterName:="jpeg"

End Sub

---

Is that possible? If it's not then can you suggest another way of doing it? Thanks

Answer 1

You can only export to a file, not to a URL. So, you could export to a temporary file on disk, and then submit the file to your web server. You would of course need the web server to have the ability to receive files.

Hang on, from the URL, it's a SharePoint server, yes? Presumably a SharePoint document library? In that case, you need to write some code to use one of the following techniques to upload the file:

• SharePoint Web Service
• WebDAV
• FrontPage Extensions

If you want to do this in VBA, then the MSXML3 library may be useful, since it will let you do HTTP requests.

---

EDIT: OK, based on your comments, here's a simple VBScript script to get you started. This opens an Excel workbook at a known location, and exports the first chart sheet.

Save this as "test.vbs" and then double-click on it to run it (having created a suitable Excel file, etc.).

Dim oExcel : Set oExcel = CreateObject("Excel.Application")

Dim oWorkbook : Set oWorkbook = oExcel.Workbooks.Open("C:\test.xls")

Dim oChart : Set oChart = oWorkbook.Charts(1)

oChart.Export "C:\chart.jpg", "JPEG"

oWorkbook.Close False

oExcel.Quit

As I said in my comment, VBScript is very much like VBA, but the downside is that there's no strong typing, so you don't get Intellisense, etc. It might be easier to create the script in VBA where you do have Intellisense (and a debugger, etc.) and then "port" it to VBScript.