XMLHTTP.ResponseBody / XMLHTTP.Responsetext Paste into Excel Sheet

**gophins** · 01-07-2012, 10:20 PM

Hi everyone,

Thank you for your help. Once in a while I get into a new project and get stuck with something very confusing and I'm not sure in this case if it is that I don't know what I'm doing or if it is not possible at all. My goal: to have workbook that has a tab with historical stock data for each of the stocks I own. I downloaded a workbook with a macro that downloads the data but saves it into individual csv files. I'm hoping to keep it all in one workbook.

The ideal end product includes: 1. Sheet1 where I can list in column A all the stock ticker symbols. 2. A macro that crosschecks existing tabs vs the list, and creates or deletes tabs accordingly (if I delete a stock from the list, the macro deletes the tab, if I add a stock a new tab is created) 3. Download data using
XMLHTTP.ResponseBody that is saved to the respective tab.

My biggest issue right now is change the following code so that instead of creating the CSV files, I get the Responsebody copied into a sheet. Any help will be greatly appreciated. Also, if you know if Point 2 "A macro that crosschecks..." is doable please let me know, I found a code that creates the tabs, not sure that it can also delete if I remove a stock from the list.

Thank you!!!

Please Login or Register  to view this content.

**Zababcd** · 01-08-2012, 01:22 AM

I'm not sure how to copy ResponseBody to the worksheet (you could almost certainly write the separate file and then read it from there into the worksheet, though).

So my preferred solution is to just read the response as text and interpret it:

Please Login or Register  to view this content.

I'm not sure if this is exactly what you want... I assume for example, that the table has seven columns, because that's what I got when I tried a similar URL... but it should help.

**gophins** · 01-09-2012, 10:35 PM

Hi,

Thanks for the help. I made some changes, but now I'm getting a Type Mismatch error and I'm not sure what is causing it...
I'm not sure if it will be easy to find in on the code. I think it has to do with z, I'm not sure if I should set it as an Integer or something else.

Thanks for the help!!!

Please Login or Register  to view this content.

**Zababcd** · 01-09-2012, 11:02 PM

z should be a String. responsetext is what it says it is - it's a text version of the server's response to the xmlHTTP object. And text is held in String variables.

**gophins** · 01-09-2012, 11:15 PM

Thanks for the response. I changed z to a String but I'm still geting the Type mismatch Error 13. When I try to debug it doesn't highlight what's causing the error, not sure if that is common with this type of error.

**Zababcd** · 01-09-2012, 11:34 PM

I see that you've given currst as Long as well. currst should also be a String. I'm pretty sure that's the only problem left - I copied and pasted your variable declarations and they work now in my test version.

**gophins** · 01-10-2012, 02:50 PM

Thanks for the help Zababcd!! That fixed it. I have what I hope is a simple question, the code you provided me gave me the option to paste to here:

Please Login or Register  to view this content.

I have a code now that manages the tabs, so before I run this macro I will have tabs for each of the stocks I download from yahoo. Originally, I planned on pasting to a TempData sheet and copying and pasting from there to the respective stock tab. I wonder if I can paste directly to the tab without the intermediate step, say:

Please Login or Register  to view this content.

But that is not working, it only pastes the stArray in the tab for the stock in the last row within the Porfolio Tab (ignoring all the other ones- eg. I have stock symbols in rows A1 through A3, it only pastes A3). I also tried my original way but running in to a similar problem, where (I believe) it makes 3 downloads from yahoo but pastes them all on top of each other in the Tab for the last stock listed.

Please Login or Register  to view this content.

Either option, I have it between the last Next y and the Next x (see below). I'm guessing is the position of the command and when it comes into play vs the loop it is doing through all the "Xs". I hate to abuse your help, I promise this is the last question to you or anyone else who can help on this matter. Thank you so much!!!

Please Login or Register  to view this content.

**Zababcd** · 01-10-2012, 06:07 PM

Don't worry about 'abusing my (or others') help', that's what the site is for.

As for the problem in your code, should you be using x instead of PFROW in the last line (either

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

)?

As far as I can see, Cells(x, 1) contains the stock symbol for the data you've currently downloaded in the code, and Cells(PFROW, 1) is the cell containing the last stock symbol, so if you use this one then it will always write to the same worksheet.

Also, I see that you've failed to specify Worksheets("Portfolio") as the source of Cells in the second code sample in your post. It sounds like it's working anyway, but it's always good to be specific if you're working with more than one worksheet.

**gophins** · 01-11-2012, 11:38 AM

Thank you! It is working now. I went with:

Please Login or Register  to view this content.

I added the .value line to convert all the numbers stored as text. I'm sure there is a smart way to count how many rows to apply to, but 1500 is more than enough and it got the job done.