Reading/Updating Excel

[Juppo]

I can copy a template excel sheet (Purchase form) but need to be able to update specific cells (D9 - Customer Name,D11 - Address1 etc). I've searched and can find loadsa of ways to write to an excel sheet but really Id love to be able to;
1) Read in data from specific cells
2) Update specific cells

Could somebody point in the right direction or give an example? Loads of thanks in advance!

 

[mumbojumbo]

Using asp?

ooooooooooooooooooooooooh bugger.

 

[DreXor]

Using ADO you can update/read from the spreadsheet

just look over the threads in regarsd to updating or retrieving a recordset, and use an Excel ADO connection

 

[FengShui1998]

Juppo,

You can try this.

Filespec = "C:\Excel\MyExeclFile.xls"
Set objXL = WScript.CreateObject("EXCEL.application&quot
objXL.Visible = True
objXL.WorkBooks.Open Filespec
Set objWkBook = objXL.Workbooks.Item("MyExeclFile.xls&quot
Set colSheets = objWkBook.Sheets
WshShell.popup "Reading sheet: " & "Active" & ".", 3
Set Actv = objWkBook.Worksheets(1)

rows = Actv.UsedRange.rows.Count
cols = Actv.UsedRange.columns.Count

val1 = Ucase( trim( Actv.Cells(1,1).Value ) )
val2 = Ucase( trim( Actv.Cells(2,1).Value ) )
WshShell.popup "Val1= " & val1 & ", Val2= " & & val2, 3
objWkBook.Close
objXL.quit
Set colSheets = Nothing
Set objWkBook = Nothing
Set objXL = Nothing

fengshui_1998

 

[Juppo]

Thanks FengShui1998 for the code but Im getting errors as it doesn't like the WScript, at least I have a rough idea about what Im aiming for.

 

[FengShui1998]

Juppo,

Sorry. If you are doing it from an ASP page, change the following to:

Set objXL = Server.CreateObject("EXCEL.application&quot


fengshui_1998

 

[Juppo]

Hi I'v got it working but my update causes an error, whats wrong ?

<%
  Filespec = &quot;C:\test3.xls&quot;
	
  Set objXL = Server.CreateObject(&quot;EXCEL.application&quot;)
      objXL.Visible = True
      objXL.WorkBooks.Open Filespec
      
  Set objWkBook = objXL.Workbooks.Item(&quot;test3.xls&quot;)
  Set colSheets = objWkBook.Sheets
  Set Actv = objWkBook.Worksheets(1)

  rows = Actv.UsedRange.rows.Count
  cols = Actv.UsedRange.columns.Count
    
    Actv.Cells(2,1).Value = 7777
  	Actv.Cells(2,2).Value = &quot;Dairyman&quot;
    Actv.Cells(2,3).Value = &quot;=Sum(A4+B4)&quot;
    Actv.Cells(2,4).Value = &quot;SLLLLLLAAAAAAAAGGGGGGGG&quot;

Actv.Update

  objWkBook.Close
  objWkBook.quit

  objXL.quit
  Set colSheets = Nothing
  Set objWkBook = Nothing
  Set objXL = Nothing 
%>