desperate individual --- read inside

[GrantL]

Hi All,
I have promised a little form (by tomorrow) .
Basic enough.....little form that has a couple of option boxes and text fields a submit button which writes to a .xls on network drive.

works great using my machine (office 2000) however it seems to work on office 95 machines but id doesnt actually write the data to file.

here is the code any help greatly appreciated !!!

Dim oExcel As Object
Dim objExlBook As Object
Dim objExlSht As Object ' OLE automation object
Dim ThePath As String

ThePath = "c:\xsell.xls"


ExcelWas = 0
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application&quot
' if Excel is not launched start it
If Err = 429 Then
Err = 0
ExcelWas = 1 ' = 1 if excel was not already started
Set oExcel = CreateObject("Excel.Application&quot
' can't create object
If Err = 429 Then
MsgBox Err & ": " & Error, vbExclamation + vbOKOnly
Exit Sub
End If
End If


Set objExlBook = oExcel.Workbooks.Open(ThePath)
Set objExlSht = objExlBook.sheets(1)

Dim objRange As Object
Dim rowNo As Long


Set objRange = objExlSht.Range("A2&quot

TheVal = 1
Do Until TheVal = ""
With objRange
TheVal = objExlSht.cells(rowNo, 1).Value
rowNo = rowNo + 1
End With
Loop

rowNo = rowNo - 1

Dim Today As Date
Today = Date
'With objRange
objExlSht.cells(rowNo, 1) = Form1.Text1(0).Text
objExlSht.cells(rowNo, 2) = Form1.Combo1.Text
objExlSht.cells(rowNo, 3) = Form1.Text2(0).Text
objExlSht.cells(rowNo, 4) = Form1.Text3(0).Text
objExlSht.cells(rowNo, 5) = Form1.Option1(0).Value
objExlSht.cells(rowNo, 6) = Form1.Option2(0).Value
objExlSht.cells(rowNo, 7) = Form1.Option3(0).Value
objExlSht.cells(rowNo, 8) = Form1.Option4(0).Value
objExlSht.cells(rowNo, 9) = Today
'End With



oExcel.Visible = True
oExcel.Interactive = True
oExcel.AlertBeforeOverwriting = False
objExlBook.Save
oExcel.AlertBeforeOverwriting = False
objExlBook.Close

If ExcelWas = 1 Then ' If excel was not open before then close it now
oExcel.Quit
End If
' clean up (I test if objects are still "alive" to avoid errors):
If Not (objExlSht Is Nothing) Then
Set objExlSht = Nothing ' Remove object variable
End If
If Not (objExlBook Is Nothing) Then
Set objExlBook = Nothing ' Remove object variable
End If
If Not (oExcel Is Nothing) Then
Set oExcel = Nothing ' Remove object variable
End If



End Sub



Thanks in advance

regards
grant

 

[xlbo]

I'd be surprised if this worked at all on xl95 - it uses dialogue sheets rather than forms and the syntax for certain things is quite different

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[GrantL]

incase anyone cares the problem was :

objExlSht.cells(rowNo, 1) = Form1.Text1(0).Text
objExlSht.cells(rowNo, 2) = Form1.Combo1.Text
objExlSht.cells(rowNo, 3) = Form1.Text2(0).Text
objExlSht.cells(rowNo, 4) = Form1.Text3(0).Text
objExlSht.cells(rowNo, 5) = Form1.Option1(0).Value
objExlSht.cells(rowNo, 6) = Form1.Option2(0).Value
objExlSht.cells(rowNo, 7) = Form1.Option3(0).Value
objExlSht.cells(rowNo, 8) = Form1.Option4(0).Value
objExlSht.cells(rowNo, 9) = Today

should have had : objExlSht.cells(rowNo, 1).Value

hope this may help someone in the future

regards
grant

hehe maybe it was a stupid question

 

[GrantL]

sorry Geoff
I must have been posting at the same time as you....
thanks for the reply ....
but i got lucky anyway

cheers