Wrox Home  
Search P2P Archive for: Go

  Return to Index  

pro_vb thread: HELP needed on getobject method on linked excel file


Message #1 by jjacquet@i... on Mon, 13 Aug 2001 15:05:22
Julien,

	The current problem you have in your code is that when 
you run the following statement:

Set xlApp = GetObject("C:\Documents and 
Settings\Administrator\Desktop\html\Gs1sslg.xlw", "Excel.sheet")

you get the message are your program is unable to continue for
whatever the reason.

The following lines of code:

xlApp.Application.EnableEvents = False
xlApp.Application.DisplayAlerts = False

are not executed, because the program thread is blocked 
already by your set statement.

In this situation I would make two suggestions:

1) Set the application settings before you open
	the worksheet.

2) Use Early Binding to maintain more control over the
	memory that your application is using.  Also
	by doing this, you expose the properties and
	methods of the objects so that your intellesense
	will pop up.

3) Explicitly define one application object in which all
	your Excel batch processes execute.  This will first
	of all eliminate the chances of a stray excel
	application residing in your memory (A quick glance
	at the task manager may show multiple copies of
	excel.exe running.)  Also, your program will not
	have to spend time loading all the excel application
	each time a file is opening thus greatly speeding
	your execution time.

Here is some example code.  This code contains a
solution to your problem, as well as an example of
early binding.  In order for this code to work,
you will need to go to Project->References and
add, Microsoft Excel 8.0 Object Library.

If you don't have version 8.0 you can use an
earlier version of the library, but this
code may need to be modified a little bit.

----Begin Code---

Dim xlappMain As Excel.Application
Dim xlwbkTemp As Excel.Workbook
Dim xlshtSheet As Excel.Worksheet

Private Sub ProcessFiles()  
  Set xlappMain = New Excel.Application
  xlappMain.EnableEvents = False
  xlappMain.DisplayAlerts = False
  
  'Do loop through all your worksheets
    'replace the filename with a variable of your choice
    xlappMain.Workbooks.Open "c:\myfilename.xls"
    Set xlwbkTemp = xlappMain.ActiveWorkbook
    Set xlshtSheet = xlwbkTemp.ActiveSheet
      
    'Do your processing here.
    
    Set xlshtSheet = Nothing
    Set xlwbkTemp = Nothing
    
    xlappMain.ActiveWorkbook.Close
  'end of do loop
  
  xlappMain.Quit
  Set xlappMain = Nothing
End Sub

----End Code---

Please feel free to post any questions or
comments that you may have.

Hope this helps.

Cardyin

--------------------------------------
Cardyin Kim
C/S & Web Development Analyst
Information Services
San Antonio Community Hospital
ckim@s...     (xxx)xxx-xxxx     
--------------------------------------

  Return to Index