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:
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
I need to get VBA code to import a web page one at a time based on a condition. When one of the urls below has "1 mins to post", I want that page imported into a worksheet. The pages require a login and here is the source code for login - "acctnum" and password - "zipcode". The name for "1 mins to post" is name ="MTP".
I would want to keep the page loaded until "MTP" = OFF. Then whenever the next URL goes to "1 mins to Post", load that web page and keep it loaded until "OFF" and so on.
This would need to continually scan through all of the following urls:
Working on automating a data update process for my department (well, for the Italian speaking analysts anyway). I'm trying to import data from an embedded PDF to my Excel 2007 worksheet - I only need the data on the PDF, but if the whole screen can be scrapped I can clean it up in my code.
There is a chart at the center of this page: http://charts3.barchart.com/chart.as...K&org=stk&fix= I can't import it into excel using DataImport External Data New Web query Is there a way to grab that data in VBA or in Excel somehow?
So far everything works great and it imports everything correctly to Excel 2010. This is the code I'm using:
Code: Sub test() Dim WS As Worksheet: Set WS = ActiveSheet Dim req As New XMLHTTP Dim Link As String Dim PageNumber As Integer Dim Url As String PageNumber = 1
[code].....
The problem is that I only import page1 (only link [URL])
How do I get all URLs so that it also imports the data located on other pagenumbers? For example [URL]
I have some software that was written for me that scrapes data. It has a file in it that mirrors the web pages that it is scraping.
I would like to actually import those web pages one at a time based on a condition in the browser. The code of the web page is html. When one of the 35 mirror web page's "MTP" = "POST", I would like for all the web page to be loaded into a sheet called WPS. I would want that to stay loaded until "MTP" = "OFF". There may be more that one of the mirrored web pages with "MTP" = "POST". In that case, only one can be loaded at a time and that is fine. Once the "MTP" = "OFF", I want it to go to the next mirrored web pages with "MTP" = "POST" and load that web page from the mirror file.
Here are 4 of the 35 web pages that are mirrored and their paths C:TotearkmantotemirrorWPSAQU.HTM C:TotearkmantotemirrorWPSBEY.HTM C:TotearkmantotemirrorWPSCTN.HTM C:TotearkmantotemirrorWPSDDN.HTM
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 ...
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.
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?
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.
However, I have a problem in that my import macro gives me 'Run-time error '1004:
Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!
The code below shows is for the import macro only:
Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer
I have some code that parses an html table. I want to put in error handling in case the format of the page changes or the internet page is unavailable. I am testing without an internet connection and the run time error is not being branched off to the error handler and is fatally ending the macro. This is true also if I raise an error manually.
Private Sub ParseInjuryPage() On Error Goto ErrorHandl Dim strPage As String Dim webIE As SHDocVw.InternetExplorer Dim myURL As String Dim tableBeg As Long Dim tableEnd As Long Dim RowBeg As Long Dim rowEnd As Long Dim cellBeg As Long Dim cellEnd As Long Dim strBeg As Long Dim strEnd As Long Dim myCell As Range Dim rowNum As Integer With Sheets("INJ") Set myCell = .Range("A2") .Range("A:F").Value = vbNullString rowNum = 2 Set webIE = New SHDocVw.InternetExplorer myURL = "http://www.sportsline.com/nfl/injuries" webIE.Navigate myURL Do Until webIE.ReadyState = READYSTATE_COMPLETE DoEvents Loop strPage = webIE.Document.body.innerhtml...................................
I have a sheet that changes the number of columns based on data from other sheets. This is the only sheet in the workbook that I will print out every day. I have it set right now to fit all columns on one page, however sometimes this will result in the bottom 1/3rd of the sheet not being used. If I use fit all rows on one page, then some of the columns will get cut off. Is there a way to scale in both directions? My guess is there is not, but I thought I would ask because it would work great for me if I could.
I have a report that I use and right now I have a command button that prints the report x2. What I would like to do is to have it print one in color and one in grayscale. I could do this I think with two different buttons, but I would like to utilize just one. I tried creating a macro but it did not pick up the fact that I changed the properties to black and white.
I am looking for a formula that will take data from one page and graph it on another page. The data I am trying to graph is arranged like so:
A B
1 q 2009.05.01 2 w 2009.05.02 3 w 2009.05.01 4 q 2009.05.01
I am looking for something that will look at column A and if the answer is "q" and then look at column B and if the answer is "2009.05.01" Excel will take that and count it in a specific cell on another page. In the case above, I want Excel to give me the answer of "2" in a designated cell.
Could I use 1 multipage UserForm Instead of 8 UserFoms. Is it better to use 1 multipage UserForm with 8 pages or 8 UserForms. I dont know if this make sense or not?
I have a workbook that has 9 different tabs in it. Once all the information has been completed throughout the workbook, is there a code that could transfer specific data on each page to a different colum on that same page? I need to do this for 9 pages at one time. The information is listed differently on each page. I need to transfer data from the current data column to the previous data column once the entire workbook has been filed out.
how to get rid of the page numbers that appear when using page break preview mode. The page numbers appear in large grey font in the middle of each page and sometimes makes it difficult to read cells. Can I use this view with all the same functionality without that one feature?
I need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
Working with 2000 Excel is bringing some new chalanges. I see things are done different. I am trying now to keep my header to repeat from page to page.
For example I have a header that may say something like: People, numbers, date, ect. And there are so many new rows they go on for pages long. How can I keep my header on the top of each page when it prints out on paper.
Assume that calculation is set to automatic and not manual, then is every formula on a page updated when any value on the page is changed, or only when the values in the cells pertaining to a particular formula change?
For instance, if cells a1-a10 have values and in a11 is a sum a1-a10 formula, and in b1-b10 are values and a sum b1-b10 formula in b11, if I change a value in range b1-b10 but do not change any values in a1-a10,then as well as the formula in b11 updating will the formula in a11 recalculate or does excel keep track of which cells have changed and thus is aware that the formula in a11 does not need to be recalculated?
What I want to do, is to populate the Lois, Beth, Kelly and Shelley pages with the rows from the Que page when they get assigned to one of them. I tried to program then off of a different example in the forum. It didn't work. Will include the copy.
In my header I am trying to show the current page number and the total number of pages in the workbook. So if I have 10 pages in my workbook, page 5 would read "5 of 10".
I have this in my header "&[Page] of &[Pages]". All worksheets with 1 page read "1 of 1" and worksheets with 2 pages read "1 of 2" on the first page and "2 of 2" on the second page.
The code which you provided works fine no problem for a page break. I need to run the macro for the page break by asking the input file for page break to be done.
For Example, If excel filename "A" contain the code which you have given need to ask to input the filename "B" and process need to be done in file "B".
I have added some code to your code which you provided but it gives error message "1004" "Method 'Range' of object '_Application' failed" at following line :
Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
I need to remove all existing page breaks in a document and add a page break every 72 rows. I've tried some similar codes from this forum with other functions that I don't need in it.
I have a booklet I want to print from Excel 2000. I'm not seeing how to have the HEADER on page one only. The header is coming up on all pages. Is there a way to tell Excel to print the Header on page one only?
I have the tedious task of building a visitor database, i have a web page with over 300 records on it, each record containing information such as company name, address, phone number email and this is only the first web page i have to visit, that's already over 1000 copy and pastes.
I'm wondering if there is a way to just capture all of the information on the web page and then populate my excel spreadsheet with information falling under the appropriate headings.
I tried the method of right clicking on the web page and said import to excel, but as you can imagine this just resulted in all of the data appearing in one column and i would still have to move all the information around so it is not of much help.
