web form needs to open, write to, and save/close existing excel file 2

[chilly442]

I have a web form that users will enter monthly numbers into. I have a submit button that the user will press when they are done entering the data. I need the web form to open an existing Excel file, write the data into specific cells, then save and close the Excel file. If possible , I would like to have this all go on without the user knowing.
Can anyone help me with all or even part of this.
Thanks in advance.

Chilly442

 

[w33mhz]

well could you have the button update or insert or whatever to an SQL Express DB and use that data to populate the spreadsheet.

 

[w33mhz]

could try this (obviously modify to your needs)

'This script grabs the names and size subfolders of the defined subject folder.  It places the info into an Excel 
'document, sorts in descending folder size and autofits the cells.  Now if you leave the script the way it is 
'the script will not save the autofit because it creates the xls as a Tab deliminated file.  
'The best way to run this script is to comment the Wscrpt.Echo "Done"; objExcel.Quit; and 
'objWorkbook.Close TRUE statements out and then Save it as a new xls doc.  BTW the Option Explicit is NOT needed.

Option Explicit
Const ForWriting = 2
Const CONVERSION_FACTOR = 1048576
Const xlDescending = 2
Const xlYes = 1

dim fso, fl, objFolder
dim colSubfolders, objSubfolder, objExcel, objWorkbook, objWorksheet
dim x
dim objRange, objRange2
dim strEndPoint

Set fso = CreateObject ("Scripting.FileSystemObject")
Set fl = fso.CreateTextFile ("e:\test6.xls", ForWriting, True)
fl.Close
Set objFolder = fso.GetFolder("E:\stuff")
Set colSubfolders = objFolder.Subfolders
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook =  objExcel.Workbooks.Open("e:\test6.xls")
Set objWorksheet = objWorkbook.Worksheets(1)


objExcel.Visible = True
objExcel.Cells(1, 1).Value = "Folder Name"
objExcel.Cells(1,2).Value = "Folder Size"


x = 1


For Each objSubfolder in colSubfolders
	x = x+1
	objExcel.Cells(x,1).Value = objSubfolder.Name
	objExcel.Cells(x,2).Value = Convert(objSubfolder.size)
	
Next

strEndPoint = "B2:B" & x
Set objRange2 = objExcel.Range(strEndPoint)

Set objRange = objWorksheet.UsedRange

objRange.EntireColumn.Autofit()
objRange.Sort objRange2,xlDescending

'the objExcel.Quit is not needed if you get rid of the Echo "Done" statement.
objWorkbook.Close TRUE
objExcel.Quit
WScript.Echo "Done"



Function Convert(SIZE)
	If SIZE =< 0 Then
		Convert = 0
	Else
		Convert = SIZE / CONVERSION_FACTOR
	End If
End Function

 

[chilly442]

Thank you!!!
If I want to put the data in pre-defined cells (C,1)(C,2)... would I take the For Each loop out?

Then add something like:

objExcel.Range(C,1).Value=txt.Home.value
objExcel.Range(C,2).Value=txt.Next.value

Any ideas?

 

[PHV]

I'd use this:
objExcel.Range("C1").Value=txt.Home.value
objExcel.Range("C2").Value=txt.Next.value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[w33mhz]

Yep Yep. Here is a couple of references.

http://www.cruto.com/resources/vbscript/vbscript-examples/office/excel/Format-a-Range-of-Cells.asp

http://www.cruto.com/resources/vbscript/vbscript-examples/vbscript-sitemap.asp

 

[chilly442]

Thanks for the help. Been trying to get this to work for a couple of days, and now I have it.

Thanks
Chilly442

"Can I get that Icon in Cornflower Blue?