Here is what I have.

I was subscriber for stocks real time market data, which was delivered via DDE. Basically-to receive data, all you have to do is type in cell following formula-

=MLINK|MFDC!'MSFT,LAST'

Then, as I understand, link was established and every time last price was changed-it will be change in cell to.

Number of current links is limited to 300. Than mean, you cannot receive more than 300 quotes on different stocks at same time. I don’t need a real time data, but I want to create a snapshot for about 1000-3000 stocks. So-as I understand, I need to create links for first 300 symbols, get data, write it into file, and then change formula in those cells to next 300 symbols and so on.

Here is what I wrote so far-

Public Type OC

Symbol As String

UpdateTime As Date

UpdateStatus As Integer

End Type

Public AllSymb() As OC

Sub ReqData()

Dim i As Integer

Dim FileNum As Integer

Dim LineCount As Integer

Dim One_Line As String

LineCount = 0

FileNum = FreeFile

Open "C:\NASDAQ\NAZ_ALL.txt" For Input As FileNum

Do While Not EOF(FileNum)

Line Input #FileNum, One_Line

LineCount = LineCount + 1

Loop

Close FileNum

ReDim AllSymb(LineCount - 1)

LineCount = 0

FileNum = FreeFile

Open "C:\NASDAQ\NAZ_ALL.txt" For Input As FileNum

Do While Not EOF(FileNum)

Line Input #FileNum, One_Line

One_Line = Trim(One_Line)

AllSymb(LineCount).Symbol = Trim(One_Line)

'Debug.Print AllSymb(LineCount)

LineCount = LineCount + 1

Loop

Close FileNum

For i = 1 To 200

Cells(i, 1) = UCase(AllSymb(i).Symbol)

AllSymb(i).UpdateStatus = 2

Cells(i, 2).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_DATE'")

Cells(i, 3).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",OPEN_PRICE'")

Cells(i, 4).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",BIDSIZE'")

Cells(i, 5).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",BID'")

Cells(i, 6).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ASK'")

Cells(i, 7).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ASKSIZE'")

Cells(i, 8).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",LAST'")

Cells(i, 9).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_VOL'")

Cells(i, 10).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_TIME'")

Cells(i, 11).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ACVOL'")

Next

End Sub

This sub is open file with tickers, load them into AllSymb() array and then-request data for first 300 symbols.

Now-I have to change those symbols somehow, after I receive data for first 300.

I have no idea, how to do it properly and if someone can recommend something-please do. Here is my lame solution-

In cell next total volume of shares traded, I did put 1, and in nest cell I wrote-if value in volume cell > that value in cell with number 1(which is always true)-then-call my function, which will write data into file and change links in row for another symbol.

Here is the function-

Public Function Update_Fnc(ByVal Location As String)

Dim sCell As String

Dim sRow As Integer

Dim sRecord As String

Dim Ticker As String

Dim FileNum As Integer

Dim Temp As String

Dim TempData As String

Dim i As Integer

Dim TempTime As Date

Dim Test As String

FileNum = FreeFile

sCell = Mid(Application.Caller.Address, 2, 1)

Temp = Application.Caller.Address

sRow = Mid(Temp, 3, Len(Temp) - 2)

If Cells(sRow, 2) <> "#WAIT" And Cells(sRow, 3) <> "#WAIT" And Cells(sRow, 4) <> "#WAIT" And Cells(sRow, 5) <> "#WAIT" And Cells(sRow, 6) <> "#WAIT" And Cells(sRow, 7) <> "#WAIT" And Cells(sRow, 7) <> "#WAIT" And Cells(sRow, 8) <> "#WAIT" And Cells(sRow, 9) <> "#WAIT" And Cells(sRow, 10) <> "#WAIT" And Cells(sRow, 11) <> "#WAIT" Then

sRecord = Cells(sRow, 2) & "," & Cells(sRow, 3) & "," & Cells(sRow, 4) & "," & Cells(sRow, 5) & "," & Cells(sRow, 6) & "," & Cells(sRow, 7) & "," & Cells(sRow, 8) & "," & Cells(sRow, 9) & "," & Cells(sRow, 10) & "," & Cells(sRow, 11)

Else

Exit Function

End If

Ticker = Cells(sRow, 1)

Test = Application.ActiveCell(sRow, 1).Value

TempData = Sheet1.Cells(sRow, 11)

Open "D:\Test\MFEED\" & Ticker & ".txt" For Append As #FileNum

Print #FileNum, sRecord

Close #FileNum

For i = LBound(AllSymb) To UBound(AllSymb)

If AllSymb(i).Symbol = Ticker Then

AllSymb(i).UpdateStatus = 1

AllSymb(i).UpdateTime = Format(Now, "H:M:SS")

TempTime = Format(Now, "H:M:SS")

Exit For

End If

Next

For i = LBound(AllSymb) To UBound(AllSymb)

If AllSymb(i).UpdateStatus = 2 And AllSymb(i).UpdateTime < TempTime Or AllSymb(i).UpdateStatus = 0 Then

ActiveSheet.Cells(sRow, 1) = UCase(AllSymb(i).Symbol) -------function exit here for some reason

ActiveSheet.Cells(sRow, 2).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_DATE'")

ActiveSheet.Cells(sRow, 3).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",OPEN_PRICE'")

ActiveSheet.Cells(sRow, 4).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",BIDSIZE'")

ActiveSheet.Cells(sRow, 5).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",BID'")

ActiveSheet.Cells(sRow, 6).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ASK'")

ActiveSheet.Cells(sRow, 7).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ASKSIZE'")

ActiveSheet.Cells(sRow, 8).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",LAST'")

ActiveSheet.Cells(sRow, 9).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_VOL'")

ActiveSheet.Cells(sRow, 10).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",TRADE_TIME'")

ActiveSheet.Cells(sRow, 11).Formula = "=MLINK|MFDC!'" & UCase(AllSymb(i).Symbol & ",ACVOL'")

AllSymb(i).UpdateStatus = 2

End If

Next

End Function

Up to the point, where I wrote “function exit here”-everything works just fine.

So –here my question number one-why it exits there with no sign of error or something? My guess that I wrote something wrong, but I do know how to write it properly.

Can you show mw, how and what I should write, to change link?

Or (second question)-maybe you can explain, how to do it right way from beginning?

Basically-all I need, if to call function or sub, which will change link, every time, when value in any row for particular symbol is changed.

Thank you!