Opening Microsoft File Formats to Java
Subject:   An alternative that may work in some situations
Date:   2003-02-01 15:44:36
From:   anonymous2
I got round the problem of presenting data using any of the office components as follows.

This method relies on two things.
1. You can use a web server to transfer a file to the client
2. The client enables macros (or you sign them)

My site works like this. When a user clicks on a report Icon (say for MS Project) a .mpp file is sent to their browser which they then open. The autoexec macro looks at the filename and picks off the ipaddress and some parameters. The macro then sends a request to the same Java servlet that sent the file with a ?MPP=Y switch and the other parameters by using the VB FileOpen (Workbooks.OpenText in Excel) to import a .csv file but from the servlet URL. The code is at the bottom.

I then use my servlet to send back a bunch of records as .csv.

I then use VB to handle all of the custom formatting, to make the data look pretty, add header and footer information etc. I actually transmit the header and footer information at the start of the .csv and make sure the macro knows what to truncate before opening the .csv.

I know it is not for everyone but it does save messing with custom file formats and I reckon tweaking some VB code to get the data to look right is probably a bit easier. Also it works with Word, Excel, Project and probably any other office component that can import .csv, .txt or maybe even XML!

Send me an email if you want the code writetojon at hotmail

Here is the VB macro
Sub Auto_Open()

' Determine remote_user, RVID and IP address from filename passed in
' in the form [CDSI][RVID][x].[IPAD].xls
S = ActiveWorkbook.Name + ".xls"
CDSI = Mid(S, 2, InStr(1, S, ")") - 2)
RVID = Mid(S, 2 + Len(CDSI) + 2, InStr(1 + Len(CDSI) + 2, S, ")") - 2 - Len(CDSI) - 2)
IPAD = Mid(S, InStr(1, S, ".(") + 2, InStr(1, S, ").xls") - InStr(1, S, ".(") - 2)
Application.ScreenUpdating = False
Workbooks.OpenText Filename:= _
"http://" + IPAD + "/CleanGreen/Health?XLSR=1&RVID=" + RVID + "&CDSI=" + CDSI, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False
Call Format(CDSI)
Sheets(1).Name = "HealthChart"
Application.ScreenUpdating = True
End Sub