473,287 Members | 3,228 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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
ExcelWasNotRunning 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 _
"FindWindowA" (ByVal lpClassName as String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (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 ExcelWasNotRunning 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.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = 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:\vb4\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.Application.Visible = True
MyXL.Parent.Windows(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 ExcelWasNotRunning = True Then
MyXL.Application.Quit
End IF

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

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

Public Sub Copy_Data_to_Excel()
Dim x1%, y1%
Dim current_col%, current_row%
Dim xls_filename$, active_sheet_name$

Dim MyXL As Object ' Variable to hold reference to
Microsoft Excel.
Dim ExcelWasNotRunning 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.Application")
If Err.Number <> 0 Then
ExcelWasNotRunning = 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.Application.ActiveWindow.Parent.FullName
'full name of .xls file
active_sheet_name$ = MyXL.Application.ActiveSheet.Name
'active sheet within that file

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

Set mySheet = MyXL.Worksheets(active_sheet_name$)

'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.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True

'get active cell - this is where program will start to copy
data
current_col% = MyXL.Application.ActiveCell.Column - 1
current_row% = MyXL.Application.ActiveCell.row - 1

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

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

'set number of digits to the right of the decimal point
Data_Grid!grdOutput.col = 1
Data_Grid!grdOutput.row = 1
If IsNumeric(Data_Grid!grdOutput.Text) Then
If conversion_factor <= 180 Then
mySheet.Columns(2 + current_col%).NumberFormat =
"0.00"
Else
mySheet.Columns(2 + current_col%).NumberFormat = "0.0"
End If
End If

'set the format for date and time stamp
Data_Grid!grdOutput.col = 4
Data_Grid!grdOutput.row = 1
If IsDate(Data_Grid!grdOutput.Text) Then
mySheet.Columns(5 + current_col%).NumberFormat = "dd mmm
yyyy hh:mm"
End If

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

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

If x1% = 1 Or x1% = 3 Then
If Data_Grid!grdOutput.CellForeColor = RGB(0, 192,
0) Then
mySheet.Cells(y1% + current_row%, x1% + 1 +
current_col%).Font.Color = RGB(0, 192, 0)
ElseIf Data_Grid!grdOutput.CellForeColor =
QBColor(9) Then
mySheet.Cells(y1% + current_row%, x1% + 1 +
current_col%).Font.Color = RGB(0, 0, 255)
ElseIf Data_Grid!grdOutput.CellForeColor =
QBColor(12) Then
mySheet.Cells(y1% + current_row%, x1% + 1 +
current_col%).Font.Color = RGB(255, 0, 0)
End If
End If

mySheet.Cells(y1% + current_row%, x1% + 1 +
current_col%).Value = Data_Grid!grdOutput.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 ExcelWasNotRunning = True Then
MyXL.Application.Quit
End If

Set mySheet = Nothing 'release resources
Set MyXL = Nothing
End Sub
Declare Function FindWindow Lib "user32" Alias "FindWindowA" (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("XLMAIN", 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*********@adelphia.net> wrote in message
news:Rp********************@adelphia.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
ExcelWasNotRunning 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 _
"FindWindowA" (ByVal lpClassName as String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (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 ExcelWasNotRunning 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.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = 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:\vb4\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.Application.Visible = True
MyXL.Parent.Windows(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 ExcelWasNotRunning = True Then
MyXL.Application.Quit
End IF

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

Thanks,
Otie

Oct 20 '05 #2

"Otie" <ot*********@adelphia.net> wrote in message
news:rL******************************@adelphia.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.Application")
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_name$)


is redundant - you already have the excel app.

Delete all that, and instead just put

Set mySheet = MyXL.Application.ActiveSheet

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

"Otie" <ot*********@adelphia.net> wrote in message
news:rL******************************@adelphia.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.Application")


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_name$)


is redundant - you already have the excel app.

Delete all that, and instead just put

Set mySheet = MyXL.Application.ActiveSheet

Oct 28 '05 #4

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

Similar topics

10
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...
2
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...
5
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...
4
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
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...
1
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...
0
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...
0
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...
0
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...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.