I looked at the source code of the website and realized that if you notice (above) that the variables listed in the link (i.e year, month, day) are exactly what i need to change in order to get the data for a specific date. how can I accomplish this using VBA. so say I have in on an excel sheet year in column A, month in column B, and days in column C (time interval is constant so we don't have to worry about stime and etime). and i run the macro and it loops through each row taking year,month,day for all rows and saving the data as .csv or xls files?
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
I have a tedious task of copy/paste from our website. have a look at below sample data. I have around 1500 rows of data like this.
Sheet1 A B
[Code]....
Column A has input data which i need to copy and paste in a web form. Column B will have results scrapped form webpage[ URL]. This webpage will need login details
If a code can be written which take value form column A of spreadsheet and paste in webpage like shown in above image and hit search button. Next webpage will be loaded with number of results like shown in below. Take the number of results and paste in column B of spreadsheet.
retrieving data from financial website databases like yahoofinance.com and bloomberg.com. I'm trying to make an automatic stock analysis model to read from the website database and retrieve the data into excel sheets. For example, when opening the excel model the user gets a popup to enter the stock ticker, the user enters the ticker and gets a set of data. Is this do-able in excel?
I have written a macro to pull in data from a website each day. However the macro falls over because the end of the url changes slightly each day as the website provider updates the information. Unfortunately there does not appear to be a logical pattern to the number change in the url and so I don’t think I can pre-empt what the change will be. The path through the website remains the same, but the url changes as a new csv document replaces the old one. Is there any way of writing the macro so that it opens/picks up the current url rather than a fixed url?
I went through the thread http://www.mrexcel.com/forum/showthread.php?t=302438. I have similar problem of pulling the data from http://www.nseindia.com/ site. Could you please help me?
The navigation to the page required is as below.
http://www.nseindia.com/ > Equity > Historical Data > Security-wise data >
eg. NSE Symbol: RPL, Series: ALL, From date: 01-01-2008, To date: 01-05-2008.
After clicking on the 'Get Results' button it navigates to the page with tables. At the end it says "Download file in csv format". upon clicking this a csv file opens. (http://www.nseindia.com/content/equi...-2008RPLXN.csv)
I need this page to be opened in Excel sheet. How should I go ahead? (Basically I need historical stock data csv files of various stocks)
Even though a simple web query opens the page, it do not work if the parameters (stock name, date) are changed.
I found that the last resulting page in csv is using a "REFERRAL URL". Could this be a problem? How to get around?
I know little bit of VBA and can further work to pass variables.
Is it possible to extract data from a website into an excell workbook using data from the workbook?
I have rough workbook that i gather data from 3 different websites to calculate a rough market value for DVDs that I buy and sell. I am good with excel but have no idea about importing data into it.
Currently I copy and paste from the sites on an as needed basis but it is a slow slow task when I have several dozen or more to do per hour.
I wish to surf to a website and retrieve the bottom entry in a column The column updates dynamically with a new number added to the bottom on a daily basis.
Here is the website URL: http://global-view.com/fxhist.TXT.
The column I am interested in is the second column from the left under the History section. The column contains close prices for the currency symbol EUR/$.
I have managed to manipulate a website by entering some dates into the input boxes then go to another page with the information I need. I am wondering if anyone knows a macro I can use to copy the data directly to a new worksheet?
I've only recently ventured into the VBA scripting and am finding it quite exciting. After doing some trivial stuff, I'm trying to work with websites. Currently, I'm trying to pull data off of a website which poses a form which has to be filled first. I've been successful in filling the form and clicking submit, through the code, but the site opens the results in a separate tab in IE. I am unable to make it go to that tab and copy-paste data from there.
Secondly, instead of just blindly copy pasting, it would be better if the data is pasted in a more readable format (as on the website). The following code is plainly copying and pasting the data as-is and hence rendering it unreadable:
I am an excel novice trying to create a list of local churches. There are several online lists that I am taking the data from but, being a novice, I am entering the data manually. How to set up a method to bring the data into excel and order it where I want it.
I am attaching the spreadsheet.
These are the websites I am taking my data from: [URL]
From inside Excel, I wish to surf to a website and retrieve the bottom entry in a column The column updates dynamically with a new number added to the bottom on a daily basis.
Here is the website [URL].......
The column I am interested in is the second column from the left under the History section. The column contains close prices for the currency symbol EUR/$.
I need to get data from a website that requires to log-in, so i set this Vba, and i get an error "object dosent support this property or method"?
Sub GetTable() Dim IEApp As Object Dim IEDoc As Object Dim IETable As Object Dim clip As DataObject Set IEApp = New InternetExplorer IEApp.Visible = True
Doing all 47 pages at once may cause their site to block your IP. Though usually only temporarily - a few minutes to a few days. You could do it in sessions by changing "For i = 1 To 47" to the appropriate page numbers. After each session be sure to rename the "Stats" sheet as the code starts by deleting and re-creating it. After all sessions combine the various "Stat" sheets.
Doing all 47 at once will take a few minutes to complete and you won't see anything happening. The cursor will only spin occasionally. You wil get "Query complete" when it finishes.
Code:
Sub QueryWeb() Dim i As Integer Dim firstRow As Integer Dim lastRow As Integer Dim nextRow As Integer Dim URLstart As String Dim URLend As String
[Code]....
This code works like charm. Is is possible to develop a code like this which open each each name from this page- [URL]....
AND
copy details like Attorney Name, email, phone and fax nos. and paste the same into excel rows.
The code below should copy website data to XL 2003 However an error occurs at linemarked (**)
Error is:
Object doesn't support this property or method (Error 438)
adn the online help is just UNHELPFUL.
Sub CopyInternetDoc() Dim IntApp As Object Set IntApp = CreateObject("InternetExplorer.Application") With IntApp ' Change file name to suit .Visible = True .Navigate "Intranet website" .ActiveDocument.Select ** .Selection.Copy End With ActiveSheet.Cells.Select Selection.ClearContents ActiveSheet.range("A1").Select ActiveSheet.Paste IntApp.Quit Set IntApp = Nothing
I have a bunch of lists I would like to copy from a website. I'm trying to copy them into excel using an external data query but the information I'm trying to grab doesn't export. I tried selecting various tables within the webpage.
I am importing some data from a website into several sheets but when I refresh it I am not able to access the data as numbers. I am using a sumif to collect the data. See the attached sheet. if there is a way to do this without sumif, that would work as well, but my numbers are not copying over to the main sheet because they are not being imported as numbers. also when the a and c are changed on the main sheet i want the values to change with them.
Working in Windows 8 excel. I am copying data from a website and entering that data onto a spread sheet. There are certain websites where the data does not transfer on the first try. It sometimes takes up to trying 3 times before the data will download onto the spread sheet. Why is that? And is there anything I can do to get it to transfer on the first try?
I have a problem with text to columns with data downloaded from our website. The address field is coming through with the whole address in a single cell with multiple rows. I have tried the following but all I get is the first row in the first colimn and the rest of the data:
Text to Columns, Delimited, Other and ALT-0010 Text to Columns, Delimited, Other and ALT-010 Text to Columns, Delimited, Other and ctrl-j
I have even created a macro to replace the hard returns with commas and then use comma as a delimiter but that still does the same.
I have attached a file with two of the records (1 tab for the original data and one with the commas)
Finally, I have tried this in both 2003 & 2010 and get the same issue in both.
I'm trying to see if there is a way to speed up the process of doing multiple Google searches to see if I can find a website attached to a text. I have a list of websites, and am trying to update the list to remove dead/inactive sites, and also find the URLs for the live sites.
The list looks like this (sample): Comstock, Inc. ConNuestroPeru Contacto Latino contentSutra Core States Crains Chicago Crains Chicago Business Crains Detroit Crains Detroit Business Credential Crescent-News.com Crowell Weedon
Thus there's no website attached, and I need to manually find the URL myself. There are over a thousand cells in the list.
How to speed up the process of finding a url for these websites?
My Excel Spreadsheet has columns named "Hospital Name | Overview | Address | Phone | Fax | Contact Person 1 | Contact Person 1 Title | Contact Person 2 | Contact Person 2 Title | Contact Person 3 | Contact Person 3 Title"
There is the list of hospitals starting from "Abbott Northwestern Hospital". I have two things in my mind to extract the information. If you click on the first hospital name "Abbott Northwestern Hospital". Below is an example text which I would like to transfer it automatically in columns given above;
This text should go in column named "Overview":
Abbott Northwestern Hospital bring twins into the Twin Cities -- along with triplets, quadruplets............
This text should go in column named "Address": 800 E. 28th St.Minneapolis, MN 55407
This text should go in column named "Phone": 612-863-4000
This text should go in column named "Fax": 612-863-5667
This text should go in column named "Contact Person 1": Jeffrey D. (Jeff) Peterson
This text should go in column named "Contact Person 1 Title": President
This text should go in column named "Contact Person 2": Daryl Schroeder
This text should go in column named "Contact Person 2 Title": VP Operations
This text should go in column named "Contact Person 3": Sandy Schmitt
This text should go in column named "Contact Person 3 Title": VP Strategic Development, Allina Hospitals and Clinics
I recently created a spreadsheet to calculate the value of certain stocks. I chose to do the s&p 500. It can take a while to manually get the info I need, so I looked to videos on vba writing and different solutions. I do not want to use web queries. Rather, I'd like to use a simple formula that pulls the necessary "single" item from a webpage and puts in a cell, then I will simply copy and paste that cell for all stocks.
from my online endeavors and inquiries, I put together this POS:
Function vi_Price(ByVal Symbol As String) Dim IE As New InternetExplorer IE.navigate "[URL]... Do DoEvents
[Code]...
Using Google Chrome, I tried to find the "element" that I need. In this case, the last close price at the top of the webpage. It's hard to distinguish which it is. If I can just get the code to work for the price, I think I can modify it for the other 3 items I need.
I would like to be able to have Excel VBA pull down the data from the following website.
The first URL will take you to the website, but you then need to select a state shown in bold from the Radio side of the index window in the middle of the screen. Once at the next screen, this is the table I wish to download into excel.
I want to do this for all the states listed. How can I do this or is it possible since you have to select a state prior to the listing.
HTML Code: [URL] ..... I found this code from anothe thread but it did not work.
Code: Sub GetData() Dim x As Long, y As Long, t As Long Dim htm As Object
Can VBA be used to extract a table from a website and output it on an excel sheet?
Basically we have to log in to a website and type data to our spreadsheet which is a bit tedious from the table on the website.
I'm guessing this can be done in some way but us having to enter username/password on website first to get to page we want will probably cause an issue would it?
Im trying to import data from a plumbing supply website to an excel spread sheet.
On the supply house web site, for each part catgory there are corresponding pages containing a price list data table.
there are from 1 to 3 pages (price list data tables) for each part category.
i can import data from page 1 and page 3 fine. When i try to import data form page 2, it returns no data, or imports page 3 (instead of 2)
I spoke to the person who designed the website and he said that it was a problem within excel and not a problem with his program. (said it was a problem with excel not being able to recognize the page)
I have a set of exported data from a Project Management SW (activecollab). The result is an excel .xslx file with a table inside with a lot of cells I am not going to use. Additionally, what I would like to do is creating new tables on other sheets that use only the data I want from the export. For example, I have two projects and three employees. I want to create three tables with the names of the three employees. In each table I want the tasks done by them and the time they will spend on them in order to create a Gantt chart. I need a solution that allows me to create new tables with selected data from a bigger cluster (the export). you do not need to give me the exact solution, I only need to know whether it is possible or not and where could I get the info to do what I want.
In the past, elements were more or less fixed and was able to use my very well known method QueryTables under Workbooks.open
This website below has a "div", called "div.sidemeta" wihick contains the information I want to grab. [URL]
I have tried a different alternative:
Sub Test() Dim IE As Object Set IE = CreateObject("internetexplorer.application") IE.Navigate "http://www.webiste.com"
[Code]....
The data I get under "IEdoc.body.innerhtml" does not show that particular DIV. I have tried using getElementById,getNamedItem and getElementByName without any luck.
