Simultaneous XMLHTTP Posts W/Event Handler
Jul 12, 2006
I have a standard module in which I gather information from a workbook, create an XML document, Post it, and collect the value I need from the response XML. Thing is I have to run through it maybe hundreds or thousands of times depending on the number of records I have. It takes ten years to finish the loop. I have read that it might be possible to post them concurrently using a class module and an event handler, but I have not worked with Class modules before. Compiling all the XML documents into memory is easy, posting them and getting my return values in a timely manner is the problem at hand.
View 2 Replies
ADVERTISEMENT
Dec 23, 2009
I wonder if there is a way to clear event handler code in a userform programmatically?
Haven't quite managed it yet.
Something like:
dim x as integer
With ThisForm.CodeModule
x = .CountOfLines
For 1 to x
.line = ""
Next x
End With
View 9 Replies
View Related
Aug 22, 2006
Is there an Event Handler that runs a macro whenever any control object in a worksheet has its state changed or whenever any checkbox is checked/unchecked. I want to have a method handle this outside of the checkbox since the number of checkboxes are dynamic and should not have to be individually coded by the user.
View 5 Replies
View Related
Oct 26, 2007
I want to create an event handler for multiple buttons. I know that it can be done in VB but I'm not sure about VBA. Auto Merged Post;bump* Auto Merged Post;bump*
View 4 Replies
View Related
Feb 5, 2007
I have a workbook, that when opened the first time needs to prompt the user to save it. I got that working with no issues by using
Private Sub Workbook_Open()
SaveOnOpen
End Sub
where SaveOnOpen is a procedure in another module. What I would like to do now is re-assign the Workbook_Open sub to be set to null, so that it doesn't run any more. Is it possible to somehow assign Workbook_Open to call a null procedure, as opposed to setting up an onTime call to delete the code itself?
View 9 Replies
View Related
Jul 21, 2006
I'm trying to attach Worksheet_Change to a worksheet created at runtime. Usually you put it in the code window of the Sheet object but what do you do when you create the worksheet at runtime?
View 9 Replies
View Related
Mar 17, 2007
to save typing the same things over and over I have created a dialog box with checkboxes, named with several common terms we use when writing an invoice. i.e. dig a hole, paint a fence etc. I have assigned the dialog box to a button on the worksheet.
When I check the checkboxes, I want the text to go to a blank section of the invoice one underneath the other. The reading I have done suggests this is an event-handler subroutine, I just don't know enough about VBA yet to be able to write the code.
View 9 Replies
View Related
Feb 26, 2009
how and where to navigate on the website to mark all my posts solved. Sometimes I don't see the edit button, I don't know how to list all my posts, etc.
View 5 Replies
View Related
Mar 15, 2009
Is it possible to download it into my computer?
View 9 Replies
View Related
Feb 21, 2008
I am trying to automate data downloads into Excel from a webpage, similar to webquery but slightly more complex. In the browser I can only get the data I want if I select a checkbox. As soon as the checkbox is clicked, the webpage reloads with the right data, and this is what I am trying to import into Excel via XMLHTTP.
The html/javascript code related to the checkbox on the website is:
<form method="post" name="tsOptions">
...
<input type="checkbox" name="allRecs" onclick= 'form.submit();'>
...
</form>
The ... contain some html stuff and further controls like radiobuttons. Anyway, I know how to load the website into Excel VBA as is before the checkbox is selected. Now how do I implement this PUT/submit.form() thing? the VBA code so far is:
Set oHttp = CreateObject("MSXML2.XMLHTTP")
oHttp.Open "GET", "http://...?option1=...&option2=...", False
oHttp.send
a = oHttp.responseText 'a contains source code of website
View 3 Replies
View Related
Aug 5, 2009
i am getting the error run-time error '-2146697211 *800c0005)'': the system cannot locate the resource specified
when running the following:
my guess is because sometimes the internet connection doesnt always work, or the page loads too slow, but 'on error resume next' doesnt work because the line takes awhile to execute and then excel turns off
i believe i saw somewhere here (mrexcel) how to give specific commands a limited time to run. i dont rememeber how to do this though, or any other workaround ...
View 9 Replies
View Related
Dec 18, 2009
I have set up a workbook containing 15 sheets. 12 of them are named Jan to Dec. I KNOW how to protect each one, but is it possible to protect all twelve in one go?
View 9 Replies
View Related
Jan 18, 2007
It is my understanding that two or more people can simultaneously work in and save data in a given workbook.
Is this true?
If so, how does one set up the collaborative mode? Currently we are getting "the first person in has control of the file, all others are 'read only' until the file is released".
View 3 Replies
View Related
Jan 16, 2013
I'm putting together a workbook which tracks operating hours for 17 pieces of equipment (units) at 7 job sites.
I need to track and record the number of hours where at least two of units 1-6 operate simultaneously each month.
So far I have only succeeded in creating another table which pulls all records for units 1-6 from the Log. The table can then be sorted by date (for some reason, this sorting only kind of works) so simultaneous operating hours can more easily identified and summed manually.
I'll attach a workbook with mock data which shows what I have so far. The complete workbook also does some other calculations which I have removed for clarity. That is why there is some "extra" info in the workbook.
The workbook is set-up where:
LOG: This is where the user records when each unit was operated.
LOG2: This is where I've attempted to isolate records for units 1-6 and sum simultaneous operating hours.
Factors+Limits: This is where the fields for the lists are stored as well as other factors used throughout the workbook.
Monthly: I'd like to be able to records simultaneous operating hours here.
View 4 Replies
View Related
Oct 9, 2007
I am trying to solve a set of simultaneous equations by using SolverOk function in VBA.
I dont want to use the 'SetCell' option so, I wrote the following
SolverOk MaxMinVal:=1, ByChange:= Range("V34:V36")
SolverAdd CellRef:=Range("AK4:AK6"), Relation:=2, FormulaText:="0"
SolverSolve
but I get an internal memory error. However, if I use the solver dialog box directly from excel and not through a macro and keep the 'Set Target Cell' as empty, I get the right solution.
View 8 Replies
View Related
Aug 23, 2007
I have just signed up to the forums as I am having a very specific problem. I have looked on google and used the search feature (on several forums!) and found very limited information that is useful to me. I am having trouble with a very simple few lines of code in a macro. The code looks like this:
Function startmarketIDs()
Dim targeturl, writerow, readrow, textmass, xmlHTTP
targeturl = "http://lite.betfair.com/ Events.do?s=00010913z"
Set xmlHTTP = CreateObject("Microsoft.xmlHTTP")
xmlHTTP.Open "GET", targeturl, False
xmlHTTP.send
MsgBox xmlHTTP.StatusText
textmass = xmlHTTP.responsetext
MsgBox textmass
End Function
My problem occurs at the line "textmass = xmlHTTP.responsetext". The responsetext command is obviously causing problems because if I remove it, the code executes without error and the "xmlHTTP.statustext" says "OK". With the responsetext command left in, the code generates the following error: Run-time error '-1072896658 (c00ce56e)': System Error: -1072896658. If I change the targeturl to www.betfair.com, the code executes fine and I get a message box (as desired) with the source code of the website displayed. Without meaning to sound like Im answering my own question (!!), perhaps there is something about the url or the way in which I am using the xmlHTTP object that is causing the issue, I wondered if someone would mind inspecting my code and perhaps pointing me in the right direction?
View 2 Replies
View Related
May 9, 2014
I have rows of data in excel that update automatically and i want to check in column B how many times there are simultaneous occurrences of a cell value and then if those occurences have been there for a time specified time duration using column C which has a time and date stamp. The time and date stamp is when the price is saved down.
A Simplified Example:
Item
Action
Date & Time
Red
Buy
09/05/2014 09:00
[Code] .....
What I would like the formula to is to count the occurrences of cell B that have been avilable for longer than say 30 minutes.
Formula that worked out the occurrences of cell B with this formula
=SUM(IF(FREQUENCY(IF($B$2:$B$7="Buy",ROW(B2:B7)-ROW(B2)+1),IF($B$2:$B$7<>"Buy",ROW(B2:B7)-ROW(B2)+1)),1))
So if i wanted to count the simultaneous occurrences of "Buy" then the formula would result 1 and the same for sell if i changed the formula would be 2.
Now i want to try and get a way of using the time those occurrences were available for by using Column C
The simultaneous "Buy" was available from 09/05/2014 09:00 to 09/05/2014 11:01 which is longer than 30 minutes so i want to count that as a occurrence.
Then both the "Sell" were only available for 14 minutes the first time and then only for 20 minutes the second time so none of those would count.
View 1 Replies
View Related
Apr 5, 2013
I have a userform that reads data from a sheet with thousands of records (lines) each record with some 30 cells (columns). I wanted the user to scroll up & down in the sheet, using the keyboard arrows and I wanted the userform to show the relevant record, where the cursor stands. For this purpose I created an event handler (Worksheet_SelectionChange) that identifies the scrolling, populates the form with the relevant record data and then returns the focus back to the sheet. The command I have used to send focus back to the sheet (so the user can keep scrolling) is:
VB:
AppActivate "Microsoft Excel" & " - " & ThisWorkbook.Name
Now comes the bizarre part... This macro can work perfectly for hours and then decide to "die", or can work on one computer but not the other (all with Excel 2007, BTW). When the macro dies (or if it does not work, to begin with), the debugger points to the command I mentioned, with an error message "Run-time error '5' , Invalid procedure all or argument."
how to make my macro "stable" (make it work always and on every computer)?
Unfortunately, I cannot upload the file, because it contains confidential data (and it is not in English...).
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
lr = Cells(Rows.Count, 1).End(xlUp).Row
If Target.Column < 2 And Target.Row > 1 And Target.Row <= lr Then
crow = ActiveCell.Row
[Code]....
View 5 Replies
View Related
Sep 21, 2007
I have a piece of code with some error handlers in it but for some reason the error handler can't deal with a second error. The example code below suffers from the same problem in that it falls over when it tries to process the i = "w" line for the second time. If you F8 through the code the error handler works the first time but not the second. Why is this and what can I do to get it to keep going to erH when it hits the i = "w" line?
Sub test()
Dim i As Integer
testing:
On Error GoTo erH
i = "w"
erH:
i = 1
GoTo testing
End Sub
View 9 Replies
View Related
Mar 21, 2012
how to improve the following error handling strategy and code samples so as to maximise its usefulness for the purposes described.
During function calculations in VBA, such as is done by numerical integrators, numerical run-time errors may occur, such as:
Err.Number = 5 Invalid procedure call eg. LOG(-5)
Err.Number = 6 Overflow
Err.Number = 11 Division by zero
My present strategy to address these errors:
Say the variable x is used in the function calculations, and some particular value of x causes one of the above errors to occur. After the above errors are trapped, the x value is increased slightly from its initial value, and the failed calculation line is retried (Resume). On the next error trap, the x value is decreased slightly from its initial value, and the calculation tried again. This way, the initial x value is cyclically changed by adding increasing magnitude increments of alternating signs, searching for the nearest x value which will allow the function calculation to be done without error. Thus it causes the x value to swing around the initial x value in a pendulum-like manner, but with a widening swing per pass.
So far, I have the following code:
Code:
ErrorHandler:
If Err.Number = 5 Or Err.Number = 6 Or Err.Number = 11 Then
If Initial = True Then
[Code]....
View 5 Replies
View Related
Oct 11, 2009
From my userform I use a calendar to input in two text boxes the "from" and the "to" dates and get a "custom" statement of activity by company.
If I input two dates that do not exist within my summary of sales how can I code in order to get an error message AND clear the text boxes. (textbox1&2)
Example: jan 07 to Dec07 do not exist/ Oct 09 does
"from" and the "to" dates = 01/01/2007 to 12/10/2010 would work (and it does)
01/07/2007 to 12/06/2007 does not exist anywhere then I want to see a message box to that effect AND the textboxes cleared for input of the new "from" date in textbox1
If Impossible at least can you help on an error handler? (on error resume does not work or at least I think it does not)
View 9 Replies
View Related
Oct 23, 2008
Before doing some modifications in a data sheet, I first check with the below code whether a certain list is complete or not using the VLOOKUP function. If I encounter a missing value in the list, a userform appears asking to fill out 3 data fields, which are then added to the original list to complete it. All of this works fine if only one line is missing; if there are two (or more) lines missing, all goes well untill the VLOOKUP function errors out on the second line missing.
I can't find out why everythign goes welll with the first missing line, but not with the second missing line.
Sub proCheckVesselCodes()
Sheets("Sheet4").Select
Range("K1").Value = "Check"
Set varRange1 = Range("A1")
fctCountNrRows
varRow1 = varRow
varRow2 = 2
jump:
On Error Goto addvessel...............
View 3 Replies
View Related
Mar 15, 2014
Is it possible to create an Error Handler for the entire workbook, or do you have to put one on every sub routine?
View 1 Replies
View Related
Jan 19, 2009
I can not seem to find a good example of code for a try and catch error handler out on the web for something I am working on. Here is the code I am working with:
View 2 Replies
View Related
Dec 11, 2007
I am writing a macro to track stats in a workbook. The workbook has the check every tab in the given workbook, hidden and unhidden. In order to track the stats I find a column labeled "Read Dates" on each worksheet. The "Read Dates" column is not always in the same location so I have the macro Find the words "Read Dates". To make sure the cell the macro finds is the column label and not just another cell where someone may have used the words "Read Dates", I have an 'If/Then' statement checking the column to the left to ensure "Rev Mo" is there. If the state is true, the macro begins tracking stats. If the statement is false it attempts to find the next cell containing "Read Dates".
My problem occurs when the macro selects a sheet that contains no data or does not have the words "Read Dates". To help remedy this, I have an Errhandler that simply tells the macro to move onto the next worksheet. My problem is that there are several worksheets that do not have a cell with the value of "Read Date" so the second time the ErrHandler errors. Below is the section of the code I currently have an issue with.
Sub Begin_tracking()
'Error Handler
On Error Goto ErrHandler
WCount = Worksheets.Count
For i = 1 To WCount
If Worksheets(WCount - i + 1).Visible Then
Worksheets(WCount - i + 1).Select
Can anyone think of a better way of doing this so the entire workbook is worked or tell me how I can reset the Error Handler is it will move onto the next worksheet? I've looked into help on this forum to rest the ErrHandler but what I've found and tried has not worked. The ErrHandler may be invoked 2 times or it may be invoked 20 times depending on the workbook.
View 8 Replies
View Related
Feb 1, 2007
For some reason I thought this would be simple, but not as simple as I thought. I have a UserForm that enters data into a sheet, but before it is entered, the user is prompted for the sheet password (sheet is protected). What I tried to do is use an error handler to exit the sub if the password is incorrect, user hits Cancel, etc. Here is the code I am using:
View 10 Replies
View Related
Mar 27, 2009
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
View 9 Replies
View Related
Aug 7, 2006
I have the following code, which works perfectly:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TotalDays As Integer
TotalDays = Range("C65536").End(xlUp).Row + 1
The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.
However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.
How do I add the following, to my previous code?
Range("E8").Value = Cells(7, 6) * 2.5
View 9 Replies
View Related
Aug 5, 2014
Looking for the syntax that will allow me to code an event sub routine, based off the event of a specific function e.g. findnum being run.
View 14 Replies
View Related
Oct 3, 2008
I have written a long bit of code, which works fine. I decided to add an error handler just in case of any errors. I have run through my code numerous times to confirm that there is no error, but the msgbox still pops up.
View 3 Replies
View Related
