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
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 ??
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?
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?
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.
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.
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.
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.
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.
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"
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?
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!!
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.
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.
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"!!!!!!
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 ?
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.
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.
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.
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