VB6 opening excel in w95 (w95/w2000)

[jha900]

I wrote a small vb6 program that works fine on w2000. When i try on w95 i get "this program performed illegal operation" when i hit the submit button.

Private Sub imgSubmit_click()
Dim xlsApp As Excel.Application
Dim wb As Workbook


'code to make sure no other instance of excel is open

'open the contact xls
Set xlsApp = New Excel.Application
' xlsApp.Visible = True
Workbooks.Open FileName:="c:contacts.xls"
Call AddToExcel
ActiveWorkbook.Close SaveChanges:=True
xlsApp.Quit
Set xlsApp = Nothing
Call ClearTextBoxes
If MsgBox("Contact has been entered, do you want to enter another?", vbYesNo) = vbNo Then
End
End If

End Sub

Private Sub AddToExcel()

Dim rngData As Range
Dim rngRow As Range
Dim lastRow As Integer

Worksheets("Data").Activate
Set rngData = Range("A3").CurrentRegion

lastRow = rngData.Rows.Count + 1
Range("A" & lastRow) = txtDate.Text

end sub

What am i doing wrong? thank you for the help

[gwhitfield]

Word 95 used Wordbasic- a Basic-like language- which was easier to learn than VBA but which was far less powerful. I'm not even sure if you can do what you want in Wordbasic.

[jha900]

by w95 i meant windows 95. I tried to use the code with w95 and word97 and it bombed. thanks

[jscher2000]

Can you narrow it down to the line that causes the crash? Annoying ways to do this include putting a MsgBox after each line in the most likely area or writing to a log file as you go. If there is an elegant way to do it, I'm not aware of it.

[gwhitfield]

Sorry- confusion now cleared up.

Do you have the same version of Excel on you Win95 machine? Do you have a reference set to Excel from your Word VBA? Is it set to the correct version?

[gwhitfield]

I'm running Word 2000 on WinXP, and I could not get it to even compile.

I added a reference to Excel. I did have to remove the call to "ClearTextBoxes" but that wasn't a big deal. But it didn't like the reference to <pre>Range("A3").CurrentRegion</pre>

I had define it as an Excel range, otherwise it thought it was a Word object. I also had to move the definition of xlsApp to become a module level variable, and to use it in the Workbook activate event:

[pre]
Dim xlsApp As Excel.Application

Private Sub imgSubmit_click()
Dim wb As Workbook


'code to make sure no other instance of excel is open

'open the contact xls
Set xlsApp = New Excel.Application
'xlsApp.Visible = True
xlsApp.Workbooks.Open FileName:="c:contacts.xls"
Call AddToExcel
ActiveWorkbook.Close SaveChanges:=True
xlsApp.Quit
Set xlsApp = Nothing
' Call ClearTextBoxes
If MsgBox("Contact has been entered, do you want to enter another?", vbYesNo) = vbNo Then
End
End If

End Sub

Private Sub AddToExcel()

Dim rngData As Excel.Range
Dim rngRow As Excel.Range
Dim lastRow As Integer

xlsApp.Worksheets("Data").Activate
Set rngData = Excel.Range("A3").CurrentRegion

lastRow = rngData.Rows.Count + 1
Excel.Range("A" & lastRow) = txtDate.Text

End Sub
[/url]

[jha900]

Thank you very much. I have a reference to excel 10.0 object library. I changed the code, see below. I still get a page fault error when i try it on a w95 box but i tested on both nt and w2000 and it works fine. Also, on the w95 box it does open excel but then i get a page fault error.

dim xlsApp as excel.application
private sub submit_click()
dim wb as workbook

set xlsApp = new Excel.Application
xlsApp.Workbooks.Open filename:=App.Path & "contacts.xls"
call addtoexcel

if msgbox("enter another?", vbyesno) = vbno then
activeworkbook.close saveChanges:=true
xlsApp.quit
set xlsApp = nothing
end

else
call cleartextboxes
end if
end sub

private sub addtoexcel()
dim rngData as range
dim rngRow as range
dim lastRow as integer

worksheets("Data").activate
set rngData = xlsApp.Range("A3").currentregion
lastRow = rngData.rows.Count + 1
xlsApp.Range("A" & lastRow) = txtDate.text
end sub