Re: Different Macro behavior when called from Immediate Window vs.
- From: TimWms <TimWms@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 27 Jan 2008 16:22:01 -0800
Regardless of how i import the data, I want to erase the previous data
automatically. Try this with a new workbook:
Type text into cell A1.
Add this macro into module -
Function EraseA1() As Boolean
If (ActiveSheet.Cells(1, 1) <> Empty) Then
ActiveSheet.Cells(1, 1).Delete
End If
If (ActiveSheet.Cells(1, 1) <> Empty) Then
EraseA1 = False
Else
EraseA1 = True
End If
End Function
Now in another cell on the same worksheet use the formula =EraseA1(). The
result is FALSE.
Now in the immediate window type x = EraseA1(), and Cell A1 on the worksheet
gets erased.
What am I missing?
"Don Guillett" wrote:
This is the sort of thing I do for clients on a daily basis..
I think you would be much better off using data>import external data>new>put
in your url>import.
Then record a macro while doing. Then modify to put into a loop and copy
desired data from the fetch sheet to another sheet with just the data you
want. Send a workbook to my address below with your symbols, etc and I will
take a look.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@xxxxxxxxxxxxx
"TimWms" <TimWms@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BAC765D6-13F8-4AEC-9A38-B03F0ECA4C09@xxxxxxxxxxxxxxxx
I'm not sure if you will be able to follow along....
Rem Load Option Chain Pricing from Yahoo using the web address
Rem http://finance.yahoo.com/q/os?s=YHOO&m=2008-01
Rem NOTE: Whenever a .refresh is executed, function may be called again
from
the beginning, function must be reentrant
Function LoadChains(Stock As String, NextExp As Date, Series As Integer)
As
Boolean
Dim ws As Worksheet
Dim conn As String, newconn As String
Dim i As Integer
Static LastCRef(4) As Date
If NextExp <> 0 Then
Set ws = Worksheets("Chain " & Format(Series, "#0"))
If ws.QueryTables.Count <> 0 Then
conn = ws.QueryTables(1).Connection
End If
newconn = "URL;http://finance.yahoo.com/q/os?s="; _
& Stock _
& "&m=" & Format(NextExp, "yyyy") & "-" & Format(NextExp, "m")
If conn <> newconn Then
Do While ws.QueryTables.Count <> 0
ws.QueryTables(1).Delete
Loop
LastCRef(Series) = 0
If ws.Cells(1, 1) <> Empty Then
ws.Range("A1:O50").ClearContents
End If
With ws.QueryTables.Add( _
Connection:=newconn, Destination:=ws.Cells(1, 1))
.BackgroundQuery = False
.EnableRefresh = True
.EnableEditing = True
.FillAdjacentFormulas = True
Rem .Name = "Chain" & Format(Series, "#0") & "_" & Stock
.RefreshOnFileOpen = True
.RefreshStyle = xlOverwriteCells
.SaveData = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "8,12"
End With
End If
If (LastCRef(Series) - LastClose()) < 0 Then ' if close has
occurred since last refresh
ws.QueryTables(1).Refresh (False)
LastCRef(Series) = Now
End If
LoadChains = True
Else ' NextExp = 0 - do nothing
LoadChains = False
End If
End Function
- Follow-Ups:
- Re: Different Macro behavior when called from Immediate Window vs.
- From: Don Guillett
- Re: Different Macro behavior when called from Immediate Window vs.
- References:
- Re: Different Macro behavior when called from Immediate Window vs. She
- From: Don Guillett
- Re: Different Macro behavior when called from Immediate Window vs.
- From: TimWms
- Re: Different Macro behavior when called from Immediate Window vs.
- From: Don Guillett
- Re: Different Macro behavior when called from Immediate Window vs. She
- Prev by Date: Re: Sum after every blank cell
- Next by Date: Displaying published htm file from remote share
- Previous by thread: Re: Different Macro behavior when called from Immediate Window vs.
- Next by thread: Re: Different Macro behavior when called from Immediate Window vs.
- Index(es):
Relevant Pages
|
