VBA- Pulling Data From Website
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.
View Complete Thread with Replies
Related Forum Messages:
Pulling Data From Website When Url Changes
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?
Extract Data From A Website
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.
Importing Data From A Website
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/$.
Copying Data From A Website ..
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?
my code is below ...
Copying Website Data To XL 2003
The code below should copy website data to XL 2003
However an error occurs at linemarked (**)
Object doesn't support this property or method (Error 438)
adn the online help is just UNHELPFUL.
Dim IntApp As Object
Set IntApp = CreateObject("InternetExplorer.Application")
' Change file name to suit
.Visible = True
.Navigate "Intranet website"
Set IntApp = Nothing
Importing Data From A Website Into Several Sheets
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.
Importing Data From A Website To Spread Sheet
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)
Pulling Data From A Tab
i want it to change the Header in E1 to the name of that price level and have the prices change according to that price level. The price level prices are currently being pulled from another tab through vlookups which lookups up the part numbers. My method only works with 1 price level right now and have no clue how to approach it with more then one price level. Also these part numbers will change positions and locations and are not permanent hence why i thought to use a vlookup.
Is there a way macro wise ( preferably through a formula ) that i can have Column E prices change according to the Price Level Entered.
Pulling Data From A List
I am trying to create a spreadsheet which will help us analysis the sale of each of the inventory items.
Col A: Item #
Col B: Description of Item
Col C: Standard COGS
Col D: Price
Col E: Profit %
On Sheet 1(Standard), I listed all 205 items with columns b-e also being filled in.
On Sheet #2(Actual Sold), I want to just enter the Item # and have all the other info on Col B - Col E fillied in automatically.
Pulling Data From Web Without Web Query
information from a website that does not allow web queries (or at least from Excel 2003).
I have to pull the latest data every Wednesday from this webpage: [url]
To make things even more complicated the name of the page changes every week as well (corresponding to the date).
Pulling Data Out Of A Table
I have a 4 columned table with 10000 rows
Entry # Correlation Index1 Index2
I want to be able to create tables out of this where I can pull all line items with correlation > 0.8 or <0.1 or <0.3 etc.
I think a macro will have to be written for this.
Pulling Data Off Web - Function
I have searched extensively for an answer to this but can't find anything.
I am pulling data off the web and it puts data down the column in the following manner:
I need to extract each set of Data to another worksheet. The problem is that it will be anywhere from 1 row each to 10 rows each. In each instance, there is a blank between the two sets of Data. I've looked at ISBLANK, LOOKUP, SEARCH, etc. but nothing I've tried has worked out.
Pulling Data From A Webpage
I am having trouble pulling data from a website. I need to get data from this website:
I need the highs and the low temperatures for the next week. (just the numbers preferably). But when I go to Data>> Import External Data >> New Web Query i'm not able to get any of the numbers into Excel. This way worked fine with other websites.
Pulling Up Data From Multiple Workbooks
I have a folder which contains a number of Workbooks which were all built using the same template, therefore the layout and formatting is identical in each one. Cells A1:B15 of these workbooks contain information which I am attempting to extract.
I have been asked to build a worksheet with two cells for user's input. The first cell is the 'Directory location cell' and the second cell is the 'Policy Number Cell.' The policy number is found in Cell A2 of every workbook.
The idea in mind is that the user inputs the directory location in the first cell, the policy number they are searching for in the second cell and then excecutes the macro. The Sheet would then return all of the valuable information from whichever workbook contained it.
I think I need to compose a macro which trawls through the folder specified in the 'Directory Cell,' opened every xls until it found the policy number which matched the value in the 'Policy Number Cell' and then returned the cells A1:B15 into the new Sheet.
Pulling Detailed Data From One Sheet To Another
I don't think this may even be possible, but what I am trying to do is pull only certain information from one worksheet to another based on whether there are entries on certain dates for employees.
A sample is attached.
On the first worksheet I have a drop down menu for all employees on the second sheet. (the drop down menu pulls the names from the second sheet)
The second worksheet has all employees in Column A and to the right has all their variances by date. Most dates will be blank as they didn't have a variance.
What I would like is on the first worksheet, is to select their name from the drop down menu and have all the dates that they had variances and the variances show up.
If you take a look at the attached excel file it might explain what I am trying to do better.
Pulling Data From Other Files - #NUM! Error
I have a main spreadsheet (Excel file) that pulls data from 3 other Excel files. Every day, the main spreadsheet has #NUM! errors in the cells referencing the other 3 external files. The errors go away when I open and then close the other 3 files, and the data then shows up fine on the main spreadsheet. What is causing this? Is there a way for the main file to automatically update the links without the errors? I set the option to enable external content and automatically update the links, but I keep getting the #NUM! error, until I actually open and close the 3 other referenced files, then it will work fine for the rest of the day.
Pulling Worksheet Data From Many Tabs
I am trying to do is populate one worksheet (in the same workbook) with data from many (ie 500) separate tabs. An example is as follows:
On the summary data worksheet, we will call Price Highs, I am trying to return one column of data from each of the 500 worksheets (aka their Price Highs). This is so that I can have each tabs (symbols) data next to one another for easy formula dragging and analyzing. Up to this point I have been using the Find/Replace function, but with this many worksheets I would obviously have to do it 500 times!
Pulling Data Only If File Exists
I am using the following VB code to check if a file exists and if so add a formula in my workbook providing data from that file.
I have pre-pulled raw data in the directory "H:BusinessRptTest for scripts" which is named firstly by the day of the month and then what data it is.
- i.e/ 13_CAI_AgentStats.xls
The idea of my code is to check firstly whether the file exists (i.e has the data been pulled yet) and then if so to extract the data I need from the correct sheet and if not to add a zero instead.
I thought all was ok, but if the raw workbook is not open I get a #REF error.
I have looked at some posts for getting info from a closed workbook on this site but I can't get my head around incorporating them into my code!
Code listed below
For date_test = 1 To 31
If Dir("H:BusinessRptTest for scripts" & date_test & "_CAI_AgentStats.xls") "" Then
Range("AE" & date_test + 11).Formula = "='H:BusinessRptTest for scripts[" & date_test & "_CAI_AgentStats.xls]" & date_test & "_CAI_AgentStats'!$D$4"
Pulling Data From A Text Report
First I copy and paste the text into Excel. All of the data is in column A because of varying lengths, which seems to work fine. I've already written VBA code (shown at the end of this posting) that reads the contents of each cell in column A, pulls out the key information (like the actual error code or invoice #), stores the fields in variables, switches to a separate tab that has a more user-friendly layout, locates the next empty cell in the appropriate column for the field, and puts the variable contents into the cell. I realize that the code currently has some variables that aren't being used and other basic issues, but that's not really what I'm after at this point.
The report itself includes billing errors, with each billing error separated by a series of asterisks. My current code works just fine for sections of the report like the first section in the sample below, where the billing error only contains a single invoice, error code, description, and action item. If the entire report was formatted in that manner, my task would be finished because it does those sections perfectly well.
But...if you notice in the sample report below, the third billing error contains more than one invoice, error code, description, and action item. Obviously, using my current code, when the macro encounters that section, it continues to function, but the rows do not align properly in the separate worksheet because there are extra invoices, error codes, descriptions, and action items. I just cannot figure out a way to have the macro keep the information aligned. In other words, in the third section of the sample report below, the billing error contains more than one invoice and item.
One approach that I've thought of is to try to count the number of rows between the cell containing the previous set of asterisks and the cell containing the next set of asterisks, because it's the information between each set of asterisks that I need to work with. But I haven't found a way to do that and don't even know if that's the direction I need to go or not. I'm pretty much lost at this point.
Here's a sample of the text report as it appears in column A (the fields are actually longer than this - I tried to simplify as much as I could for this posting): ....
Formula For Pulling Data From A Matrix
Im currently working to speed up my monthly reporting process. Each month I pull data from the ledger by account & office. Below is an example of what the format of the ledger looks like.
My next step is to report the amounts by account number for each office. At the moment I do this manually, which takes a very long time. My goal is to be able to paste the ledger data on a worksheet and have my report populate automatically.
In regards to the account numbers from the ledger, we summarize the accounts using the first 3 digits of the account. For example, account number "1113454" would be considered a "111" account. Account number 3335454 would be considered a "333" account.
************************************************************************>Microsoft Excel - 3Q Global Variance Analysis.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB16C16D16E16F16B22E22B23E23B24E24B25E25B26E26=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
Pulling Data From A Charging File
I would like to be able to copy a single row of data from a huge sheet containing about 7000 rows on a daily basis. This would be a non issue except that it is not in the same row from day to day. It may be in row 1624 today and 1620 tomorrow basically. In the second column is a unique ID number that says constant though. I need a macro that can find this number and copy the entire row of data to another sheet.
The second issue I am having is that this data is from a .csv file that is posted on the next business day. The name of this file changes day to day because the name of the file is the date it was from.
Pulling Data From External Files
I have 12 external files (one per month) and need a quick way to pull these into a reporting book. I've used the following code...
Workbooks.Open FileName:= _
Call OpenClipboard(0&): Call EmptyClipboard: Call CloseClipboard
However with 10000++ rows of data in each file, this is very very slow (approx 5 mins to update all 12 months).
Pulling Data From Different Sheets And Different Files
I am trying to write a macro that will select all cells on a sheet from a2 until the last cell with data. (The last column will be column k but not all columns are completly filled. And I need all the data from the page copied.) Then paste that info onto the first sheet. Then go to another sheet do the same thing except paste it in the first open cell on the first sheet.
Then take all the data from the first sheet of different files into one seperate file.
VLOOKUP - Pulling Wrong Data
I have a VLOOKUP, which uses a named range called Department. The first column is sorted in asending order. The department numbers are 4 digits, formatted numeric. The department numbers are in order up to Department #19000, then they jump to 50000 series. Here's the problem, the 20000 department seris is in the master database, and the lookup is returning the data relating to Department #19000, instead of NA.
SUMIF - Pulling Data Across From Different Spreadsheets
To comply with the rules, I have already posted this request in the Excel forums http://www.excelforum.com/forumdisplay.php?f=8. I have 6 spreadsheets that the team that I work for edits on a regular basis. I also have one summary spreadsheet that our director reads and uses for her reports. Most of the forumulae pulling information through are simple = ones, but there is one section which is a bit more complicated. The team have to list the number of client related activities they have done by month - eg:
A------- B--------C--------D---------- E-------F
Month--- Client--ITT-----Quotation-- Demo---Visit
I am using a SUMIF to total the number of ITTs etc done per month: =SUMIF('S:InternalSales Figures2006-2007[ES 06-07 Spreadsheet.xls]Activity'!$A$3:$A$1001,1,'S:InternalSales Figures2006-2007[ES 06-07 Spreadsheet.xls]Activity'!$C$3:$C$1127)
This works fine if both the Summary Spreadsheet and ES 06-07 Spreadsheet are open at the same time, however, if ES 06-07 Spreadsheet is closed, then all I get in the Summary is #VALUE!. At the moment, all I can think of to rectify this is to do the SUMIF in each separate spreadsheet and then copy the information over to the summary one, which is duplicating information!
Pulling Data Foward To The Front Sheet
I am working on a project for a monthly report. My back sheets have (or will have) 12 columns of info for monthly income/expense for multiple companies. The front sheet will have 2 columns, one for the past month & one for cumulative.
Obviously the cumulative is an easy one to pull off, but how can I make the correct column show up for the current (last) month I am working on.
Rather than make it tied to the actual date of input, I have used an approach in the past with a "sum if" formula for a single reference where =sum if(b6:b6,a2,a6:a6) but I'm not sure how to make it work for 12 references. Can this be done?
Pulling Data From Multiple Sheets Into 1 Sheet
I am creating a spreadsheet for mutiple clients, which has around 5 columns. This spreadsheet will have 7 sheets on it. 1 sheet for each client, and the 7th will be to display all of the information together.
Basically I am looking to find out if this is possible?
I will try and explain a bit more, as above is just beifly what I am loking for.
I Have Client.xls
On my Tabs I have
Client1, Client2, Client3, Client4, Client5, Client6, All Clients
In Each individual spreadsheet, I have the following Columns
Incident Reference; Description; PMDB number
Each Day data will be entered into each client sheet, and I would like this information to be populated into the All Clients Tab. I am not sure if this is possible, and if it is, would I then be able to put it into the All Clients tab, and have it auto sort by the incdient reference column? As this is an autogenerated reference for all of our clients.
Pulling Individual Rows Out Of Repetitive Data.
I have a workbook populated with values in 3 columns. The values of the second column group rows into larger segments. I want to pull one row for each different value onto a different sheet.
How do I only pull one row of apples onto another sheet without copying by hand? I have attached an live example. I want to pull one row for each classification (column 2) named on the sheet without copy/pasting each one.
Pulling Of Data From Sheet1 To Sheet2 Or Any Backup
I have some data in sheet1 (as per attached sheet), every month i have to enter this data more than 1000 rows..i want a formula so that automatically this data should go to sheet2 and get a seperate report shape (as mentioned in sheet2) and also add a row below of each report in case ColumnA (Shipp) data is increased for some shipp name and also report heading shoudl be change according to columnA (shipp) and renumbering it.
Pulling Data From Files By Part Of File Name
I have forms by differnet departments each day. The files are saved as the department's name then date Byrd 82708.xsl. I need a command button that will pull data from three cells in each of these forms. The master list will not be in the same folder as the deparment forms, the cells are E20, f20, f25. I have a text box were the user will input the date, by this date I would like all forms with this date in that folder to have their data pulled from those three cells and returned in master list.
Pulling Specific Data From Random String
I have random comments in a column of cells of which I'm searching for a specific string of characters that may be contained in each cell. I want to pull 2 pieces of data from this specific string and place the data in 2 other cells in the same row.
Sample comments in cell 'N1':
..... blah, Appt: Friday; 12/4; night drop off. blah, blah....
Sample comments in cell 'N2' (and so on and so on):
.....blah, blah, Appt: Thurs; 12/3; 12:30PM. blah, ....
The specific string in the above examples will always begin with:
Then the key elements found directly after the 'APPT:' are:
Thurs; 12/3; 12:30PM.
(which are the)
DAY; DATE; TIME.
These elements will be always separated by the semi-colon ';' and the string will always end with the period '.'
I need a pair of formulas to be in col 'J' & 'K' to extract the DATE and place it in column 'J' and the TIME and place in column 'K', both in each same rows.
So, from the 2 above samples I would need the following:
J1 - 12/4 ....... K1 - night drop off
J2 - 12/3 ....... K2 - 12:30PM
I've been trying to come up with a formula using a combination of FIND() & MID(),
LOOKUP Formula Not Pulling Correct Data
Attached you will find the spreadshhet in question. If you click on the click here button input the following info:
the sheet will auto populate all numbers. this part is working properly. MY issue is if you look at the Bad Debt number, the correct response should in cell f21 should be $636.46 because in order to hit the next level of pay, you must at least hit $3,424,082.00. I can not figure out how to get the correct formula that will put the right pay out. Basically in order to hit a certain level of payout you must hit that next number, even if you are a $1 higher you can not get the pay out. So for example if your bad debt is $3,424,083 you would be paid out at 100%, not 120%.
Pulling Data From List If Cell Blank Then Move To Next
Trying to pull data from a list and if the cell above is empty then pull the data from the cell below and so on........
I have some code for it..but its just a little bit too long and I can't use it
This is what i have so far, trying to simplify it as its way to long to fit in a singel cell....i did think about breaking it down in to collums of either 8 or 6 and having it in chunks that way...but if i did that then i would still have to eventually use this forumla below as i don't really want huge gaps within the form i'm doing.
Pulling Data From Multiple Worksheets Onto Master Sheet
I have an identical worksheet for all the days of the month - i.e. the May workbook has 30 identical worksheets. I am wanting to pull only certain rows from each worksheet onto a master. Something like - copy row from all worksheets where that rows cell "D" has any value other than " ".
I have used the following macro to pull all the data from every sheet onto a master - but i don't want all the data - just the qualifying data.
Dim J As Integer
On Error Resume Next
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "ALL"
I am not sure what the range "A1" is - i am thinking i can modify it to only grab certain rows.
Pulling Information In Seperate Sheet Based On Data In Current Sheet
I've got a bit of a quandary here that my novice programming skills can't seem to solve.
Here is the scenario. I work for a photographic library. We have a spreadsheet that contains 5 or so columns, and a couple of thousand rows. Essentially each row contains an imagine code (the number we use to file away the imagine, ie. US_NY_NYC_1 ) in the first cell, and the image information in the following cells (ie metadata, keywords, photographer, date, etc).
so a typical row would look as follows:
Filename | Keywords | Photographer | Metadata | Location |
all the keyword, photographer, metadata, and location information can only be tied to the one image code whose cell begins the row.
Now, here is my dilemma. This sheet (lets call it the master sheet) contains all the images we have in a given batch. However, when a client orders images, we will send them most images in that batch, but not all, and we may send a few additional. So I have a separate sheet that contains only those image codes that the client requested. What I need to do is essentially take a code from the second sheet, find that same code in the master sheet, and copy over all the cells associated with that code's row. If the number does not exist in the master sheet, simply leave that row with only the code in the first cell, and the rest blank, and move on.
so for example, in my second sheet lets say I have the following A_B_C . I would like for excel to find that same code in the master sheet, and then copy over the metadata, photographer, date, etc, associated with that code, and paste it into the second sheet, so that that row in the second sheet now looks identical to the row with the same image code in the master sheet . If A_B_C does not exist in the master spreadsheet, then I would like excel to simply leave that code in its own row, leave the rest of the row blank, and move on to the next code.
Launch A Website
I am wanting to be able to lauch www.fafsa.gov from within Excel. In other words I want to be able to put a button on screen and when the user clicks on the button it will record some statistical data and then lauch the website. I know how to do everything except lauch the website. Can you lauch a website using code from within Excel.
Bringing In .gif From Website
I am trying to bring in a web page into excel but when it brings it in it misses the little football .gif. My code is like this if I don't get this gif its another bunch of work to read through the data.
Dim cur_year As Integer
Dim game_url As String
Dim cur_row As Integer
Dim site_url As String
Dim paste_row As Long
Dim Heading As String
Dim q1_row As Long
Dim find_last As Long
Dim space_count As Integer
Dim prev_year As Integer
'Main loop each loop one season
Can A Macro Contain A Link To A Website
I have two different worksheet that if data is changed then the user needs to go to the company's website to update information there as well.
Can a macro look at a range of cells and if a change is made then a message box would pop up and if the user said yes then they would be directed to the company's website.
VBA To Publish A Worksheet To Our Website
We are a construction company.
We travel and do work local.
Some employees that have access to the internet at home.
Some have access when they stay at hotels.
What I would like to do is give them a work schedule on our website.
I would really like to do this using VBA.
Is it possible to save the worksheet and send it to our website?
I would love to attach a Macro to a button so the person, after updating the schedule, just punches that button and the updated sheet goes on our website.
Access Banking Website
if there is a library that supports interacting with a
website via VB? I'm trying to write a program that automatically logs
onto my bank account and downloads my monthly statement.
My searches for "VB web access" seem to only bring up a lot of stuff
about VB Access databases.
VBA - Save A File From A Website
problem with the following code
Dim URL As String
URL = Worksheets("References & Resources").Range("URLMSL")
Dim IE As Object
Set IE = CreateObject("internetexplorer.application")
IE.Visible = True
My problem is that on most of the workstations here, it will open internet explorer, then open the file in Excel. I just want it to Save the file.
Logging In A Website With Macros
I'm trying to automate some webscraping on a website that requires a login, and was wondering how I would do so using Macros with a specific username password somewhere in the spreadsheet, lets say B2, and C2 respectively. The website I'm trying to login is this; http://underground.chacha.com/account/. I think I have most of the scraping figured out; its just the log-in for now.
View Website Tags?
I am building an application through Excel to update specific internal website information. My question is, is there an easier way to identify and view the tags on a web page without having to right-click and "view source"?