Run Excel Macro From MS Access
Sep 11, 2006
man am i stuck.......here goes. i am using a mdb that i upload a weekly excel file to build my weekly report.
the weekly excel file is called Subledger Current.xls
before i upload the excel file i run a macro that is stored in an a different file called Converter.xls on my c drive (C:Converter.xls). the macro name is "Converter_Macro". also, the workbook is hidden, therefore, when i open this ms excel file i have to go to window and unhide to view the workbook. after that, i have verified that the macro is part of the list under Tool Macro Macros.
With that said, can anyone identify my error in my code why the macro is not running. that is, the code does not error out but the macro is not running.
thanks
terry
Code:Private Sub cmdImport_Click()Dim appExcel As Excel.ApplicationSet appExcel = New Excel.ApplicationExcel.Application.Workbooks.Open "C:Subledger Current.xls"Excel.Application.Visible = False'this is where i call another sub to open the file containing the macro and attempt to run itCall Sub xlAddin()'i have addition code here to finish out the prodecure which is function properly when i step through manually.End SubSub xlAddin() Dim objExcel As Excel.Application Set objExcel = CreateObject("Excel.Application") objExcel.Workbooks.Open "C:Converter.xls" ' Runs the macro objExcel.Workbooks("Converter.xls").RunAutoMacros (xlAutoOpen) objExcel.Application.Run (Converter_Macro) objExcel.Quit Set objExcel = NothingEnd Sub
View Replies
ADVERTISEMENT
Jun 14, 2004
Hi all,
I was hoping that someone could tell me if it is possible to run an Excel macro from an Access database. I am importing 3 Excel worksheets into Access and this macro needs to be run on all 3 evertime that this file is imported by the user (twice a week). If this can be done could someone please tell me how ??
View 14 Replies
View Related
Sep 2, 2012
I've been opening comma delimited files in Access, used an Excel function to re-save those files in .xls which Access readily understands, then opening said files and running a query on them to organize them. Now the file needs to go to Excel where it is entered into a worksheet with a certain heading.
Problem is when opening the .xls converted file in Excel, no macros show up. If I open a blank worksheet in Excel my macro shows up. I lowered the security settings to the most basic level in Excel. How do I make the macro show up in the file opened in Excel through the Access VBA, so I can run it?
View 14 Replies
View Related
Dec 7, 2004
I am trying to populate an Excel Shreadsheet (Template in Effect) with Data from Access. This is going ok no problem. Although I need to run an excel macro, which does some formatting to the WorkSheet after each entry in the Access Recordset. I've tried copying the VB over, but getting some errors, and frankly, I think it's easier if I just call the macro itself, rather than try to adapt it for the Access context. Though, I accept it would be a cleaner approach. Can I do this?
View 1 Replies
View Related
Aug 25, 2013
i want to export a table to excel , open this file and execute a macro from another file.
the code i have now is :
Code:
DoCmd.OpenTable "Overzichtaanwezigheid", acViewNormal
DoCmd.RunCommand acCmdExportExcel
DoCmd.Close acTable, "Overzichtaanwezigheid"
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open ("C:UsersErwinDocumentsOverzichtaanwezigheid.xlsx")
XL.Visible = True
XL.Run "d: est.xlsm!Macro3"
Opening the excel file goes ok, running the macro however not.
View 1 Replies
View Related
Mar 26, 2008
I have managed to build a macro that will run a query I have in Access but what I would like to do is be able to change some of the query criteria using the macro script or any other way possible.
Is this possible?
Thanks
View 1 Replies
View Related
Apr 5, 2012
I am using Office 2007 and trying to export a table in Access to a Macro-Enabled workbook in Excel. Unfortunately, when I go to export the table, my file does not show when I browse for the file and .xlsm doesn't look like it's a supported file extension. I have looked around and noticed others have this problem as well. One solution was to use save the Excel file as a 97-2000 file since it doesn't change the file extension based on having macros. However, I can't do this because then I lose functionality with tables and other things on my spreadsheet. I need the data in Excel to be updated every month. Is there any way to do this in Access? I'm going to explore using sharepoint. My DB options are limited to Access and Sharepoint. I don't have access to Oracle or SQL Server or MySQL etc.
View 1 Replies
View Related
Dec 11, 2013
I'd like to use a macro to display only those items which stay as a group in "Old Project Code" (black font) and filter out those which are singles in "Old Project Code" (red font) as shown below extracted out from a worksheet called "Project" in Excel.
New_Project_Code Old_Project_Code
62409 AU028
62410 SAU029
62411 ZZZ001
62963 ZZZ010
62964 ZZZ010
62965 ZZZ010
62966 ZZZ010
62967 ZZZ010
62412 ZZZ050
62973 ZZZ061
62974 ZZZ061
62975 ZZZ061
1. The color wasn't there in original sheet. It is here only for demo purpose.
2. Data are changing dynamically, so need a macro in place to catch a snapshot at the time of macro is firing.
View 1 Replies
View Related
Jun 26, 2015
I have a database which I've creating but now want to make others available to update - in the past this has not gone well! The database uses data from two excel sheets and the problem has been people do not always check if these have been updated today before running and therefore the database gets loaded with old data.I have found code that will check and display a message showing datelastmodified for a file but what i'm trying to find is code which will check both files' datelast modified, if they are modified today then run the update macro, if one or both are not modified today, then to display a message saying that the excel is not updated.
View 7 Replies
View Related
Jan 27, 2015
Is there a way to create some sort of an EXE or BATCH file in any Windows Explorer folder, that executes me a specific Excel Macro?
My problem is that I got several files split up by Departments, updated every day.
So far I have to open each file and update the format.
It would make my life much more easier if I could just run a file that opens each file and applies a format to them.
View 1 Replies
View Related
Dec 8, 2013
I am trying to write a more complex macro that will start another macro at a preset time, however I am getting stopped at the first hurdle - getting a macro to run another macro.
Here is the code i am using at the moment, all I want to do currently is click the first button, then get the second macro to execute. But no luck, getting error 2157 "cannot find the procedure"
Code:
Private Sub Command3_Click()
MsgBox "1st macro running", vbExclamation, "Note"
Application.Run "teststart1"
' Application.OnTime TimeValue("19:55:00"), "teststart1"
[code]....
View 1 Replies
View Related
Apr 25, 2013
I am using Excel and Access 2010.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
View 7 Replies
View Related
Dec 3, 2007
Hi,
Task: to extract data from table 1 of a database (tied to form 1) to create a record in another table 2 of the same database (tied to form 2).
Besides, I need to make it simple to use for an end user.
On the form 2 I have a command button that activates macro. Macro makes a query to run and extract data from table 1 and append a table 2.
Now I want the user to see the record on the screen (form 2) that has just been created. For that purpose I add "go to last record" step in the macro.
Problem: that doesn't work. :) For some reason it brings back same record from the middle of the table which is not the last. And what is even more interesting is that it doesn't tie to the record ID on the bottom of the screen (access generated).
Challenge: the record ID field in the table 2 is a primary key auto-numbered field (i know this is not perfect but I am not the one who created the database) and some records have been deleted over time. Might that be connected to that or that is something else?
Thank you!!
View 1 Replies
View Related
Oct 7, 2005
I have recently made a database and through MS Development Tools have been able to bundle it with Access Runtime. We are using MSDE as a backend and when I install the 'package' created by MS Project on another computer it says that it cannot find the SQL Server database.
This will mean (i believe) I will have to write a macro to reference the external SQL database (MSDE)?
If anyone has knowledge of this or/ and can point me in the right direction it would be appreciated.
Phil
View 3 Replies
View Related
Mar 28, 2006
Hello
I am using the code below to populate three fields by selecting the data from one combo box
In the AfterUpdate event of a cmbBox I have entered the code:
Me.[Part Code] = Me.cmbBox.Column(0)
Me.Description = Me.cmbBox.Column(1)
Me.Cost = Me.cmbBox.Column(2)
When I then try to select from the drop down list I get a message sying that the Macro cannot be found. What am I missing?
View 1 Replies
View Related
Jan 5, 2006
I have a MS Access database which requires that a MS Word document be opened via a macro.
In the Macro design view under the Action tab I have the Macro set to RunApp. Then in the Command Line I have:-
C:Program FilesMicrosoft OfficeOFFICE11WINWORD.EXE C:Documents and SettingsKnightALDesktopDataBase AssignmentLetterhead.doc
When the macro is run I get an error. It begins to open the Letterhead.doc document. Word begins to open but then a messages saying Word experienced an error trying to open the file. With as few suggestions on what to try none of which make any difference.
If I change the Command Line to read:-
C:Program FilesMicrosoft OfficeOFFICE11WINWORD.EXE
Then all is well and a blank Word document opens.
I have tried quite a number of variants in the command line but all give me no joy at all. So can any one tell me please what it is I am doing wrong here. I can even get the macro to open my Spyware program but cannot get a Word document to do its thing.
I have put copies of the document in all sorts of places but still it cannot be detected.
View 7 Replies
View Related
Oct 20, 2006
As I can't seem to find another way to make Access add together two seperate columns of numbers (i.e in Excell it would be A2+B2 = ) I've created a macro of some 42 lines long that copies information from a form into a table, from where it's summed in a query and the results shown on another form. The macro "Steps" through the process without a problem and will rune through the process with "ECHO - YES" selected. However, if I select "ECHO - NO" the screen freezes (although from the "Bong" it appears the macro has finished running) with the tool bars at the top missing and only the initial form infront of the Database window, on display. I've left the computer (actually a couple of computers - a laptop, a high spec desk top and the works' networked computer) for up to 15 mins without anything changing. Once (I think) I was able to shut down the database using the normal windows red / white X button in the top corner of the programme screen, otherwise I've had to Ctl / Alt / Del to end the programme. The other strange thing seems to be that I can open and work other programmes while the Access screen is frozen so it looks as if the computer isn't freezing (The task manager CPU useage bar is only up to 15 - 30 % as well)
Any ideas what may be going wrong or how I can get round this? I'd perfer to be able to run the programme in the "ECHO - NO" state as one of the guys I'm building it for is blind and has his computer talking to him. It makes a hell of a racket as the macro runs in "ECHO - YES"!!!!!!
View 1 Replies
View Related
Mar 19, 2013
I have my data updated 3 certain times in a day. So i dont want to bother opening the same access 3 times a day. That access can be opened whole day no problem for me. So, how to run macros automatically in a time which I determine before ?
View 6 Replies
View Related
Jan 23, 2015
I have 1900+ records, of where each record is represents a PDF Document in my folders.Each document in my folders is named in the same format "2014-001a" for example. This is the combination of two fields in my Access Database - Document Number and Current Revision.
I am looking for some Macro/VBA where I can put the same command on every records form, that will then recognise the Document Number and Current Revision of that record, and then "On Click" able to directly load up that Pdf.
View 1 Replies
View Related
Jun 22, 2007
In access 2003 their is a RunApp macro to run applications from a macro. In access 2007 (at least my copy) it does not show up. Is it my copy or has it been taken out of this version.
View 3 Replies
View Related
Jul 26, 2007
Hello,
When I add a butten using the wizard Access 2007 adds a macro instead of vba code.
How can I get the good old VBA code???
Thanks for your help in advance
View 11 Replies
View Related
Nov 11, 2006
Let me have an example of an Access macro importing Sql table to Access table
View 1 Replies
View Related
Dec 20, 2004
Is it possible to create a macro in Access that opens up a word document from a shared drive? Within Access, I don't see a macro available to do this.
View 1 Replies
View Related
Nov 16, 2011
I am trying replicate the northwind web database macro called getproductname getproductlist which is on the products table. this macro has a simple lookup action which looks up a record by a parameter being sent.
the customer order form has a productid combo box. on its after update it finds the product list. this UI macro just passess the product id to the data macro on the table and looks up the price for that id. then sets the value on the form. simple right? however i consistently get an error from the data macro on the table which says the "the identifier (enter field name here) could not be found"
in my case im selecting id and productname from my table. the syntax is correct. but when i run the macro i get "the identifier productname could not be found. ive been struggling with this for hours. and im using a working sample as an example.
View 2 Replies
View Related
Feb 21, 2006
Greetings - some help required please:
I have an intermittent error occurring on an Access 2k form giving the error
message "cant find the macro '.'" although there are no macros on the form.
After much trawling around I believe this may be due to a space being
mis-keyed into one of the event properties of one of the form’s controls but
I have looked at each to no avail.
My question is therefore two fold:
1. If this is due to a space being keyed how do I, in code, step through
each control and it's events looking for it?, or,
2. If it's not a space what else could it be.
The form opens and works Ok most of the time, the db compacts and repairs and compiles to an MDE and the error appears to not be linked to any one control or event but seems to be triggered more often than not if move from the tab order through use of the mouse.
Ideally I don’t want to redo the form or copy the database objects into a new db as there are too many.
Any help gratefully received.
NB This query was originally posted to the vba section without much success apart from a rebuild suggestion gratefully received from WayneRyan. I thought this was a vba error but may be a macro issue, a form error or could be a general problem so apologies if this is not the correct forum, if incorrectly placed Moderators please feel free to move it
Thanks
Kevin
View 1 Replies
View Related
Nov 7, 2006
Ok,
I have a table that has new data added into it through forms, and i want the data to be sorted when a new record is added.
The person that the database is for is new to Access and therefore is not 100% competant at it.
I wanted to add a button onto the forms that would run a macro that would sort the data. Is there a macro in Access that can do this?
Cheers,
Slifer
View 4 Replies
View Related
