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 a bunch of Forms set up in Access that are updating tables etc......
At present 100% of the code is sitting on the Form. I have some code that is repeated because of a string parsing issue. What I am thinking of doing is writing this code as a couple modules and then call them when needed.
Does that make sense to write them as modules and call them?
I have three forms - Form1,Form2 and Form3 linked to a navigation form with Navigation buttons. I have separate VBA codes under each Before update event of these forms, Form1,Form2 and Form3 to call a VBA module to track the changes performed by the users(Like an Audit Trail) in specified fields of the forms which will track them to a table.there are control tags in the form properties --> others --> Tag to track the changes in the fields which are defined in the VBA Module.this is the code to call the VBA Module:
Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Call AuditChanges("UniqueID", "Part No", "NEW") Else Call AuditChanges("UniqueID", "Part No", "EDIT") End If End Sub
[code]....
The code is working perfect when the forms are run separately.but when they are run from the navigation form by clicking the navigation buttons, the code is not working !!
set warnings to warning on: NO and runCode to Function Name: cmdImport_Click()
where cmdImport_Click() is a Private Sub in a module within the same database.
when i try to run the macro i get an error that stated "the expression you entered has a function name that MS Access can't find."
but if i open the module called ImportMSExcelFile and set my cursor on the Private Sub cmdImport_Click() and select the green arrow to run the procedure it runs fine.
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
We have been calling a main form to manage yacht races using a hyperlink/macro from a selection form which lists all the yacht races ..The main form was too big to fit on some screens so we changed it to pop up mode to allow it to be resized and use scroll bars. this works well when the form is opened manually and all functions run just fine
the hyperlinik/macro for race selection had originally been set up to call the main form in dialog mode but we have had to change this as it disabled the resizing of the form and the scroll bars..The macro tool doesn't offer pop up mode so i selected window mode = normal and the main form opens with a resizable window and scroll bars but all the functions called from this module now fail (error message = use of null value or similar and all the functions go into debug mode - the server is down and i can't access the system to get the exact details)
If i change the macro in the selection form back to window mode = dialog the functions work but i lose the resizing of the main form and and the scroll bars.
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?
Following on from that thread, I would really like to tidy up other areas of my front end. I am using the idea that Roy suggested and it works very well.
At the moment, I have a form, that has buttons on it that people click when they want to generate specific sheets from excel using data from access using VBA on the form. These work fine, however, now that the database is going live and all features need to be added, I think it would be nice to have one small form, with a list box.
I have converted a copy of the code on the form and placed in a module by declaring then functions instead of private subs.
At this stage, I tried doing it with macros running the code by using a series of runcode. I then created a table that has the macro name stored in it along with a decriptive bit of text detailing the excel chart that gets produced to make it easy for the user.
The list box populates itself correctly, but the problem I am having is I do not know how to make the 'go' button look at the list box, pull the hidden macro/function name from the list, then go and run the macro/function.
I have tried looking about, but have not found anyhthing.
Hello everybody. I made a small access program. When I run a macro I get a error message saying that the macro can only run 20 times. I tried to solve this problem by having one macro calling another macro, but it does not work. Anybody any idea how to solve this problem. As attched file I send the program in ZIP format. If you want me to send it in not ziped, please tell me. OK how can I send attachements here. I don't see it. So please tell me if you want me to send it to you by mail. My address is bvdhaegen@gmx.de If you go on record 12 and click on refresh, you'll see the problem poping up. Thank you all for your help.
I am writing a module to send information to Excel, and the information is based on a Crosstab query. But I keep getting a "syntax error in the TRANSFORM statement".
Function Edt_Royalties() As Integer Dim AplicExcel As Excel.Application Dim Planilha As Excel.WorkSheet Dim stArq As String Dim DB As Database
I have linked an excel workbook as different tables in Access. The workbook contains a Microsoft Query. I have now found out how I can open and edit the workbook from access, how can I Update/refresh the MSQuery before I close it?
Here is what I have: Private Sub cmdTest1_Click() Dim MyXL As Object Set MyXL = CreateObject("Excel.Application") Set MyXLSheet = GetObject("c: est.xls") MyXL.Application.Visible = True MyXLSheet.Parent.Windows(1).Visible = True MyXL.Application.Cells(1, 1).Value = Now() MyXLSheet.Close SaveChanges:=True MyXL.Application.Quit Set MyXL = Nothing
I spent all day trying to figure out why sometimes I can call an excel function from access and the value will appear on my form, but for some functions (F.inv, binom.inv), it errors out as "object not supported". I browsed all the available libraries in tools/reference but found nothing that seemed to solve this issue.
I'm wondering how other members here make decisions whether they want to place codes behind form or use a standard module instead.
I understand there is a performance penalty when you add another module (and use it), but am not sure whether one big fat module would be faster than several smaller modules with identical coding.
Furthermore, I know that some members use a hidden form to deal with startup and shutdown processing. Sometimes the processing has nothing to do with forms and would make more sense in a standard module, but since the form is already loaded, does it makes more sense to use the module behind the form than calling a function in a separate standard module to execute the needed code?
I'm having to recode some old MS Access DBs so they will run in the following environments:
Office 2000 on WinXP Office 2003 on WinXP Office 2010 on WinXP Office 2000 on Win7 Office 2003 on Win7 Office 2010 on Win7
When I wrote my code for Office 2000 on WinXP things were simple because directory paths were the same across all computers and I could hard code pathing when using a shell command to launch other files.
My new approach is to make a function call to the Windows registry to determine the default executable and path for opening a file based upon its extension (see apicFindExecutable in basWindows API module).
I'm able to use code to create a shell call and debug print it to the immediate window. If I put my cursor in the immediate window at the end of the shell call and hit [enter] the external file will open as desired. If I try to open the external file directly through code, I get a file not found error.
To recreate the error take the following steps:
(1) browse to files that are accessible from your computer (2) click the PREPARE DATA AND OPEN MAIL MERGE DOCUMENTS command button
Shell function call is made by the fnOpenFile function located in the basOpenFile module. There has to be a trick here that I'm missing.
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 creating an form in a database and whenever one of my procedure's run it creates this error message:
The expression ON Load you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.
*The expression may not result in the name of a macro, the name of a user-defined function, or [event Procedure]. *There may have been an error evaluating the function, event, or macro.
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.
