|
Although less frequently a scenario amongst Web applications, the situation may arise when a file needs to be picked
out of a batch of a few, several hundred or a few thousand that correspond to the current state of a Web application
as the end-user navigates it. Assuming that a Web page hyperlink exists for the current section of the Web application;
if the end user was to click on the section's link (section one of a hundred for example) then it would be wise to
pass off the current section ID as a parameter to Excel so that it could handle loading the spreadsheet associated with the section.
Bypassing client-side ActiveX resources using JavaScript we could simply write a section identifying parameter to a
file on the Web Server, which would be loaded by an Excel function upon loading of a master linker file responsible for
choosing the matching spreadsheet. The following PHP code will pass off the Web Application section ID to a scratch file.
|
<?
$id = "33123";
$url = "file://c:/linker_file.xls";
$file = fopen ("templink.dat", "w");
if ($file)
{ // write an ID value to file in the Web Server's default path
fwrite ($file, $id, strlen ($id));
fclose ($file);
}
// create a link to the file & load the default spreadsheet app. (Excel)
echo "<a href = \"$url\" target = \"_blank\">Launch Default XLS Application</a>";
?>
Code View - dynamicreport.com
|
Upon clicking on the link to the file, the default program that handles files of extension .xls (Excel) is launched and
loads the spreadsheet master linker file, which contains only the subroutine necessary in determining the associated
file to load from the batch. And perhaps even jumping to the most recent worksheet tab of information by date or index.
The file linker subroutine is called from the Workbook open subroutine so that it is executed immediately.
Next, the temporary parameter file is loaded from the Web server's default file path and the identification value is read
from the beginning of the file in cell A1. After acquiring the identification, it is searched in every filename in the path
where the Web application relevant spreadsheet files are stored. The filenames must contain IDs so that the files can be
determined and opened via the associated Web Application section links. Following the file search and load - the worksheet tabs
are traversed, and the most recent worksheet tab is selected assuming that each worksheet is named by date. The parent
master linker file workbook containing the file selection function is then closed, and the link-matched file and worksheet
tab remain visible and selected.
|
Private Sub Workbook_Open()
' Auto-run linker to open a spreadsheet based on
' the server-side identification file parameter value
GetFile
End Sub
Private Sub GetFile()
' open the Web Server file; read identification
Dim refID
Workbooks.Open "http://127.0.0.1/default_path/templink.dat"
refID = Range("A1").Value 'ID resides in first cell
ActiveWindow.Close
' obtain matching ID filename
Dim fso
Dim f
Dim f1
Set objFSO = CreateObject("Scripting.FileSystemObject")
' opening the file locally is secure since it must reside locally
' otherwise it can be loaded remotely from a server (http protocol)
Set f = objFSO.GetFolder("S:\folder\") ' shared intranet folder
Set fc = f.Files
For Each f1 In fc
If (InStr(f1, refID) > 0) Then
Exit For
End If
Next
' clear resources
Set f = Nothing
Set fc = Nothing
Dim xl
' open the matching file if it exists
If (objFSO.fileExists(f1) = True) Then
Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set newBook = xl.Workbooks
newBook.Open f1
Else
End
End If
Dim nSheets
Dim minD
Dim minN
Dim sName
nSheets = xl.Sheets.Count
minD = -9999
For i = 1 To nSheets
sName = xl.Sheets(i).Name
If (IsDate(sName)) Then
If (DateDiff("d", Date, sName) > minD) Then
minN = i
minD = DateDiff("d", Date, sheetN)
End If
End If
Next i
xl.Sheets(minN).Select
xl.Sheets(minN).Visible = True
' optional - close this workbook after loading the requested file
Application.Quit
End Sub
Code View - dynamicreport.com
|
|
dynamicreport.com
