Write to a .xls

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Write to a .xls

Logged in as: Guest

arrSession:exec spGetSession 2,2,64833

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Write to a .xls
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Write to a .xls - 10/6/2008 12:18:58 PM

exhausted1180

Posts: 6
Score: 0
Joined: 10/6/2008
Status: offline

I have scoured the internet for a way to write to a .xls, and haven't found one. I have a script that will take various inputs from the user, in the form of text boxes in two different tabs. It will create a directory structure with predetermined files and folders, and also up a main registry in a excel format. I could not get it to write to an excel format directly, I had to do something like this. Does anyone know anything different? This way works, but it isn't what I wanted.

< Message edited by exhausted1180 -- 10/6/2008 12:25:07 PM >

Revisions: 1 | Post #: 1

RE: Write to a .xls - 10/6/2008 12:20:43 PM

exhausted1180

Posts: 6
Score: 0
Joined: 10/6/2008
Status: offline

I forgot to add, what this script basically does is saves it to a text format with the .xls extension,so you can update it, and then save it, then reopen it and save as a real xls format.

(in reply to exhausted1180)

Post #: 2

RE: Write to a .xls - 10/6/2008 4:58:12 PM

htac

Posts: 12
Score: 0
Joined: 10/2/2008
Status: offline

Hi,

You could go with:

Set oExcel = Createobject("Excel.Application")
oExcel.DisplayAlerts = FALSE
oExcel.Visible = FALSE
Set oWorkbook = oExcel.Workbooks.Add
Set oWorkbook = oExcel.ActiveWorkbook
Set oWorksheet = oWorkbook.Worksheets(1)

Then,

oworksheet.Cells(1,1).Value = "something" and so on.. ' (rownumber, columnnumber)

After you have the cellvalues needed,

oExcel.activeworkbook.SaveAs "Filelocation", -4143

oExcel.ActiveWorkbook.Close
oExcel.Application.Quit
Set oExcel = Nothing

(in reply to exhausted1180)

Post #: 3

RE: Write to a .xls - 10/7/2008 8:39:05 AM

exhausted1180

Posts: 6
Score: 0
Joined: 10/6/2008
Status: offline

Yeah, I have tried that, but then everytime it is ran, it rewrites starting at 1,1.

I need it to pick up off where it last left, and then continue. So if I run the program, and it enters 13 lines of information, the next time i run it, it should pick up on 14 and on.

(in reply to htac)

Post #: 4

RE: Write to a .xls - 10/7/2008 9:50:57 AM

wjcott

Posts: 41
Score: 0
Joined: 9/7/2008
Status: offline

In that case change....

oworksheet.Cells(1,1).Value = "something"

... to...

oworksheet.Range("A65536").End(-4162).Range("A2").Value = "something"

...which will result in the data being constantly added at the end of the list.

(in reply to exhausted1180)

Post #: 5

RE: Write to a .xls - 10/7/2008 1:22:11 PM

exhausted1180

Posts: 6
Score: 0
Joined: 10/6/2008
Status: offline

Perfect! Thanks for your help!

The script asks for

Job number, capital code, date, engineering, materials, sent to drafting.

IN the registry it starts at

B7,C7,D7,E7,F7,G7,H7

The next time I run the program, i need it to fill in B8,etc..

< Message edited by exhausted1180 -- 10/7/2008 1:25:20 PM >

(in reply to wjcott)

Post #: 6

RE: Write to a .xls - 10/8/2008 3:30:50 AM

wjcott

Posts: 41
Score: 0
Joined: 9/7/2008
Status: offline

If I am understanding you correctly, I would do something like this...

< Message edited by wjcott -- 10/8/2008 3:32:18 AM >

(in reply to exhausted1180)

Post #: 7

RE: Write to a .xls - 10/9/2008 6:36:57 AM

exhausted1180

Posts: 6
Score: 0
Joined: 10/6/2008
Status: offline

For some reason it was still giving me problems.

Here is what I had to do, and it worked!!!

BTW is there a way to compile hta to an exe????

set fs = CreateObject("Scripting.FileSystemObject") 
fs.CreateFolder (userselections.txtFile.value & "\" & userselections.districtnumber.value & " " & userselections.jobname.value)
Set oExcel = Createobject("Excel.Application") 
oExcel.DisplayAlerts = FALSE 
oExcel.Visible = FALSE
Set oWorkbook = oExcel.Workbooks.open ("T:\Engineering\Database\CWO REGISTER\Thunder Bay District Registry.xls")
'Set oWorkbook = oExcel.Workbooks.Add 
Set oWorkbook = oExcel.ActiveWorkbook 
Set oWorksheet = oWorkbook.Worksheets(5) 
With oworksheet.Range("A65536").End(-4162)
  .Offset(1,0).Value = userselections.districtnumber.value
  .Offset(1,1).Value = userselections.regionname.value
  .Offset(1,2).Value = JobDescription.value
  .Offset(1,3).Value = PO.value
  .Offset(1,4).Value = strNEW & strRELOCATE & strUPGRADE & strAERIAL & strBURIED & strCONDUIT & strFIBER & strCABLE
  .Offset(1,5).Value = Nodename.value
  .Offset(1,6).Value = APC.value
  .Offset(1,7).Value = EPR.value
  .Offset(1,8).Value = SR.value
  .Offset(1,9).Value = TRC.value
  .Offset(1,10).Value = POLEPERMIT.value
  .Offset(1,11).Value = DATECREATED.value
  .Offset(1,12).Value = Dateissued.value 
  .Offset(1,13).Value = CONTRACTOR.value
  .Offset(1,14).Value = PLanner.value
  .Offset(1,15).Value = MATERIAL.value
  .Offset(1,16).Value = COMPLETED.value
  .Offset(1,17).Value = DRAFTING.value
  .Offset(1,18).Value = RDRAFTING.value
  .Offset(1,19).Value = PRINTCOR.value

End With
oExcel.activeworkbook.Save
'As "T:\Engineering\Database\CWO REGISTER\Thunder Bay District Registry.xls", -4143 
oExcel.ActiveWorkbook.Close 
oExcel.Application.Quit 
Set oExcel = Nothing

Well there we go! Thanks for all your help and direction

(in reply to wjcott)

Post #: 8

RE: Write to a .xls - 10/9/2008 8:00:06 AM

wjcott

Posts: 41
Score: 0
Joined: 9/7/2008
Status: offline

quote:

BTW is there a way to compile hta to an exe????

I am unaware of any free methods of creating an executable from vbscript/hta, but there are many commercial applications that provide this form of functionality.

(in reply to exhausted1180)