I have a file that I update daily. It involves opening the file, going to a drop down menu and selecting 1 of the listed items and pressing a button to run the macro. Once the macro is complete, I then go back to the dropdown menu and select the next line item and press the same button to run the macro on this. I repeat this task 7times.
All the macro does is load a txt file to a third party system.
I am looking to see if it is possible to reduce the time it takes to do this task.
I am trying to insert a timer into a column cell range that will countdown in hours, mins, and sec, when I enter the time. example: when I enter the "time in hr,mins,sec" it will start to countdown to Zero automatically, when I hit the enter key.
I am new to excel macros and writing automation code....I work for a company that sells special permits and so far i have written a macro to bring up a pop up box when a permit has expired but i also want to be alerted by email, how can I do this, also is there anyway to stop the macro from running once it finds all of the expired permits? I usually press ctrl + break....
I have attached the file to this post : Book1.xlsm
I am trying to analyze a web page and would like to search through the contents of a web table. I need to extract text error messages which can be located at various unpredictable rows / columns in the web table.
I have an Excel report that I'm wanting to know if it can be automated. Basically it starts off in Access, I have a few queries that run via an Access Macro, once the queries are done there is one query via the macro that exports a file of data to excel. I then have a 2nd excel file that is a pivot table that I refresh from the exported Excel file of data from Access. Once refershed, save it, upload it to a Sharepoint Site and send out an email via Outlook. Basically I'm wanting to know if this can be automated to were it runs at like 6am or something before I even get on my computer. I'm not a vba expert, I can do some basic stuff but what takes most people an hour or so to do would take me a day or so. Also would this be easier to just have the pivot update directly to the Access DB?
In cell A1 we have the word "one", and in B1 we have the word "two". Would it be possible to have a timer function run for 5 seconds, counting from 5 to 0, and everytime the timer reaches 0, the word in A1 is copied and pasted into cell C1, the timer resets and counts again to zero, this time when it gets to zero, it copies and pastes the word in cell B1 to C1. So it's like a flip flop function, with cell C1 changing it's value every 5 seconds. Also, this code needs to start running automatically when the sheet is opened. Is this at all possible with VBA?..
I have been trying for some time to be able to download a file from what I believe to be a FTP site. There are three user input fields which I have been able to identify there names. After these fields are complete there is a button to push which I have also identified it's name.
Once the process of pushing this button is done, an unspecified amount of time must occur for data to be gathered and the option of Open, Save, or Canel appear in a Download window. I would then need to Save and Overwrite an existing file.
No Login or Password is required.
I have been able to automate the open of I.E. and input to the three select fields as well as the necessary button push.
However, I am unable to identify how to automatically save the file into a specified location, with overwrite privileges enabled. Below is my progress
Any excel formulae to build in into my financial model for tax free period/tax holiday, eg for first 6 years, the project company no ned pay tax? it is best if the formulae can bring flexibility for user to change the no. of years (tax holiday).
In it I've developed some lap timers for tracking runners. However to be truly useful, each time I press a LAP button on any one row it really needs to record time with millisecond accuracy, which it currently does not.
why a macro that works without a problem in Excel 2010 now crashes Excel 2013. The error produced says 'Automation Error Exception Occurred'. If I place a stop in the code and hold the F8 key down, the code runs perfectly (Excel 2013) but if I run it, it will crash. The code is copying a worksheet into a new workbook, adding 2 more worksheets, saving then emailing the workbook before closing. It goes through the routine about 7 times before crashing (sending only 7 email out). It's something to do with the email because I can bypass that code and it runs that part without a problem.
Our office just upgraded all the computers to Office 2013 and I've got to get this macro running again. I had to email 100 reports one-by-one today and I didn't like it.
So, I have to export about 7 reports from SAP varying in size, lengths and widths. I slammed together the following code to clean the excel sheets.
I'd like the code to be able to open all the excel workbooks (saved as .csv from SAP), run the code, save the workbooks as XLSX with a specific name. Then run my Access Script to import them in to Access.
So here is the code that cleans the excel. The only problem with it is that sometimes SAP prints out a header so I have to delete may be about the first 15 rows until the actual column headings. It varies. I manually do that before running the script. Then save it manually, then open the next workbook and do it again, etc.
Cleaning Code:
Code: Sub Sample() ‘combine columns Application.DisplayAlerts = False
I have a VBA macro for automatically create a word report using data in Access and Excel. The macro is hosted within a Excel document but are using both Word-, Excel and Access objects/dataTtemplates for the creation.
The errors appears what it seems randomly. The macro iterates the same code a few times even in Office 365 but then it crashes. The most common error messages are:
Error 1:
Run-time error '-2147417851 (80010105)':
Automation error : The server threw an exception.
Error 2:
Run-time error '462':
The remote server machine does not exist or is unavailable
Error 3:
This action cannot be completed because the application () is busy. Choose Switch To to activate and correct the problem.
(no options are working, this hang the application)
If I debug usually the error originates from the code where I ether close the workbook template I'm using:
excelWorkbook.Close False
... or when I paste a chart from Excel to the Word report:
wordDocument.ActiveWindow.Selection.PasteSpecial
Even more strange are that sometimes if I continue to run the macro its all working again. This ONLY happens when the client office installation is O365. Office 2013 works just fine. I thought O365 and Office 2013 was fairly equal?
The only change I made was to change the "Sheet1" to "Journal" to match the worksheet name in my workbook.
As you close and reopen the workbook, the timer should start with =NOW() in A1 (formatted as "HH:MM:SS") and count up with the current time until you close the workbook.
I use Excel 2010, could that be the problem, that I copied a VBA code for an older version of Excel???
Question: What exactly is a regular module, do I use Module 1 for the first portion of the code or place it in ThisWorkbook?
I keep getting an automation error in excel 2010 when I click on the button that executes the macro. Excel crashes when the box appears. There is no code in the error box. Why I get this error message.
I have a macro that runs through about 40,000 records which can be quite time consuming. I currently have a status bar that shows the percent of work complete, but I'd like to add something that shows an estimate of how much time is remaining and how much time has passed. Below is the first loop in my code that uses the progress bar. You'll see that I modified the progress bar to include "Label 2" which shows the current record of the total record count, I'd like to add "Label 3" to show something like "2:30 elapsed, 1:15 remaining".
I have the following code below for a timer in a userform. Right now it is configured to start when a button is clicked. I would like to have it automatically start the timer after the workbook is open for more than 5 minutes but so far have been unable to get it to do so. I have tried putting the code in the workbook module but it still won't run. It also has a button to reset the timer if they need more time in the workbook and also a button for them to save and close the workbook if they are finished. I'm running Excel 2007.
VB: Private Sub CBReset_Click() Dim T, E, M As Double, S As Double T = Timer Do E = CDbl(Time) * 24 * 60 * 60 - T 'elapsed time in secs M = AllowedTime - 1 - Int(E / 60) S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)
I'm trying to do some automation of a website that has one of those annoying javascript popup dialog boxes you have to click OK on before you can progress. How can I kill that so that I don't have to manually click on "Ok" so that my macro can keep running?
You take an input, click a button. This will them create a small HTML table with a text input box and another button.
Sub VisitWebsite() Dim ie As Object Dim tcode As String Dim sURL As String Set ie = CreateObject("INTERNETEXPLORER.APPLICATION") sURL = http://sample.jsp ie.navigate sURL ie.Visible = True tcode = "apples"
While ie.busy DoEvents Wend Application.Wait Now() + TimeValue("00:00:03")
For Each ieForm In ie.document.forms ieForm(1).Value = tcode ieForm.submit Set ieForm = Nothing Exit For Next The code ieForm(1).Value = tcode works. The code ieForm.submit also hits submit on the button.
The problem is I cannnot figure out how to access the 2nd button which is generated when you click the 1st button. I have tried every variation of ieForm(1).Value. There appears to be only 3 forms (0, 1, 2). When I try 3 or greater, I get an error.
When I use ieForm(2).Value = "something", the Retrieve button text actually changes name to "something".
I've attached a screen shot of the webpage. I can insert text into the top Track Code box, and click Retrieve.
I can't change the text in the Routing Position field, and I cannot click the "Set" button. Any help would be greatly appreciated. I am still a beginner with VBA and it took me a while just to get it to work with the 1st input box.
The above is a screenshot of the data analysis (regression) I want to automate with vba code. Like all macros, I tried to record first and only got the following
I am trying to create a document that automatically sends an email whenever a field reaches a certain value. I am pulling the data from different worksheets onto a summary worksheet and using the data on the summary sheet as the point at which my code checks to see whether the value is below the minimum required. The summary sheet updates automatically whenever I change the data in one of the other sheets but the email will not send automatically when the summary sheet is updated. When I run the macro individually the email sends, however I need some way of triggering it so that it sends automatically upon the data in the field changing. Can anyone help me with this?
Here is an example of some of the code I am using:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D3"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value < 1 Then ANDES1 End If End If If Not Application.Intersect(Range("E3"), Target) Is Nothing Then If IsNumeric(Target.Value) And Target.Value < 1 Then ANDES2 End If End If End Sub
Creating a spreadsheet to inventory cups each day. When a macro was invoked do the following:
-unprotect the worksheet
-cut/paste the completed sheet below the current
-bring in the values form the previous day's inventory
-set protection on new spreadsheet to lock the cells
-blank out old values
-reprotect the worksheet
Pretty straightforward and seems to work fine for a few iterations, then crashes with "Run-time error '-2147417848 (80010108)': Automation error. The object invoked has disconnected from its clients.".
As FYI, this code was mostly generated using a record macro session, so no variables involved.
Here is a screenshot of the spreadsheet as it sits now:
spreadsheet.png
Here is the code: Sub NewDay() ' ' NewDay Macro
' Create new day and copy cup count from previous day[code]....
We are trying to automate the creation of PDF files for each sheet in an Excel file,a nd keep getting error messages. Can someone help? It produces the post script file, but it has no data nd then doesn't create a PDf file at all. We have Excel 2003, and have the below code setup.
should the line be set : ObjXL.displayAlerts = True - befor the objxl.Quit ? - before the Objxl.ActiveWorkbook.SaveAs FileName:= aname how to close the ObjXL with out orphaning a process.
OR??? ObjXL.ActiveWorkbook.Names.Add Name:="Data1", RefersToR1C1Local:=ObjXL.Range("A5").CurrentRegion Will this line of code create a 2nd instance of Excel?
I am trying to calculate the sumsq throught VBA for some automation.
The formula to be entered is - =SUMSQ(K1025:K1358)/(COUNT(K1025:K1358)-COUNTIF(B1025:B1358,"0"))
Another thing is that the row numbers are variables, and not fixed in automation. Let's call them R1 and R2. So I need the range to be R1 to R2 in all the instances in the above formula.
Step 1 : click on button in Internet explorer1(IE1) which inturn opens Internet explorer2(IE2). Step 2 : in IE2 i want to enter some value in textbox and click in "go" button.
Managed to get step 1 some how, need to proceed futher with step 2 given above
Private Sub IE_Autiomation() Dim i As Long Dim IE As Object Dim objElement As Object Dim objCollection As Object Set IE = CreateObject("InternetExplorer.Application")
I am trying to automate a website which I use for daily office related work. I am navigating the website, selecting some options and clicking on download button using vba code. When I click on download it opens a new internet page. Now I have to click one more hyperlink on that newly opened internet page. How to do that?
I created a UserForm (userform1) and added a label to it which essentially just says: "Pick a Colour"...
The other UF controls are added on the fly using the below code...however when I try to add the event to each button I get the Automation Error but I don't really understand how / why... the events are actually added to the VB project ok...
Private Sub UserForm_Initialize() Dim NewButton As MSForms.CommandButton Dim ws_i, line As Integer Dim clr_str As String For Each ws In ActiveWorkbook.Worksheets
I added a dummy routine for test purposes which is to be invoked on click of UF button control (I have tried placing this code in a standard module, within UF module etc to no avail):
Sub HIDE_WS(clr_id As Long) MsgBox clr_id End Sub
So to reiterate -- when I invoke the initialisation of the UF I get the following error:
Automation Error The object invoked has disonnected from its clients
All events are added for each control.
If I remove the adding of the events I know the UF populates with the correct controls etc...
pivot chart automation. I have a tab that contains thousands of rows and it grows larger on a weekly basis. On this tab is 169 peices of hardware I manage. Each week 1 row of data is added to all 169 peices of hardware. The data from the main tab contains metrics I need to chart. However, I am in need of a way of selecting a specific peice of hardware and having the pivot charts update automatically. There will be 26 Charts and going through each chart and selecting the proper hardware is going to take far too long.
Is there any way i can select just 1 peices of hardware from the data tab and have it populate all of these charts?
