Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting External Data with Bullying/Boundary problems
Dear Esteemed Sirs and Sirettes,
I have this issue with a bully in the playground of my Excel workbook. I tried to ignore and confront the bully, but both resulted in an astounding failure comparable to or exceeding that of team USA in the last soccer world cup. The issue stems from using a VBA macro to get data from a webpage and insert it into my ESPP calculation spreadsheet. The setup is as follows: - Windows XP - Excel 2000 - Monitor, Eyes, etc.etc. The workbook has not been locked, nor the cell in question. The VBA macro I got and which used to work wonderfully well was from: http://msdn.microsoft.com/library/de...xlWebquery.asp <I think google groups will wrap that URL like a fajita The gory details (sparing you the bloody nose): Added module and code snippet as given above and shown below with my tweak: Sub URL_Static_Query() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1&e=.txt", _ Destination:=Range("d3")) .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub Mind you, I changed quote data source. I also wished it to go into the cell of D3. The surrounding cells calculate boring stuff such as ESPP tax implications and TODAY() to figure out holding time. The problem I have with the execution of this macro is that it doesn't find D3 as a welcome home and decides to insert a whole column to the left of the D column. This leaves all my other wonderful goodness unchanged, since 'E' is the new 'D' and the cell functions are updated accordingly. Call me old-fashioned, but I wish column 'D' to remain where it is, BUT with the updated information downloaded courtesy Yahoo! Inc. Anyone know how to tweak this situation to enable me to update D3 with the oh so desirable current data? I thank you in advance for your time and apologize that I can't offer too much assistance within this forum. I'd be happy to pre-offer assistance in exchange in the following areas: C, C++, MFC, Windows Programming (within my capability), and some C-based cell phone programming. Best, Me!~ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting External Data with Bullying/Boundary problems
Don,
I very much appreciate the help and reference to more useful tools! Best Regards, Me!~ Don Guillett wrote: Try adding a line. .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True You also are building a LONG list of external names with each query. You should incorporate a name delete into the macro to prevent this. I also suspect you want more than one symbol so you need a list loop. You may want to goto xtraders groupjoindownload some of mine and other FREE files to do this for you. To Post a message, send it to: To Unsubscribe, send a blank message to: To change address, first unsubscribe, then resubscribe to: -- Don Guillett SalesAid Software "TATrader" wrote in message oups.com... Dear Esteemed Sirs and Sirettes, I have this issue with a bully in the playground of my Excel workbook. I tried to ignore and confront the bully, but both resulted in an astounding failure comparable to or exceeding that of team USA in the last soccer world cup. The issue stems from using a VBA macro to get data from a webpage and insert it into my ESPP calculation spreadsheet. The setup is as follows: - Windows XP - Excel 2000 - Monitor, Eyes, etc.etc. The workbook has not been locked, nor the cell in question. The VBA macro I got and which used to work wonderfully well was from: http://msdn.microsoft.com/library/de...xlWebquery.asp <I think google groups will wrap that URL like a fajita The gory details (sparing you the bloody nose): Added module and code snippet as given above and shown below with my tweak: Sub URL_Static_Query() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1&e=.txt", _ Destination:=Range("d3")) .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub Mind you, I changed quote data source. I also wished it to go into the cell of D3. The surrounding cells calculate boring stuff such as ESPP tax implications and TODAY() to figure out holding time. The problem I have with the execution of this macro is that it doesn't find D3 as a welcome home and decides to insert a whole column to the left of the D column. This leaves all my other wonderful goodness unchanged, since 'E' is the new 'D' and the cell functions are updated accordingly. Call me old-fashioned, but I wish column 'D' to remain where it is, BUT with the updated information downloaded courtesy Yahoo! Inc. Anyone know how to tweak this situation to enable me to update D3 with the oh so desirable current data? I thank you in advance for your time and apologize that I can't offer too much assistance within this forum. I'd be happy to pre-offer assistance in exchange in the following areas: C, C++, MFC, Windows Programming (within my capability), and some C-based cell phone programming. Best, Me!~ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting External Data with Bullying/Boundary problems
Don,
Very much appreciate the help and reference to the useful tools! Best Regards, Me!~ Don Guillett wrote: Try adding a line. .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True You also are building a LONG list of external names with each query. You should incorporate a name delete into the macro to prevent this. I also suspect you want more than one symbol so you need a list loop. You may want to goto xtraders groupjoindownload some of mine and other FREE files to do this for you. To Post a message, send it to: To Unsubscribe, send a blank message to: To change address, first unsubscribe, then resubscribe to: -- Don Guillett SalesAid Software "TATrader" wrote in message oups.com... Dear Esteemed Sirs and Sirettes, I have this issue with a bully in the playground of my Excel workbook. I tried to ignore and confront the bully, but both resulted in an astounding failure comparable to or exceeding that of team USA in the last soccer world cup. The issue stems from using a VBA macro to get data from a webpage and insert it into my ESPP calculation spreadsheet. The setup is as follows: - Windows XP - Excel 2000 - Monitor, Eyes, etc.etc. The workbook has not been locked, nor the cell in question. The VBA macro I got and which used to work wonderfully well was from: http://msdn.microsoft.com/library/de...xlWebquery.asp <I think google groups will wrap that URL like a fajita The gory details (sparing you the bloody nose): Added module and code snippet as given above and shown below with my tweak: Sub URL_Static_Query() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1&e=.txt", _ Destination:=Range("d3")) .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub Mind you, I changed quote data source. I also wished it to go into the cell of D3. The surrounding cells calculate boring stuff such as ESPP tax implications and TODAY() to figure out holding time. The problem I have with the execution of this macro is that it doesn't find D3 as a welcome home and decides to insert a whole column to the left of the D column. This leaves all my other wonderful goodness unchanged, since 'E' is the new 'D' and the cell functions are updated accordingly. Call me old-fashioned, but I wish column 'D' to remain where it is, BUT with the updated information downloaded courtesy Yahoo! Inc. Anyone know how to tweak this situation to enable me to update D3 with the oh so desirable current data? I thank you in advance for your time and apologize that I can't offer too much assistance within this forum. I'd be happy to pre-offer assistance in exchange in the following areas: C, C++, MFC, Windows Programming (within my capability), and some C-based cell phone programming. Best, Me!~ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting External Data with Bullying/Boundary problems
glad to help. You will like the xltraders group
-- Don Guillett SalesAid Software "TATrader" wrote in message oups.com... Don, Very much appreciate the help and reference to the useful tools! Best Regards, Me!~ Don Guillett wrote: Try adding a line. .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True You also are building a LONG list of external names with each query. You should incorporate a name delete into the macro to prevent this. I also suspect you want more than one symbol so you need a list loop. You may want to goto xtraders groupjoindownload some of mine and other FREE files to do this for you. To Post a message, send it to: To Unsubscribe, send a blank message to: To change address, first unsubscribe, then resubscribe to: -- Don Guillett SalesAid Software "TATrader" wrote in message oups.com... Dear Esteemed Sirs and Sirettes, I have this issue with a bully in the playground of my Excel workbook. I tried to ignore and confront the bully, but both resulted in an astounding failure comparable to or exceeding that of team USA in the last soccer world cup. The issue stems from using a VBA macro to get data from a webpage and insert it into my ESPP calculation spreadsheet. The setup is as follows: - Windows XP - Excel 2000 - Monitor, Eyes, etc.etc. The workbook has not been locked, nor the cell in question. The VBA macro I got and which used to work wonderfully well was from: http://msdn.microsoft.com/library/de...xlWebquery.asp <I think google groups will wrap that URL like a fajita The gory details (sparing you the bloody nose): Added module and code snippet as given above and shown below with my tweak: Sub URL_Static_Query() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1&e=.txt", _ Destination:=Range("d3")) .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub Mind you, I changed quote data source. I also wished it to go into the cell of D3. The surrounding cells calculate boring stuff such as ESPP tax implications and TODAY() to figure out holding time. The problem I have with the execution of this macro is that it doesn't find D3 as a welcome home and decides to insert a whole column to the left of the D column. This leaves all my other wonderful goodness unchanged, since 'E' is the new 'D' and the cell functions are updated accordingly. Call me old-fashioned, but I wish column 'D' to remain where it is, BUT with the updated information downloaded courtesy Yahoo! Inc. Anyone know how to tweak this situation to enable me to update D3 with the oh so desirable current data? I thank you in advance for your time and apologize that I can't offer too much assistance within this forum. I'd be happy to pre-offer assistance in exchange in the following areas: C, C++, MFC, Windows Programming (within my capability), and some C-based cell phone programming. Best, Me!~ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
External data not updating | Excel Discussion (Misc queries) | |||
External Data | Excel Discussion (Misc queries) | |||
How do I create a list in excel that contains external data? | Excel Discussion (Misc queries) |