Excel 2000 and VB

[Chris]

I am currently using the following code to read in some
values from my VB App and place them into a preformatted
excel sheet. When my App is run by users on Win NT
machines with Office 97 it works perfectly. But when users
who are using XP Pro and Office 2000 run this code
(executed by a button); the worksheet in Excel 2000 loads
up and the values from my App are successfully transfered
but Excel has infact 'frozen' (hung/crashed etc). Does
anyone know a work around for this problem ?

Private Sub Command28_Click()

Dim ExcelSheet As Object

Set ExcelSheet = GetObject("h:\apps\ett\VRF.xlt")
ExcelSheet.Application.Visible = True
ExcelSheet.Parent.Windows(1).Visible = True

ExcelSheet.Worksheets(1).Range("e28").Value =
MakePayableTo.Text
ExcelSheet.Worksheets(1).Range("e31").Value = Address1.Text
ExcelSheet.Worksheets(1).Range("e35").Value = Address2.Text
ExcelSheet.Worksheets(1).Range("e38").Value = Address3.Text
ExcelSheet.Worksheets(1).Range("e47").Value = PostTown.Text
ExcelSheet.Worksheets(1).Range("e41").Value = Address4.Text
ExcelSheet.Worksheets(1).Range("e44").Value = Postcode.Text

Set ExcelSheet = Nothing
End Sub

 

[Beth Melton]

Hi Chris,

If you don't get the answer you are looking for here then you might
try posting in the Excel Programming newsgroups. That's where the
Excel VBA experts tend to hang out.

--
Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

 

[SA]

Chris:

In looking at your code, I guess there's a few things that I change;
you seem to skip over a number of items in your routine (principally in
the area of saving the changes):

1.) Set ExcelSheet = GetObject("h:\apps\ett\VRF.xlt")

You might do better with a more hierarchical run at the object

model as in:

Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.OpenWorkbook("h:\apps\ett\VRF.xlt")
Set objSheet = objBook.Worksheets(1)

Then you can address objSheet's ranges directly; doesn't
even need to be the active sheet.

2.) ExcelSheet.Parent.Windows(1).Visible = True

It looks like you are trying to activate the first sheet in the

workbook. Your better route would be code like this:

objSheet.Activate or

objBook.Worksheets(1).Activate

This is also true because the Windows collection
includes all the windows in the Workbook and if as an
example you include a chart its a Window too.

3.) When closing up a Workbook do the proper clean up as in

objBook.Close SaveChanges:=True

or objBook.Save

Without the explict save when you set ExcelSheet = Nothing
Excel will prompt for saving and you probably are hanging because
of a dialog box that's waiting for input from the user.

4.) Then once close use
objExcel.Quit
Set objExcel = Nothing