I'm trying to automate an Excel spreadsheet from Access. I've
established a link and loaded data from a recordset onto a worksheet
in Excel. I now want to perform some manipulation on the data. I
previously set up a command button in the spreadsheet to run the VBA
to do this manipulation; this VBA is stored in the button's Click
event. Now that I am learning about Automation, I would like to "call"
this VBA from Access.
Can anyone tell me how I can simulate clicking this command button on
the spreadsheet through VBA from Access?
Or is there another way to approach this such as creating a public
procedure in the spreadsheet which can be called from Access?
Your assistance is very much appreciated.
Oliver 4 4318
On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
<ol*********@ma ilinator.comwro te:
That's right. Change the event procedure from Private to Public, then
call it like any other public procedure.
-Tom.
>I'm trying to automate an Excel spreadsheet from Access. I've established a link and loaded data from a recordset onto a worksheet in Excel. I now want to perform some manipulation on the data. I previously set up a command button in the spreadsheet to run the VBA to do this manipulation; this VBA is stored in the button's Click event. Now that I am learning about Automation, I would like to "call" this VBA from Access.
Can anyone tell me how I can simulate clicking this command button on the spreadsheet through VBA from Access?
Or is there another way to approach this such as creating a public procedure in the spreadsheet which can be called from Access?
Your assistance is very much appreciated.
Oliver
On Nov 7, 4:00 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:
On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
<oliverja...@ma ilinator.comwro te:
That's right. Change the event procedure from Private to Public, then
call it like any other public procedure.
-Tom.
I'm struggling to call a public procedure in a standard module in
Excel from Access, can anyone tell me why the following code does not
work?
In the Excel spreadsheet I have declared the following public function
in a standard module 'PubFns':
Public Function pubintTest()
pubintTest = 100
End Function
In an Access database I have the following subroutine:
Public Sub pubsubRunProc()
Dim xlapp As Excel.Applicati on
Dim xlwkb As Excel.Workbook
Dim intX As Integer
Set xlapp = New Excel.Applicati on
xlapp.Visible = True
Set xlwkb = xlapp.Workbooks .Open("C:\Test1 .xls")
intX = xlapp.pubintTes t
Debug.Print intX
End Sub
When I run pubsubRunProc from the Immediate window the spreadsheet
opens but I get the error message: "Run-time error '438' Object
doesn't support this property or method"
Any assistance would be appreciated.
Cheers,
Oliver
On Nov 7, 5:29 pm, oliver james <oliverja...@ma ilinator.comwro te:
On Nov 7, 4:00 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:
On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
<oliverja...@ma ilinator.comwro te:
That's right. Change the event procedure from Private to Public, then
call it like any other public procedure.
-Tom.
I'm struggling to call a public procedure in a standard module in
Excel from Access, can anyone tell me why the following code does not
work?
In the Excel spreadsheet I have declared the following public function
in a standard module 'PubFns':
Public Function pubintTest()
pubintTest = 100
End Function
In an Access database I have the following subroutine:
Public Sub pubsubRunProc()
Dim xlapp As Excel.Applicati on
Dim xlwkb As Excel.Workbook
Dim intX As Integer
Set xlapp = New Excel.Applicati on
xlapp.Visible = True
Set xlwkb = xlapp.Workbooks .Open("C:\Test1 .xls")
intX = xlapp.pubintTes t
Debug.Print intX
End Sub
When I run pubsubRunProc from the Immediate window the spreadsheet
opens but I get the error message: "Run-time error '438' Object
doesn't support this property or method"
Any assistance would be appreciated.
Cheers,
Oliver
I got an example to work using the same code as yours except I used:
intX = xlapp.Applicati on.Run("pubintT est")
That idea came from recording a macro that ran a macro.
James A. Fortune CD********@Fort uneJames.com
On Nov 8, 12:01 am, CDMAPos...@Fort uneJames.com wrote:
On Nov 7, 5:29 pm, oliver james <oliverja...@ma ilinator.comwro te:
On Nov 7, 4:00 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:
On Tue, 06 Nov 2007 16:58:45 -0800, oliver james
<oliverja...@ma ilinator.comwro te:
That's right. Change the event procedure from Private to Public, then
call it like any other public procedure.
-Tom.
I'm struggling to call a public procedure in a standard module in
Excel from Access, can anyone tell me why the following code does not
work?
In the Excel spreadsheet I have declared the following public function
in a standard module 'PubFns':
Public Function pubintTest()
pubintTest = 100
End Function
In an Access database I have the following subroutine:
Public Sub pubsubRunProc()
Dim xlapp As Excel.Applicati on
Dim xlwkb As Excel.Workbook
Dim intX As Integer
Set xlapp = New Excel.Applicati on
xlapp.Visible = True
Set xlwkb = xlapp.Workbooks .Open("C:\Test1 .xls")
intX = xlapp.pubintTes t
Debug.Print intX
End Sub
When I run pubsubRunProc from the Immediate window the spreadsheet
opens but I get the error message: "Run-time error '438' Object
doesn't support this property or method"
Any assistance would be appreciated.
Cheers,
Oliver
I got an example to work using the same code as yours except I used:
intX = xlapp.Applicati on.Run("pubintT est")
That idea came from recording a macro that ran a macro.
James A. Fortune
CDMAPos...@Fort uneJames.com- Hide quoted text -
- Show quoted text -
That works perfectly thank you.
I also had a case where I had to pass a parameter to the called
procedure. For this I used the following syntax:
intX = xlapp.Applicati on.Run "pubintTest ", "1"
In other words, without the brackets. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: news.btinternnet.com |
last post by:
I can do this in IE
myLink.click(); //Invoking the handler as if the user had clicked on the
link themselves.
I need to be able to do the same in Netscape Navigator,
can anyone help ?
|
by: Pete |
last post by:
I have some funky form/event behavior.
Access 97. Split frontend/backend, using Access security. I have the
same behavior (or lack of behavior) for the pag_Click() event of two
separate pages on different menu forms. Details:
frmTRNMenu is the main menu of the application. It uses tabbed pages
to logically separate menu items. The last tab on the list is
different - it has an image and then "O&M". Clicking on this last
page will...
|
by: George |
last post by:
I'm having a weird problem.
When I double-click a Web server control that I have on my design-time Web
form, such as a button, it puts the event handler in the Code Behind as a
Private Sub routine. However, when I try to compile, I get an error saying
that I can't access that event handler because it is Private.
When I change the event handler from Private Sub to Public Sub, it works
fine, but I know that is not the way it is supposed...
|
by: JD |
last post by:
Hello,
I have a problem with checkboxlist inside Repeater (in ASP.NET page).
I am able to create Checkboxlist and bind it (inside Repeater_ItemBound -
including setting checked/unchecked). Checkboxlist has Autopostback=true so
whenever I click on checkbox, the page is submitted to the server.
Here starts my problem:
I am not able to capture the click event of checkbox list to find out which
checkbox was clicked (which generated click...
|
by: Rusty |
last post by:
I'm using a third party tree view control on my web app. I have built a
web control that is used throughout the application.
When a user clicks on a node in the tree, the nodes click event is
fired. This calls a public event that the consuming page can use as
it's up to that page to do what it wants at that time.
I'm trying to add a new feature in the control that will fire the click
event automatically given a certain scenario. The...
| |
by: prashantnurvi |
last post by:
I have programaticaaly created several Link Buttons on my ASPX page, I would like to raise the Click event of these Link buttons, passing in appropriate parameters depending on the Link button I click. Below is the code I have written to create the Link Buttons
For i as integer = 0 to 10
Dim lnkbtn as new linkbutton
lnkbtn.ID = lnkbtn&i.tostring()
lnkbtn.Text = Test&i.tostring(()
me.controls.add(lnkbtn)
Next
Since the Link buttons...
|
by: Jim Devenish |
last post by:
I have an unbound form that displays all the days of the year as a
calendar. It has 12 rows of text boxes with either 29,30 or 31 in
each row. Text box names are of the form: display_01_01,
display_01_02 ... display_12_31.
This is used to display bookings of holiday properties and works
well. I am now wishing to be able to highlight a range of dates by
double clicking in one text box and then in another, highlighting all
the dates...
|
by: paragbhavsar |
last post by:
Hello Friends,
Can we simulate click event of Input File Control through Javascript - I want this for Firefox 3.5 browser ?
I have also tried with following code. Please let me know solution about this.
<pre>
<a id="myobj" href="#" onclick="document.getElementById('test').click();
">Click me</a>
<input type="file" id="test" name="test" >
|
by: Martin Lang |
last post by:
Hi All,
Thanks for reading this :)
I have a form A that consists of a main form A and a sub form A.
In sub form A, I have a field which I can double click. Then, Main form B opens up with a search field. Sub form B has a query as recordset. The query use the search field value as a filter, and the filter is updated every time text is entered into the search field.
What I want to happen is to set focus on the search field once form b...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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...
| |
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,...
|
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...
|
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...
|
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();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |