Creating and Destroying Excel Objects from VB

[RiosPapa]

Hello i'm not to sure of the Proto-Call for this type of request, If I am out
of bounds
And the request it to big please don't be bashful let me know.

This code stub runs from a VB app I have tested it seems to function
reliabily. My question is about the methods I have employed to create and
destroy the excel objects and the syntax of various interactions with Excel.
Are my DIM, Set, Quit and Nothing statments relating to Excel the best
approach ?

'///////////////////
Sub PassASheet() 'Test Stub
'This code Stub was created to validate concept
'The Excel LIst engine holds all the logic and calulations to build a
spacific list
'I callup the engine and pass in a couple of Params (Hard Coded in this test
stub)
'For saftey purposes the Excel List engine is set to read only so it can't
be broken
'because of a PC crash, or user tampering...
'So the goal is to have the engine generate the new list then this code will
'open another excel workbook and copy the sheet in to it and save this
workbook.

Dim EnginePath As String 'The List engine
Dim TargetPath As String 'The Saved workbook which the DB query points to

'Allocate Object Pointer
Dim xlapp As Excel.Application

'Create Instance
Set xlapp = CreateObject("Excel.Application")

'Set Paths
EnginePath =
"C:\aa_VB_Misc\TestListStub\AMS_SpreadSheets\LogicalMasterList.xls" 'Master
Engine which builds the qualified list
TargetPath = "C:\aa_VB_Misc\TestListStub\AMS_SpreadSheets\ListTarget.xls"
'Workbook which holds validated list and where the Database points

'Call Open Method for the List Engine
xlapp.Workbooks.Open EnginePath

'Select Logic Page
xlapp.Sheets("Logic").Select

'Call the Intiliaze Function on the list generator
xlapp.Run "Module1.Initialize_ListGen"

'Set List Revision Cell
xlapp.Range("Title_RevisionToGenerate").Value = "V1000"

'Set the List Type
xlapp.Range("Title_ListType").Value = "List4"

'All information is now passed and set to the List Engine
'Select the Sheet which holds the Calulated List
xlapp.Sheets("CalulatedList").Select

'Select everything on the list
xlapp.Cells.Select
xlapp.Range("O1").Activate
xlapp.Selection.Copy

'slelect Interm Sheet and paste
xlapp.Sheets("Dynamic_List").Select
xlapp.Range("A1").Select
xlapp.Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Alrighty then the list is now complete and ready to Pass to another
workbook which can be saved

'Frist Open the target work book
xlapp.Workbooks.Open TargetPath

'Delete the Previous Dynamic Work Sheet so you can send in the new one
xlapp.Sheets("Dynamic_List").Select
xlapp.ActiveWindow.SelectedSheets.Delete

'Switch back to the List Engine.
xlapp.Windows("LogicalMasterList.xls").Activate

'Select the Sheet of interest (The one you just built above)
xlapp.Sheets("Dynamic_List").Select

'Copy it to the Target
xlapp.Sheets("Dynamic_List").Copy
Before:=xlapp.Workbooks("ListTarget.xls").Sheets("Empty_Sheet_dont_Delete")

'Close with a Save the Target workbook
xlapp.Application.Workbooks("ListTarget.xls").Close SaveChanges:=True

'Close without! Save the List Engine workbook
xlapp.Application.Workbooks("LogicalMasterList.xls").Close SaveChanges:=False

'Now That we have made such a beautiful list together lets formaly quit our
relationship
xlapp.Quit

'Thout shall Free memory for others to use
Set xlapp = Nothing

End Sub

 

[Vacation's Over]

Ok needs some work

XlApp is the application, sometimes you can use unqualified references and
Xlapp can be used for a workbook or worksheet but this is a bad idea when you
create a new instance of Excel. CRASH

Dim XLapp as Excel.Application ' the way you start is ok

Dim XLTarget as Workbook ' Use this object to specify your Target
Dim XLEngine as Workbook 'Use this object to specify your source

EnginePath =
"C:\aa_VB_Misc\TestListStub\AMS_SpreadSheets\LogicalMasterList.xls" 'Master

TargetPath = "C:\aa_VB_Misc\TestListStub\AMS_SpreadSheets\ListTarget.xls"


Set xlapp = CreateObject("Excel.Application")

Set XLTarget =xlapp.Workbooks.Open TargetPath
Set XLEngine = xlapp.Workbooks.Open EnginePath


''Now use these objects to refer to the 2 different workbooks you are using.
'''XlTarget.Sheets(" ").Cells()

then clean up memory from all 3 objects

 

[RiosPapa]

Thanks for the quick post, this helps to better undestand, only one issue the
synax of
the Set workbook does not work it causes the IDE to spit up...
Set XLTarget =xlapp.Workbooks.Open TargetPath
Set XLEngine = xlapp.Workbooks.Open EnginePath

 

[Vacation's Over]

Sorry, Syntax ...try

xlapp.Workbooks.Open (TargetPath)
Set XLTarget =xlapp.Workbooks(TargetPath)