473,842 Members | 1,866 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

GetObject and Excel - working with VB 5

I found the following under the GetObject help notes and in the
example for GetObject:

"This example uses the GetObject function to get a reference to a
specific Microsoft Excel worksheet (MyXL). It uses the worksheet's
Application property to make Microsoft Excel visible, to close it, and
so on. Using two API calls, the DetectExcel Sub procedure looks for
Microsoft Excel, and if it is running, enters it in the Running Object
Table. The first call to GetObject causes an error if Microsoft Excel
isn't already running. In the example, the error causes the
ExcelWasNotRunn ing flag to be set to True. The second call to
GetObject specifies a file to open. If Microsoft Excel isn't already
running, the second call starts it and returns a reference to the
worksheet represented by the specified file, mytest.xls. The file must
exist in the specified location; otherwise, the Visual Basic error
Automation error is generated. Next, the example code makes both
Microsoft Excel and the window containing the specified worksheet
visible. Finally, if there was no previous version of Microsoft Excel
running, the code uses the Application object's Quit method to close
Microsoft Excel. If the application was already running, no attempt is
made to close it. The reference itself is released by setting it to
Nothing."
Wonderful!

BUT, what I want is to reference the current open file that has the
focus in Excel - I do not want to reference the specified file,
mytest.xls. See "LOOK HERE" below.
' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindow A" (ByVal lpClassName as String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessag eA" (ByVal hWnd as Long,ByVal wMsg as Long _
ByVal wParam as Long _
ByVal lParam As Long) As Long

Sub GetExcel()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunn ing As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application
isn't
' running, an error occurs.
Set MyXL = Getobject(, "Excel.Applicat ion")
If Err.Number <> 0 Then ExcelWasNotRunn ing = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,

' enter it into the Running Object table.
DetectExcel

'LOOK HERE
'Set the object variable to reference the file you want to see.
Set MyXL = Getobject("c:\v b4\MYTEST.XLS")
'LOOK HERE

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Applicatio n.Visible = True
MyXL.Parent.Win dows(1).Visible = True
' Do manipulations of your
' file here.

' ...
' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunn ing = True Then
MyXL.Applicatio n.Quit
End IF

Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
End Sub

Thanks,
Otie
Oct 19 '05 #1
3 20296
Here's my solution, in case it helps anyone:

Public Sub Copy_Data_to_Ex cel()
Dim x1%, y1%
Dim current_col%, current_row%
Dim xls_filename$, active_sheet_na me$

Dim MyXL As Object ' Variable to hold reference to
Microsoft Excel.
Dim ExcelWasNotRunn ing As Boolean ' Flag for final
release.
Dim mySheet As Excel.Worksheet

'test to see if there is a copy of Microsoft Excel already
running.
On Error Resume Next ' Defer error trapping.

'getobject function called without the first argument returns
a reference to an instance
'of the application. If the application isn't running, an
error occurs.
Set MyXL = GetObject(, "Excel.Applicat ion")
If Err.Number <> 0 Then
ExcelWasNotRunn ing = True
MsgBox "Please open an Excel spreadsheet before performing
this operation", vbCritical, "No spreadsheet open"
Set mySheet = Nothing
Set MyXL = Nothing
Exit Sub
End If

Err.Clear ' Clear Err object in case error occurred.

'check for Microsoft Excel. If Microsoft Excel is running,
enter it into the Running Object table.
Call DetectExcel

xls_filename$ = MyXL.Applicatio n.ActiveWindow. Parent.FullName
'full name of .xls file
active_sheet_na me$ = MyXL.Applicatio n.ActiveSheet.N ame
'active sheet within that file

'set the object variable to reference the file you want to
see.
Set MyXL = GetObject(xls_f ilename$)

Set mySheet = MyXL.Worksheets (active_sheet_n ame$)

'show Microsoft Excel through its Application property. Then
show the actual window
'containing the file using the Windows collection of the MyXL
object reference.
MyXL.Applicatio n.Visible = True
MyXL.Parent.Win dows(1).Visible = True

'get active cell - this is where program will start to copy
data
current_col% = MyXL.Applicatio n.ActiveCell.Co lumn - 1
current_row% = MyXL.Applicatio n.ActiveCell.ro w - 1

'set column width
mySheet.Columns (5 + current_col%).C olumnWidth = 15 '15 =
number of characters

'set centering
mySheet.Columns (2 + current_col%).H orizontalAlignm ent =
xlCenter
mySheet.Columns (3 + current_col%).H orizontalAlignm ent =
xlCenter
mySheet.Columns (4 + current_col%).H orizontalAlignm ent =
xlCenter
mySheet.Columns (5 + current_col%).H orizontalAlignm ent =
xlCenter
mySheet.Columns (6 + current_col%).H orizontalAlignm ent =
xlCenter
mySheet.Columns (7 + current_col%).H orizontalAlignm ent =
xlCenter

'set number of digits to the right of the decimal point
Data_Grid!grdOu tput.col = 1
Data_Grid!grdOu tput.row = 1
If IsNumeric(Data_ Grid!grdOutput. Text) Then
If conversion_fact or <= 180 Then
mySheet.Columns (2 + current_col%).N umberFormat =
"0.00"
Else
mySheet.Columns (2 + current_col%).N umberFormat = "0.0"
End If
End If

'set the format for date and time stamp
Data_Grid!grdOu tput.col = 4
Data_Grid!grdOu tput.row = 1
If IsDate(Data_Gri d!grdOutput.Tex t) Then
mySheet.Columns (5 + current_col%).N umberFormat = "dd mmm
yyyy hh:mm"
End If

'copy data - each column, then each row
For y1% = 1 To Data_Grid!grdOu tput.Rows - 1
For x1% = 0 To 6 '7 columns of data total
Data_Grid!grdOu tput.col = x1%
Data_Grid!grdOu tput.row = y1%

mySheet.Cells(y 1% + current_row%, x1% + 1 +
current_col%).F ont.Name = "Rosecast"
mySheet.Cells(y 1% + current_row%, x1% + 1 +
current_col%).F ont.Size = 10
mySheet.Cells(y 1% + current_row%, x1% + 1 +
current_col%).F ont.Color = RGB(0, 0, 0)

If x1% = 1 Or x1% = 3 Then
If Data_Grid!grdOu tput.CellForeCo lor = RGB(0, 192,
0) Then
mySheet.Cells(y 1% + current_row%, x1% + 1 +
current_col%).F ont.Color = RGB(0, 192, 0)
ElseIf Data_Grid!grdOu tput.CellForeCo lor =
QBColor(9) Then
mySheet.Cells(y 1% + current_row%, x1% + 1 +
current_col%).F ont.Color = RGB(0, 0, 255)
ElseIf Data_Grid!grdOu tput.CellForeCo lor =
QBColor(12) Then
mySheet.Cells(y 1% + current_row%, x1% + 1 +
current_col%).F ont.Color = RGB(255, 0, 0)
End If
End If

mySheet.Cells(y 1% + current_row%, x1% + 1 +
current_col%).V alue = Data_Grid!grdOu tput.Text
Next x1%
Next y1%
'done

'if this copy of Microsoft Excel was not running when you
started, close it using the Application property's Quit method.
'note that when you try to quit Microsoft Excel, the title bar
blinks and a message is displayed asking if you
'want to save any loaded files.
If ExcelWasNotRunn ing = True Then
MyXL.Applicatio n.Quit
End If

Set mySheet = Nothing 'release resources
Set MyXL = Nothing
End Sub
Declare Function FindWindow Lib "user32" Alias "FindWindow A" (ByVal
lpClassName As String, ByVal lpWindowName As Long) As Long
Public Sub DetectExcel()
'This procedure detects a running Excel application and registers it.
Const WM_USER = 1024
Dim hWnd As Long

'If Excel is running, then this API call returns its handle.
hWnd = FindWindow("XLM AIN", 0)

If hWnd = 0 Then
'0 means Excel not running.
Exit Sub
Else
'Excel is running so use the SendMessage API function to enter
it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub
---
Allen

Free astrology software at:
http://www.astrowin.org

"Otie" <ot*********@ad elphia.net> wrote in message
news:Rp******** ************@ad elphia.com...
I found the following under the GetObject help notes and in the
example for GetObject:

"This example uses the GetObject function to get a reference to a
specific Microsoft Excel worksheet (MyXL). It uses the worksheet's
Application property to make Microsoft Excel visible, to close it, and so on. Using two API calls, the DetectExcel Sub procedure looks for
Microsoft Excel, and if it is running, enters it in the Running Object Table. The first call to GetObject causes an error if Microsoft Excel isn't already running. In the example, the error causes the
ExcelWasNotRunn ing flag to be set to True. The second call to
GetObject specifies a file to open. If Microsoft Excel isn't already
running, the second call starts it and returns a reference to the
worksheet represented by the specified file, mytest.xls. The file must exist in the specified location; otherwise, the Visual Basic error
Automation error is generated. Next, the example code makes both
Microsoft Excel and the window containing the specified worksheet
visible. Finally, if there was no previous version of Microsoft Excel running, the code uses the Application object's Quit method to close
Microsoft Excel. If the application was already running, no attempt is made to close it. The reference itself is released by setting it to
Nothing."
Wonderful!

BUT, what I want is to reference the current open file that has the
focus in Excel - I do not want to reference the specified file,
mytest.xls. See "LOOK HERE" below.
' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindow A" (ByVal lpClassName as String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessag eA" (ByVal hWnd as Long,ByVal wMsg as Long _
ByVal wParam as Long _
ByVal lParam As Long) As Long

Sub GetExcel()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunn ing As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application
isn't
' running, an error occurs.
Set MyXL = Getobject(, "Excel.Applicat ion")
If Err.Number <> 0 Then ExcelWasNotRunn ing = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,

' enter it into the Running Object table.
DetectExcel

'LOOK HERE
'Set the object variable to reference the file you want to see.
Set MyXL = Getobject("c:\v b4\MYTEST.XLS")
'LOOK HERE

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Applicatio n.Visible = True
MyXL.Parent.Win dows(1).Visible = True
' Do manipulations of your
' file here.

' ...
' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunn ing = True Then
MyXL.Applicatio n.Quit
End IF

Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
End Sub

Thanks,
Otie

Oct 20 '05 #2

"Otie" <ot*********@ad elphia.net> wrote in message
news:rL******** *************** *******@adelphi a.com...
Here's my solution, in case it helps anyone:
(snipped long code)

I think you can clean that up a little. Consider the line
Set MyXL = GetObject(, "Excel.Applicat ion")
If this does not return an error, then you have gotten hold of an active
instance of Excel. So
Call DetectExcel
... Set mySheet = MyXL.Worksheets (active_sheet_n ame$)


is redundant - you already have the excel app.

Delete all that, and instead just put

Set mySheet = MyXL.Applicatio n.ActiveSheet

Oct 20 '05 #3
Thank you. I'll try it.
--
---
Otis
"Steve Gerrard" <my********@com cast.net> wrote in message
news:Ps******** ************@co mcast.com...

"Otie" <ot*********@ad elphia.net> wrote in message
news:rL******** *************** *******@adelphi a.com...
Here's my solution, in case it helps anyone:
(snipped long code)

I think you can clean that up a little. Consider the line
Set MyXL = GetObject(, "Excel.Applicat ion")


If this does not return an error, then you have gotten hold of an

active instance of Excel. So
Call DetectExcel
...

Set mySheet = MyXL.Worksheets (active_sheet_n ame$)


is redundant - you already have the excel app.

Delete all that, and instead just put

Set mySheet = MyXL.Applicatio n.ActiveSheet

Oct 28 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
4298
by: Rich | last post by:
Hello, I have not been working with ASP for too long at this time and am not real familiar with a lot of things about ASP. I have searched for articles on the following question but not come up with any definite answers. I would like to know if it is possible to use GetObject in order to read/write data between an ASP and an activeX application. VBS supports GetObject, so I thought ASP might, but I have not had any luck. ...
2
2250
by: Andrei Prodan | last post by:
Hi everyone, I have a small piece of code which uses GetObject function and it is working fine in a windows application developed under .NET In a windows service application, the GetObject function returns an error: "Cannot create ActiveX component" Does anyone knows why ??
5
6366
by: Andrei | last post by:
Hello, I posted yesterday the same problem, but now I want to be more specific about it. My intention is to create a windows service to process some Word documents. If I use CreateObject to create a reference to a Word object everything is working fine. If I use GetObject to get a reference to Word object (of
4
6402
by: bic | last post by:
When porting my Windows app to asp.net I get an System.Exception: Cannot create ActiveX component. What function can I use instead? Thanks for your comments. -- bic
2
2538
by: Randy Harris | last post by:
I'm going nuts trying to figure this out, sure hope someone can help. My application uses TransferSpreadsheet to insert data into an existing Excel spreadsheet (which works), then opens and manipulates it. Updates some pivot tables, does some formatting, etc. It works fine on several systems, but I can't get it to work on one system. It uses GetObject to open the spreadsheet. On the one system on which it won't work, instead of...
1
5359
by: Franck | last post by:
Hi, It's a bit tricky I'm launching an Excel process with a RunAs like function, in order not to launch it with ASPNET account but another one. I'm not (and I can't be) using CreateObject("Excel.Application") to set my object. Does a function exist in order to get an object from a Process ID ? I can't use MyXlObj = GetObject(,"Excel.Application") because several Excel process could run.
0
1227
by: dermot | last post by:
Hi all I am trying to trap when a user opens Excel/Word, and attach to the events of that process. I can use the Process class to get all processes, but I can't see a link to an object that can trap events, like GetObject. GetObject requires a class reference (what if there are two open?) or a filename (how to get the filename?)
0
10177
by: mammucion | last post by:
Trying to automate processing 80,000 data sets through 15 web pages. Application URL creates a new IE instance in which runs first a login form and then runs the rest of the pages in the new window. I can connect to a running Excel instance and obtain information from the Excel DOM. I cannot do the same with Internet Explorer. However, I can successfully create an new IE instance and manipulate it. Using an application that has its own...
0
1120
by: Piotr Kosinski | last post by:
Hi, I noticed strange behaviour in Excel. I wrote small shared add in for Excel. This util create button in standard bar. Button click provides to open a form which has to import data from sql server and present on listbox, double click date should be paste into activecell in excel. Because shared add in is loaded with excel i use: myexcel = getobject(, "excel.application")
0
9865
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10934
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10668
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10303
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9446
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7853
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7025
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5882
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3140
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.