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?
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 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 created a help file in access that lets you have text mesasge up to 1024 chars. Just press F1 key on any control that you have setup to bring up help message.
Does any one see any problems with the way I did this?
Form Help File: = "" Help Context ID: = 0
For each control on the form where you would like to have a help message do the following. Form Control Help Context ID: = Set to one of the help index numbers in the help table.
Add KeyDown Event to the control
Private Sub Text0_KeyDown(KeyCode As Integer, Shift As Integer) 'Add this line KeyCode = DisplayHelp(KeyCode, Me.ActiveControl.HelpContextId) End Sub
module
Function DisplayHelp(KeyCode As Integer, HelpContextId As Integer) As Integer Dim dbs As DAO.Database Dim rstRecords As DAO.Recordset
If KeyCode = VBKeyF1 Then ' 112 = F1 Set dbs = CurrentDb Set rstRecords = dbs.OpenRecordset("SELECT tlbHelp.Index, tlbHelp.HelpMessage " & _ "FROM tlbHelp WHERE (((tlbHelp.Index)=" & HelpContextId & "));")
With rstRecords If .RecordCount > 0 Then Call MsgBox(!HelpMessage, vbInformation, "Help") End If End With DisplayHelp = 0 ' Clear KeyCode rstRecords.Close Set dbs = Nothing Else DisplayHelp = KeyCode End If End Function
Create Table and Fields
Table Name: tlbHelp Field Name: Index as Number Field Name: HelpMessage as Memo Field Name: FieldName as Text Field Name: FormName as Text
How type in custom number and message for each help message.
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.
My title may not accurately express my question. If you can suggest a better way to do this, please feel free to elaborate.
Within Access, I have an "Invoice Number" table, with a single field containing a number. I have a function that accesses the table, increments the number and returns it to the invoice number field.
I need a way to perform this same function to the same table from within WORD.
I am using a Access database with a frontend/backend design. I want to keep a table in the BE updated with the current list of users inside of the frontend. To do this I thought that I would just have the front end update a time field saying that that user is still inside of the FE. My question is two-fold - firstly, how can I run a function every minute or so to update this table.
I am a naive in access and really looking for some mentor to help me learn quickly
I am presently going through a book Call "Microsoft Access 2003 InsideOut" I dont have the cd so thus missing the sample databses discussedin the book.
If anyone can provide me with this data base then it would be a great help
I'm trying to extract information from Err.Description within a form's Error event. Alas, Err.Description does not seem to be available from there.I have a form that normally displays in DataSheet view. If a trigger on the Oracle back-end raises an error, I want my Access app to be able to parse out Err.Description and deliver a more user-friendly message than ODBC's message. I want to get the info about the message from the error coming back from Oracle, not by matching up error codes.
If I change the form to Single-Form view and put a Save button on it, I can capture Err.Description in the command button's Clicked() event, and then parse out what I need.So if I must display this form in Datasheet view, where can I trap the ODBC error and display my user-friendly message?
Form_A (main form for the application - should always be open) Form_B (always open, but sometimes has visibility set to false) Form_C (opens from button on Form_B)
When I press the button on Form_B, the only code behind it is DoCmd.OpenForm "Form_C". This seems to hide Form_B, and open Form_C behind Form_A (which is the main form of the application) inside of an Access window.
I would like Form_C to open in front of Form_B. I suspect that I set up the form incorrectly or something when I created it, and it is therefore opening inside an Access window.
I'm having a problem with the syntax of a recordset of a Datasheet inside a subform which is also inside a Main Form.
Main Form - frm_1_0_LMS Subform - frm_1_4_0_TeamApprovals Subform(Datasheet) - frm_1_4_1_TeamApprovalsList
Here is my code:
Code:
Dim rs As DAO.Recordset Set rs = Forms!frm_1_0_LMS.frm_1_4_0_TeamApprovals.frm_1_4_1_TeamApprovalsList.Form.Recordset If Not (rs.EOF And rs.BOF) Then Forms!frm_1_4_2_ApproveDeclineUserLeave.Controls("lblFiledDateLeave").Caption = rs!Leave_Date End If
I am getting this error: Object doesn't support this property or method
I am writing a macro to automate the filling up some data from excel to company website. I have changed the website name in this post for data protection purpose. The excel will login for different clients by using the combination of username and password for respective clients and then some data are required to be inserted in a text box on a web page, I think the text box is on a form and form is within an iframe, within the web page. Once the data is inserted into text box, one button (Submit), which is also on the same form, is to be clicked.
On the click of a button, the updated data appears on another section, I could not make out if it is an form or frame, which is under the abovementioned form. Once we are happy with the way data appears on the web page, we have to click another button (Update), which is on the same section, to finally updating the data on website.
I wrote the following code to login to the website and then to navigate to the web page where I have to fill up the performance numbers in a text box. The first problem is how to access the text box inside the form from VBA so that the macro can write a number in that text box and how to access the button to submit the data. The HTML code, which can be seen on click of F12, is attached below. The second problem is how to access the Update button inside the other section, so that the data will be finally uploaded.
Code:
Sub LoginToCorpAccount() Dim ie As Object Set ie = CreateObject("InternetExplorer.Application")
[code]...
<div id="ssc-consumers-holder"/> -
This the line which gets highlighted when I click on the section where the data appears after clicking the submit button, and from here Update button is to be clicked.
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.
