473,544 Members | 2,236 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Macros & Auto updates in ACCESS & EXCEL

3 New Member
I have an excel file that automatically updates when opened, pulling information from an Access database into into various pivot tables.

I created a macro in Access to perform various necessary functions with the data.

I want to be able to open an Access form that I created, and then hit a button that will run the macros and that will open the excel database.

The only problem that I am having is that when the excel file opens and tries to update, it can't because the access database is still open...

I need to make this user friendly for another colleague.

Is there any way to solve this one? Aside from opening access, running the macros manually, closing access and then opening excel?

I was wondering if it is possible to have the macro run the updates, close the database and then open the excel file automatically.. .

Thanks in advance for your help!!
May 14 '07 #1
4 4388
Suzette
3 New Member
Forgot to mention I have MS Access 2003
May 14 '07 #2
ADezii
8,834 Recognized Expert Expert
I have an excel file that automatically updates when opened, pulling information from an Access database into into various pivot tables.

I created a macro in Access to perform various necessary functions with the data.

I want to be able to open an Access form that I created, and then hit a button that will run the macros and that will open the excel database.

The only problem that I am having is that when the excel file opens and tries to update, it can't because the access database is still open...

I need to make this user friendly for another colleague.

Is there any way to solve this one? Aside from opening access, running the macros manually, closing access and then opening excel?

I was wondering if it is possible to have the macro run the updates, close the database and then open the excel file automatically.. .

Thanks in advance for your help!!
  1. Can you Link to Excel and create the Pivot Tables in Access?
  2. Have you tried opening an Instance of Access, (via Automation), within Excel then running the Macros from within Excel?
  3. Are you using Excel exclusively for its Pivot Table functionality?
May 14 '07 #3
Suzette
3 New Member
  1. Can you Link to Excel and create the Pivot Tables in Access?
  2. Have you tried opening an Instance of Access, (via Automation), within Excel then running the Macros from within Excel?
  3. Are you using Excel exclusively for its Pivot Table functionality?
Thanks for your reply, I really appreciate it!

1) I tried to build the report and pivot tables in access first, but was not able to get it to where I need it - creating multiple pivot tables that have different fields and are not connected, putting them in one place, and then summing amounts from them. I also need to manually input one cell record daily.

2) I have not tried opening an Instance of Access, (via Automation), within Excel then running the Macros from within Excel but would like to - could you direct me to where I can get info on how?

3) I am using excel not only to create the different pivot tables, but to also manually update that one cell amount into the spreadsheet daily, and then get other sums of various amounts.

I made a temporary fix by making a desktop shortcut to the macro that would automatically close following the update, so the user can double click on that, wait a second for it to finish, and then open up the excel file...but otherwise I'm out of ideas

Thanks again for your help!!
May 14 '07 #4
ADezii
8,834 Recognized Expert Expert
Thanks for your reply, I really appreciate it!

1) I tried to build the report and pivot tables in access first, but was not able to get it to where I need it - creating multiple pivot tables that have different fields and are not connected, putting them in one place, and then summing amounts from them. I also need to manually input one cell record daily.

2) I have not tried opening an Instance of Access, (via Automation), within Excel then running the Macros from within Excel but would like to - could you direct me to where I can get info on how?

3) I am using excel not only to create the different pivot tables, but to also manually update that one cell amount into the spreadsheet daily, and then get other sums of various amounts.

I made a temporary fix by making a desktop shortcut to the macro that would automatically close following the update, so the user can double click on that, wait a second for it to finish, and then open up the excel file...but otherwise I'm out of ideas

Thanks again for your help!!
To Run Access Macros from within Excel:
  1. First and foremost, you must set a Reference to the Microsoft Access 11.0 Object Library (Access 2003).
  2. Declare an Object Variable as type Access.Applicat ion in the Declarations Section of a Worksheet Module NOT within the Click() Event of the Command Button containing the primary code. This is because the Object Variable will immediately go out of Scope as soon as the Click() Event ends and you'll never see the Instance of Access being launched.
    Expand|Select|Wrap|Line Numbers
    1. Dim objAccessAuto As Access.Application
  3. In the Click() Event of a Command Button on the same Sheet where you made the Object Variable Declaration, place the following code:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub CommandButton1_Click()
    2.  
    3. 'Specifically an Access 2003 Application
    4. Set objAccessAuto = CreateObject("Access.Application.11")
    5.  
    6. objAccessAuto.Visible = True
    7.  
    8. '(True Argument) - Open the Database Exclusively - probably a good idea
    9. objAccessAuto.OpenCurrentDatabase "C:\Test\Test.mdb", True
    10.  
    11. objAccessAuto.DoCmd.RunMacro "mcrTest"
    12.  
    13. End Sub
  4. C:\Test\Test.md b will be opened in an Access Window, and mcrTest will be run. In this particular case it simply executes a Query.
  5. OpenCurrentData base() is specifically designed to work within the OLE Automation context - you can reference it through the Help Files.
May 14 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
5816
by: Prakash | last post by:
Hi, We face problems uploading excel (with macros) documents using HTML File Upload. The file contents are corrupted while viewing the same. However, we are able to upload excel (w/o. macros) documents successfully. Is there anything we have to take care of, while handling uploads of excel documents with macros? Thanks in advance,
11
4030
by: Mr. Smith | last post by:
Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it again, Excel hangs. OR if I open Excel again (say from a desktop icon) before I close Access, Excel hangs. (this has happened for both 97 & 2000...
6
2795
by: Reg | last post by:
At present I'm running a number of macros with a schedular on my computer to update and maintain databases within our network. All my backends contain the tables that the macros update. But all the backends are on servers that do not have access loaded on them. I don't think it's possible to activate these macros from the servers schedular...
2
1676
by: Josh E | last post by:
Is it possible to write custom macros in Access, or are you only able to use the ones they allow? If so, how do you go about accessing the Editor. One more question, what is a good source to learn how to write a macro that exports certain data from Access into Excel, then formats it. I have queries written in Access and a macro that...
2
2952
by: Stan Smith | last post by:
I couldn't decide which newsgroup to post this in so I thought I would start here. I can create an "OLE Object" field manually in Microsoft Access and insert an Excel spreadsheet or a Word document, etc. into the field. I would like to be able to programmatically do the same thing. Basicaly I want to be able to add and retrieve and...
3
3990
by: dan_roman | last post by:
Hi, I developed a script with a nice interface in Tkinter that allows me to edit some formulas and to generate an Excel worksheet with VBA macros within it. The script runs perfectlly in Office 2000, but in Office 2003 crash at line: "wbc = workbook.VBProject.VBComponents.Add(1)" Please help me :-( the code of the module that crash is...
0
5532
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted ******************************************************** For this teeny job, please refer to: http://feeds.reddit.com/feed/8fu/?o=25
11
4679
by: Max Vit | last post by:
I have deployed few Access apps splitting it in Front End and Back End. Our environment uses Win XP SP2 for clients, Win 2k3 for servers and Access 2003. The max. number of clients is about 50 (concurrent users is estimated around 10). Whilst the Back End always lives on a server, I am not quite clear where the Front End should live. I...
5
1525
by: Salad | last post by:
If I click on the Macros button in the database window there is 1 macro that exists...AutoExec. If I click on the Modules button and enter the VB Editor window there's the menu option Tools. Clicking on that there's the menu pad Macros... If I click on that pad, a window pops up with the caption "Macros". Inside is a list of "macros". ...
0
7438
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...
0
7374
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...
0
7783
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7392
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...
0
7720
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...
1
5309
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...
0
4930
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...
0
3430
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...
0
3422
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.