Excel to ASP...

courtjester · #1 Sep 24th, 2004, 14:48

Hey Guys. You know who you are :wink:. I am going to be designing a grade book for a school system. I now have the knowledge to do everything but the one thing I need help on is the spreadsheet. I want it where when the spreadsheet is saved it will automatically upload it to the internet. I know I'd need some kind of software to do this with but once I get it on the internet can I use a spreadsheet just like i would use a database? The problem as you may know is security, but I am talking to my host about setting up a local Secure folder where no one can download anything in that folder. Plz let me know if you have found any good tutorials, have one yourself, or if you know any programs (free - $2000) that will do this. Much appreciation.

Wade

spinal007 · #2 Sep 24th, 2004, 17:21

I don't know the exact details but it seems to me that you'd be better off having your data source online and allowing your users to download the resulting spreadsheet.

I've done sumthing similar so I may be able to help with the solution if you explain what the problem is a bit better.

all I can do to help at the moment is point you here:
how to use a spreadsheet as datasource,just like a database:
http://webforumz.com/showthread.php?threadid=1400
(good article by the way!)

courtjester · #3 Sep 24th, 2004, 17:28

well the reason I need it to go from the pc to the net is because I have a built-in-checker where if your not logged onto the school's network then you can't log-in to the system. It's extra security. Only LAN can make changes. I don't want some kid to hack the network and change everyone's grades you know.

spinal007 · #4 Sep 25th, 2004, 07:58

I see.......
in that case I don't see how you can automatically save the spreadsheet to a webserver without using some sort of special program.......

OR
will all users be connecting to the internet from a fixed IP?
you could use that (as well as username/password) for your security

courtjester · #5 Sep 25th, 2004, 18:32

I actally have the server on the same LAN as the pc's. So You won't be able to even access the updating stuff without being on the LAN. That part I can do. Maybe I'll just let them use access or something to get the files up.

spinal007 · #6 Sep 26th, 2004, 08:25

well then, if its all on the same network then it's easier......
you can design your pages to connect to the spreadsheet just like you would with any ohter datasource.
the hard work will be to collect the data from the users and transfer it to the server.....

how much do you know about VBA?

Rob · #7 Sep 26th, 2004, 13:08

Court Jester...

Why dont you just write out a comma seperated volume(CSV) file, using the file system object.

Any CSV can be opened in Excel quite easily.

If you need help on CSV formatting, let me know.

spinal007 · #8 Sep 26th, 2004, 16:05

last website I designed I had to use FSO to write out CSV files with financial summary and other information..... so I could hep on that too.......

Smokie · #9 Sep 27th, 2004, 08:35

Out of interest, how are you only allowing LAN users access to the area which allows updating of the grades?

spinal007 · #10 Sep 27th, 2004, 12:19

i suppose, if the program/webpage used to access the spreadsheet/datasource is only available in the Local Network then you can just leave the security job to windows

right?

courtjester · #11 Sep 27th, 2004, 14:46

We have some program. I don't know what it's called... but it allows FTP access for LAN only. If you even try to FTP outside of the LAN then it kicks you off. Pretty neat. Also, if you log-in to the protected pages the program will boot you if your not on the LAN as well. I don't know how it works, I think it checks to see if your machine has the program running, and if so it checks some key information in the program, and if your program isn't running it'll kick you. The school system is leaving the county and going to their own school system so the county installed all the stuff on thier computers. Now they need someone else to do it since the county is no longer helping them.

I'm thinking about just building an ASP application (Intranet) and letting them do all the modifications that way. Then linking the databases off of that to show the kids thier grades. I don't know how to use the FSO or CSV.

spinal007 · #12 Sep 27th, 2004, 17:56

I should really put this in the tutorials and code samples section but use this FSO module.
let's you write, delete, move, rename, copy files and folders easily......

Code: Select all

<%
' File System Object Module
' v1.2, May 2004
' diego@pinesandneedles.com
%>
<%
Function FixPath(Path)
  On Error Resume Next
  Path = "/" & Path & "/"
  Path = Replace(Path, "//", "/")
  Path = Replace(Path, "\/", "/")
  Path = Replace(Path, "/\", "/")
  Path = Replace(Path, "\\", "\")
  FixPath = Path
End Function



' GET FILE AND FOLDER OBJECTS
Function GetFolder(obj, FolderName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  Set obj = tmp_objFSO.GetFolder(Server.MapPath(FolderName))
  Kill tmp_objFSO
  GetFolder = CBool(Not (Err.number>0))
End Function

Function GetFile(obj, FileName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  Set obj = tmp_objFSO.GetFile(Server.MapPath(FileName))
  Kill tmp_objFSO
  GetFile = CBool(Not (Err.number>0))
End Function

Function GetParentFolder(FolderName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  GetParentFolder = tmp_objFSO.GetParentFolderName(FolderName)
  Kill tmp_objFSO
End Function

Function GetFiles(objRS, objFolder)
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Fields.Append "name", adVarChar, 100
  objRS.Fields.Append "size", adInteger
  objRS.Fields.Append "size_caption", adVarChar, 50
  objRS.Fields.Append "date", adDate
  objRS.Fields.Append "modified", adDate
  objRS.Fields.Append "type", adVarChar, 255
  objRS.Open

  For Each objItem In objFolder.Files
   objRS.AddNew
    objRS.Fields("name") = objItem.Name
    objRS.Fields("size") = objItem.Size
    If objItem.Size<1024 Then
      objRS.Fields("size_caption") = objItem.Size & " bytes"
    Else
      objRS.Fields("size_caption") = FormatNumber(objItem.Size/1024,1)
    End If
    objRS.Fields("date")= objItem.DateCreated
    objRS.Fields("modified")= objItem.DateLastModified
    objRS.Fields("type")= objItem.Type
  Next ' objItem
End Function



' EXISTS ?
Function FileExists(FileName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  FileExists = tmp_objFSO.FileExists(Server.MapPath(FileName))
  Kill tmp_objFSO
End Function

Function FolderExists(FolderName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  FolderExists = tmp_objFSO.FolderExists(Server.MapPath(FolderName))
  Kill tmp_objFSO
End Function



' CREATE
Function CreateFolder(FolderName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  tmp_objFSO.CreateFolder(Server.MapPath(FolderName))
  Kill tmp_objFSO
  CreateFolder = CBool(Not (Err.number>0))
End Function
Function BuildPath(FolderName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  Folders = Split(FolderName,"/")
  Path = "/"
  For Each Folder In Folders
    If Trim(Folder)<>Empty Then
      Path = Path & Trim(Folder) & "/"
      If Not tmp_objFSO.FolderExists(Server.MapPath(Path)) Then tmp_objFSO.CreateFolder Server.MapPath(Path)
    End If
  Next  ' Folder
  Kill tmp_objFSO
  BuildPath = CBool(Not (Err.number>0))
End Function

Function NewFile(obj, FileName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  Set obj = tmp_objFSO.CreateTextFile(Server.MapPath(FileName), True)
  Kill tmp_objFSO
  NewFile = CBool(Not (Err.number>0))
End Function
Function CreateFile(FileName)
  NewFile tmp_file, FileName
  Kill tmp_file
  CreateFile = CBool(Not (Err.number>0))
End Function



' WRITE
Function OpenFile(objFile, FileName)
  On Error Resume Next
  GetFile F, FileName
  Set objFile = F.OpenAsTextStream(2,-2)
  Kill F
  OpenFile = CBool(Not (Err.number>0))
End Function

Function OpenTextFile(objFile, FileName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  Set objFile = tmp_objFSO.OpenTextFile(Server.MapPath(FileName))
  Kill tmp_objFSO
  OpenTextFile = CBool(Not (Err.number>0))
End Function

Function WriteToFile(FileName, FileContent)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  If tmp_objFSO.FileExists(Server.MapPath(FileName)) Then
    OpenFile tmp_objFile, FileName
  Else
    NewFile tmp_objFile, FileName
  End If
  tmp_objFile.Write(FileContent)
  Kill tmp_objFile
  WriteToFile = CBool(Not (Err.number>0))
End Function



' MOVING
Function MoveFolder(FolderName, Destination)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  tmp_objFSO.MoveFolder Server.MapPath(FolderName), Server.MapPath(Destination) & "\"
  Kill tmp_objFSO
  MoveFolder = CBool(Not (Err.number>0))
End Function

Function MoveFile(FileName, Destination)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  tmp_objFSO.MoveFile Server.MapPath(FileName), Server.MapPath(Destination) & "\"
  Kill tmp_objFSO
  MoveFile = CBool(Not (Err.number>0))
End Function



' COPYING and RENAMING
Function DoCopyFile(Source, Destination, NewName, DeleteSource)
  On Error Resume Next
  If GetFile(F, Source) Then
    F.Copy Server.MapPath(Destination) & "\" & NewName
  End If
  Kill F
  If DeleteSource And (Not (Err.number>0)) Then DeleteFile Source
  DoCopyFile = CBool(Not (Err.number>0))
End Function

Function CopyFileRename(Source, Destination, NewName)
  CopyFileRename = DoCopyFile(Source, Destination, NewName, False)
End Function
Function CopyFile(Source, Destination)
  CopyFile = DoCopyFile(Source, Destination, Empty, False)
End Function
Function RenameFileLeaveSource(Source, NewName)
  Location = Left(Source,InStrRev(Source,"/"))
  RenameFileLeaveSource = DoCopyFile(Source, Location, NewName, False)
End Function
Function RenameFile(Source, NewName)
  Location = Left(Source,InStrRev(Source,"/"))
  If Source<>(Location & NewName) Then ' different name
    RenameFile = DoCopyFile(Source, Location, NewName, True)
  End If
End Function

Function CopyFolderContents(Source, Destination)
  'On Error Resume Next
  GetFolder Folder, Source
  For Each File In Folder.Files
    Errors = Errors And CopyFile(Source & File.Name, Destination)
  Next
  Kill Folder
  CopyFolderContents = CBool((Not (Err.number>0)) And (Not Errors))
End Function


Function DoCopyFolder(Source, Destination, NewName, DeleteSource)
  'On Error Resume Next
  GetFolder F, Source
  F.Copy Server.MapPath(Destination) & "\" & NewName
  Kill F
  If DeleteSource And (Not (Err.number>0)) Then DeleteFolder Source
  DoCopyFolder = CBool(Not (Err.number>0))
End Function

Function CopyFolderRename(Source, Destination, NewName)
  CopyFolderRename = DoCopyFolder(Source, Destination, NewName, False)
End Function
Function CopyFolder(Source, Destination)
  CopyFolder = DoCopyFolder(Source, Destination, Empty, False)
End Function
Function RenameFolderLeaveSource(Source, NewName)
  Location = Left(Source,InStrRev(Source,"/"))
  RenameFolderLeaveSource = DoCopyFolder(Source, Location, NewName, False)
End Function
Function RenameFolder(Source, NewName)
  Location = Left(Source,InStrRev(Source,"/"))
  RenameFolder = DoCopyFolder(Source, Location, NewName, True)
End Function



' DELETE
Function DeleteFolder(FolderName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  tmp_objFSO.DeleteFolder(Server.MapPath(FolderName))
  Kill tmp_objFSO
  DeleteFolder = CBool(Not (Err.number>0))
End Function

Function DeleteFile(FileName)
  On Error Resume Next
  Set tmp_objFSO = FSO()
  tmp_objFSO.DeleteFile(Server.MapPath(FileName))
  Kill tmp_objFSO
  DeleteFile = CBool(Not (Err.number>0))
End Function
%>

then all you do is define you CSV string and write it to a file

e.g:

Code: Select all

MyCSV = "student id,name,score,grade" & vbCRLf

For Each Student In Request("Student")
 MyName = Request("Name" & Student)
 MyScore = Request("Score" & Student)
 MyGrade = Request("Grade" & Student)
 MyCSV = MyCSV & Student & "," & MyName & "," & MyScore & "," & MyGrade & vbCRLf
Next ' Student

WriteToFile "/test_folder/test.csv", MyCSV

I hope that helps you get started.......

spinal007 · #13 Sep 27th, 2004, 17:58

hey, I'VE GOT 3 STARS!!!!!!!!!!!!!!!!!!

Rob · #14 Sep 27th, 2004, 18:43

Spinal.... consider turning this into an article matey!! That way you have an undirected (google friendly) backlink to your site.

For details, see this link:- http://www.webforumz.com/default.asp...9&CategoryID=1

spinal007 · #15 Sep 27th, 2004, 20:44

wicked....
I've got a whole lotta stuff that I wuldn't mind sharing, but I won't give away my secret formulas!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Anyone know much about Excel	AdRock	Webforumz Cafe	8	Sep 29th, 2007 19:58
Flash and Excel	natepen	Flash & Multimedia Forum	1	May 21st, 2007 15:56
Excel Thru Recovery	Chase42304	Graphics and 3D	3	Nov 27th, 2006 14:21
Import Excel into PHP	AndyP	PHP Forum	9	Jul 20th, 2006 12:32
Excel problem	madhuri.t	Classic ASP	1	Mar 7th, 2006 01:23

		Webforumz.com > Main Forums > Program Your Website > Classic ASP
Excel to ASP...