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.
Any ideas if/how I can do this?
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:
http://tote.phonebet.com/WPSARL-C.PHP
http://tote.phonebet.com/WPSBEL-C.PHP
http://tote.phonebet.com/WPSCAL-C.PHP
http://tote.phonebet.com/WPSCAN-C.PHP
http://tote.phonebet.com/WPSCTN-C.PHP
http://tote.phonebet.com/WPSCTN-C.PHP
http://tote.phonebet.com/WPSCOL-C.PHP
http://tote.phonebet.com/WPSDPK-C.PHP
http://tote.phonebet.com/WPSELL-C.PHP
http://tote.phonebet.com/WPSEMD-C.PHP
http://tote.phonebet.com/WPSFMD-C.PHP
http://tote.phonebet.com/WPSFLK-C.PHP
http://tote.phonebet.com/WPSFTE-C.PHP
http://tote.phonebet.com/WPSHAS-C.PHP
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?
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
I've been programming my VBA script to import data from an XML file which looks like this:
HTML Code:
<response responseStatus="success"> <result xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:type="apiPagination"> <currentPage>1</currentPage> <list xsi:type="apiReport"> <Variable1>Name1</Variable1> <Variable2>41</Variable2> </list> <list xsi:type="apiReport"> <Variable1>Name2</Variable1> <Variable2>20</Variable2> </list>
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
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.
View 3 Replies View RelatedI'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 would like to embed a chart that updates from the web into my spreadsheet. This is it here:
[url]
I have a simple macro importing a .csv data file to a worksheet every 30 minutes.
When the macro runs an Import Text File dialog box displays.
What do I need to do to have the macro run to completion without the dialog box interruption?
Sub RefreshHourlyData()
htime = Now + TimeValue("00:30:00")
Application .OnTime htime, "RefreshHourlyData"
Sheets("H1Updates").Select
Sheets("H1Updates").UsedRange.Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A1:A1").Select
End Sub
I suddenly can't adjust my page breaks in any Excel spreadsheet while in Page Break Preview. Is there some option to turn it on and off?
View 7 Replies View RelatedI 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.
View 4 Replies View RelatedI 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.
View 2 Replies View RelatedI 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.
Is it possible to Change Start Page Of Multi Page UserForm Based on 8 CommandButtons. on a WorkSheet, for example.
CommandButton1
UserForm1.MultiPage1.Value = 0
CommandButton2
UserForm1.MultiPage1.Value = 1
CommandButton3
UserForm1.MultiPage1.Value = 2
CommandButton4
UserForm1.MultiPage1.Value = 3
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.
View 2 Replies View Relatedhow 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?
View 3 Replies View RelatedI 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.
The jist of the problem is that employees are paid their normal wage up untill 37.5 hours of work. From 37.5 to 40 hours of work they are paid 1.5 times their wage. Any hours over 40 are paid at 2 times the regular wage.
So i need a formula that can work out their total pay. Im not sure what i could put in a dummy document to help you since it would just be one cell for their hourly wage, one for their hours worked and then one for the calculation. so you can reference any cell as an example and assume £6 as hourly rate
Can embedded macros be found within the new Solver and how would you make the changes you need in VBA with different constraint analysis?
View 3 Replies View RelatedMy "Drawing" is embedded on Sheet1, later on in Sheet12 I click a button that'll send an automatic email to a user. I want to attach the embedded PDF onto this email if it is at all possible??
Code:
Dim Drawing As Object
After I've declared my variables I've tried setting the variable "Drawing" to the object, this is where I'm encountering my errors mainly "Type Mismatch" or Object Variable not set...
Code:
Sheets("Menu").Visible = True
Drawing = Sheet1.Shapes.range(Array("Drawing"))
Selection.Verb Verb:=xlPrimary
Sheets("Menu").Visible = False
At the bottom of the code, this is where i pull in the object
Code:
.To = Sheet12.range("L8")
.CC = ""
.BCC = ""
.Subject = "Arrange P&D Request"
.htmlbody = strbody & vbNewLine & Signature
.Attachments.Add ("Drawing")
I need to create a formula that will calculate a total if someone checks a certain box. (Each check box is associated with a cost. When people check the correct costs, the bottom should total the costs checked.)
View 9 Replies View Relatedi want to make and if statement in a cell that does the following:
if "J8 > 10" then fail
if "5
I have a worksheet with Labels (from the "Control" Toolbox) embedded and I want to read and/or set the BackColor value in VBA. I can't figure out how to accomplish this when they're embedded on a sheet like this.
View 2 Replies View RelatedI have a worksheets (named Graphs) that contains 12 pie charts, 8 column charts and 4 line charts. I would like to create some VBA code to loop through all these 24 embedded charts and modify only the pie charts. I'm OK with the code to do the modification (which is to set their plot area size and also to delete the title), however, I have no idea how to do the initial loop to select only the pie charts. I'm sure there's a much better way then hardcoding in each of their chart names.
View 2 Replies View RelatedI'm using Excel 2003 and have a spreadsheet that I want to distribute to a handful of people around the globe.
I want some cells in the page to link to various PDF files which I need to distribute with the worksheet.
I can embed the PDF files no problem, but I need each link to the embedded objects to take up no more vertical space than one row of text (without an Icon) - ie I need to be able to access the object through a "hyperlink style" item.
I can't figure out how to do this, as the hyperlink function does not seem to permit linking to embedded objects.
I received an Excel spreadsheet that has contact info (name, address, phone, email, etc) but the email isn't visable unless I hover over their first name (a link) and I can see it in there. If I click the first name link, it opens a new Outlook email message. (Cool, but not what I need...)
When I copied the whole spreadsheet and pasted it as a tab into an existing Excel document of my own, the email addresses are no longer there, it now shows the path to where my original document is stored. Where did the email part go? how do I extract the emails out? Ideally I'd like to put them in a new column next to their name...sorted in my original document or in the tab...either one.
I am trying to create a macro to add to a button that will open an embedded document (word, PPT, and excel).
I recorded the macro and it works fine the first time I try to test it but after it tells me that there is an error...
I'm not exactly sure what to do. Unfortunately, I could not find what I needed on youtube
Error: It talks about the object being out of range and allows me to debug... the code that is in the developer is:
Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Sheet2").Select
Windows("Worksheet in Book1").Visible = True
Selection.Verb Verb:=xlPrimary
ActiveWindow.Close
Sheets("Sheet1").Select
End Sub
The Windows line is what it tells me to fix.. from there I am lost... Is it because I am trying to do a double clicking function?
I am trying to wrap up a project. The project consists of taking the input values from a userform and entering them into various Word documents and Excel workbooks. The procedure is run from Word. I have no issue completing this part.
However, there are a few Word documents that have an Excel worksheet embedded into them. How can I manipulate these worksheets using VBA? I have attached an example for your reference.
I have found out how to get into edit mode, but I still do not know how to actually edit the worksheet.
