Macro Slows System By Opening Many Windows
Jan 29, 2007
I've been using the following bit of code to run a macro at the specified time:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application .OnTime TimeValue("12:00:00"), "GetData"
End Sub
The GetData sub executes a shell script and then pulls data from the result. The problem is that the macro runs 140+ times, and so I get 140 windows popping up and the system practically stops. I can't figure out why this is happening, as there are no loops or any sort of repetition in the code. Any help is greatly appreciated as this problem occurs with more than just the one spreadsheet.
View 9 Replies
ADVERTISEMENT
Feb 22, 2007
I have to run a daily report from winxp or win2k and need to change the host files a few times for access.
I tried to use %systemroot% in the file path, but for some reason vba doesn't like it.
Here's the snippet I'm using for copying over the host file based on user choice: (the thing works if I change to the actual system root path (C: etc...)
Private Sub CommandButton1_Click()
Select Case ComboBox1
Case "- Choose -"
MsgBox ("This is not a choice")
Case "BTR"
View 9 Replies
View Related
Dec 1, 2009
I have 2 workbooks open and I want to set up formulas from one linking to the other. When I toggle on the task bar between the 2 workbooks they replace each other within the same window so I can only see one workbook at a time. How do I set it up so I can have 2 separate windows open at the same time, one with each workbook in it? I tried 'side by side' in one window but the viewing area is too small.
View 9 Replies
View Related
Sep 21, 2012
Any way to open a single instance of excel in two different windows. I use multiple monitors, and I like to have one worksheet maximized on one screen, and another worksheet maximized on the other, but when both worksheets are open in the same instance of excel this does not seem possible. Also, I don't want to use two separate instances, because then I can't paste special from one worksheet to the other.
View 4 Replies
View Related
Mar 16, 2012
Sub OpenCalendarForm()
frmCalendar.Show vbModeless
End Sub
When I run this I get this halt "System error&H80070057 (-2147024809). The parameter is incorrect. I havn't been doing VBA in a while as I am retired now but I have used this particular statement many times in the past and never had this error before. I have made sure there no typos. I open another workbook which has forms opening and copy pasted the the routine into my other workbook but I get the same error.
View 2 Replies
View Related
Aug 7, 2013
With excel 2013 you're now able to open multiple windows or views of the same workbook. However when a workbook is saved with multiple windows open, the next user to open the spreadsheet will also open it with multiple windows. Which can be very annoying when most people work off of one window vs. multiple. Is there a way to disable saving the multiple windows or a macro to force open excel in 1 window?
View 2 Replies
View Related
Mar 24, 2013
I have written this macro which I run about 1000 times in a loop. It runs solver and copies the results to a row in my spreadsheet so i end up with a sheet of solver results.
The problem is that every time it runs it eats up memory and slows down. I timed it and the first 100 runs take about 2 minutes and the last 100 takes about 15 minutes, the ram use increases by about 4gb in that time too.
As this is the first VBA code I have written and I have been learning as I go along, I assume its something I have done wrong in the code.
Sub run()
'
' run Macro
' run solver
[Code].....
View 2 Replies
View Related
Jun 5, 2009
I'm working on a spreadsheet which needs to refer to the current date. My PC checks the system date/time daily. Can I assume that if an end-user regularly connects to the internet, their PC will have a correct system date? Does anyone know if Win2000 or Win98 automatically update the system date/time when connected to the internet?
View 4 Replies
View Related
May 18, 2014
I've sent the file to 2 different windows computers and it works fine, but doesn't work with the two macs that I've sent it to.
This is the code:
[Code] .....
View 2 Replies
View Related
May 2, 2008
Is there a faster method than this code? it slows way down when the range is expanded b/c it loops through every cell.
Private Sub Worksheet_SelectionChange(ByVal target As Range)
If target.Cells.Count > 1 Then Exit Sub
Set Rng = Range("A1:GA400")
clrind = 42
If Not Intersect(target, Rng) Is Nothing Then
For Each cell In Rng
If cell.Interior.ColorIndex = clrind Then cell.Interior.Pattern = xlNone
Next cell
End If
End Sub
View 9 Replies
View Related
Feb 18, 2010
I have a macro running in Word (and referencing Excel) that is meant to iterate 15,000 times. But, after 100 times, the process slows and stalls.
I have put in a few "DoEvents" and "ActiveDocument.UndoClear" in Word, and I set "Application.EnableEvents = False" in Excel. These settings got me up to 100 iterations (previously the program would stop after even fewer )
Does anyone have any ideas or suggestions? I'm not sure what information you'd need to be informed about the problem, and I don't want to tell you too much! Do please ask if I'm not being clear.
View 9 Replies
View Related
Apr 29, 2014
Ive been looking at a bunch of examples, but everything that I have tried just gives me errors. what code to use to make a vbs script to run in windows task scheduler that will...
Open "J:GroupsBSHEETSSDANew EOD.xlsm"
Run the macro named "EODPrint"
Save the workbook
and close the workbook
Leaving excel open if other instances exist.
View 9 Replies
View Related
Feb 10, 2010
Is it possible to restart a Windows Service with execution from a VBA Macro?
When you go into Windows Computer Management you can see a list of services. You can click a hyperlink to start, pause or restart.
View 9 Replies
View Related
Mar 12, 2007
how can i set a macro to start on whenever the computer is started first on any day.
View 2 Replies
View Related
Mar 31, 2009
I have a SheetSelectionChange event that stores the Target.Row in a cell on the active worksheet.
View 15 Replies
View Related
Feb 26, 2005
I have an AutoFilter list of 14,000 rows by 14 columns, and the cells have some specific formating: fill color, font,
protection status, wrap, etc....
There are an additional 7 columns of formulas to the left of the filtered range.
The strange thing is----
-WITH the formating, trying to Unfilter the list takes 2 min, via a manually activated Data>Filter>ShowAll OR via a macro run of 'ActiveSheet.ShowAllData' .
(In an attempt to optimize speed, the VBA macro sets calculation to manual before the 'ActiveSheet.ShowAllData' and screen updating set to false.)
-WITHOUT the cell formating (eg. by doing Edit>Clear>Formats), the ShowAll takes about 3 sec.
Does anyone have experience or an explanation for this?
Why should the Formating affect Filtering so much?
Options for improving speed of autofilter?
I don't know if, or why it would be a factor, but note that I am using Dynamic Named Range and VBA to expand/contract the formulas
and formating to size of the list/table. Although this is not done during the filtering use.
Here is the dynamic formating code
Sub DynFmt_List()
Application. ScreenUpdating = False
With Application
.Calculation = xlManual
End With
I am wondering if some strange 'artifact' of manipulating the formating is becoming a factor?
View 4 Replies
View Related
Feb 24, 2013
I write a excel file (with macros included) to tackle the datas regarding stock market.
The excel file encompasses some information about financial reports, and some functions (macro) like sorting and filtering.
I'm wondering if I can build it as an windows 8 App and put it on the App market.
Or, in other words, can I make a windows 8 App by the functions in Excel ?
Can I do that ?
View 1 Replies
View Related
Dec 12, 2007
I need to develop a System using Macro in MS Excel 2007.. but i don't know how to do it..
I got 2 file in MS Excel
File number 1 : Demand - file name : ( got 55675 row of data)
File number 2 : Supply - file name : ( got 6774 row of data )
File number 1 : The data is starting from 0110111101 ( commodity code ) until 9999999999 ( commodity code )
File number 2 : The data is starting from 1532423007(commodity code) until 9119199901 (commodity code )
I need to categorize all this data in 10 sheet ( sort by the first 2 digit of the commodity code ):
Sheet 1 : 00 -09
Sheet 2 : 10- 19
Sheet 3 : 20 - 29
Sheet 4: 30- 39
Sheet 5: 40 - 49
Sheet 6 : 50 - 59
Sheet 7 : 60 - 69
Sheet 8 : 70- 79
Sheet 9 : 80- 89
Sheet 10 : 90 - 99
View 14 Replies
View Related
Jan 7, 2009
I have no experience with macros at all but i need to create a macro for work that will keep the original data entered but work out the difference between new data collected each day ...
View 10 Replies
View Related
Jun 6, 2002
What's the code for opening a Windows Explorer window from within an Excel Macro?
View 7 Replies
View Related
Mar 17, 2013
how to create and add password to a default windows zip file using vba excel macro.
View 2 Replies
View Related
Apr 19, 2013
Working with Windows 7 and Office 2010
I've created a Excel workbook with VBA code to synchronize an Access database between a laptop and a server. One of the fields in the database is a shortcut to a jpg for each record. (The Access stuff is not too important here)
Now I'd like to add the ability synchronize the photos between the laptop and the server; so people who use the server can view the photos added by the laptop and vice versa.
Something like:
Code:
For each [JPG] in [LaptopFolder]
If not [JPG] exists in [ServerFolder] then
filecopy [LaptopFolder][JPG], [ServerFolder][JPG]
end if
next [JPG]
That code wouldn't work.
View 4 Replies
View Related
Jun 29, 2006
I'm trying to run a macro on my machine (Windows 2000 and Office 2003) and I keep getting an error on one method. Now I tried to run the same macro on a different machine (Windows XP and Office 2003) and it didn't error out.
Public strControlTitle As String
Public strControlTime As String
Public strControlUnit As String
Public intControlItem As Integer
Public intLastNameOnly As Integer
Public intDataType As Integer
Sub Main()
Dim strControlItem, strPageName, strValue, sigma, z95, z99 As String
Dim r, c, count, cmax, rmax, rt, ct, low, high, a, b As Integer
Dim lesscol As Integer
With ControlChartForm
.ComboBox1.AddItem ("Average Turn Around Time All Patients")....................
View 4 Replies
View Related
Mar 16, 2013
I like the look of when only the used columns and rows are shown. I like to hide all unused columns and rows, and have the background and a minimalist spreadsheet.
HOWEVER, is it just me, or does Excel move a lot slower when thousands of rows and columns are hidden? Particularly, opening files seems to be slower. I'd love to delete them entirely from existence, so Excel only has a few rows and columns to work with, but that doesn't seem to be an option.
View 2 Replies
View Related
Jun 14, 2013
I export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.
Here are the steps I go through:
1. Clear the first 3 rows and the last 5 rows
2. Copy the totals in the last row of the data and paste them in the first row
3. Subtotal the different categories in row 2 and add a validity check
4. Add a new column A with a formula to add customer numbers on each line
=IF(ISERROR(FIND("00000",B5)),A4,B5
5. Copy the formulas and paste values over them
6. Filter the data to show blanks under the "Doc Date" column, delete all rows
7. Filter the data to show blanks under the "Type" column, delete all rows
8. Filter the data to show "Total:" under the "Apply No" column, delete all rows
9. Turn off filters, format all numbers in accounting format
10. Check the validation at the top to ensure no transactions were deleted
View 6 Replies
View Related
Sep 21, 2013
I thought about using it for forecasting purposes. I might try to use it together with live data (temperature, seasonal patterns)... but if I graph it, it starts from left to right...
How to modify the code a bit...
VB:
Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrDown
I tried to change the code, but it did not worked.
View 8 Replies
View Related
Oct 12, 2012
I have following macro recorded to extract data from a mainframe system
open "C:DesktopReportRBDRBD.txt" for OUTPUT as #1
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a24octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
The requirement here is run the above macro for the next 30 days automatically, for example
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a25octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
and so on till 23 Nov. From the start date the macro should run for the next 30 days. Currently i am changing the dates manually in the macro
View 7 Replies
View Related
Nov 21, 2012
I am thinking of commanding macro to select worksheet tab based on the system date to do some pasting.
My worksheet tab is as follow:
Nov-12, Dec-12, Jan-13, Feb-13 and .........
What should be the macro script required to select the worksheet tab based on today's date?
View 2 Replies
View Related
Oct 27, 2008
i have a spreadsheet with data that is exported to Excel via our in house investment system, the report looks something like below, though real data consists of 2000 rows of data. Where we have O/S in Bank this means these entries are all physical bank entries i.e statement credit and statement debit, and where we have O/S not in Bank these are all accounting entries, i.e. Ledger Debit and Ledger Debit.
What i am after is a macro that will insert a column next to Team and then input SDR SCR on all statement entries and LDR and LCR on all Ledger entries, the final report should look like the second spreadsheet....
View 9 Replies
View Related
Oct 24, 2007
I have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:
If ( ActiveWorkbook.Date1904 = True) Then
nh_stringToDate = nh_stringToDate - 1462
End If
But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.
View 2 Replies
View Related
