alok
12/19/2006 6:26:00 PM
The code I wrote is a VBA procedure. What you need to do is..
1. Open your excel workbook.
2. Click Alt-F11 or Tools/Macros/Visual Basic Editor
3. Insert a Module (In the Visual basic IDE click on Insert/Module). You
will see that a module (Module1) has been added to your workbook.
4. Double click on the Module1 that has been added. (If you do not see the
Module1 then the Project Explorer needs to be turned on. It can be turned on
by Cntrol-R) or by View/Project Explorer in the Microsoft Visual Basic screen.
5. Copy the entire code from Sub ExportData to End Sub into the blank area
in the module.
6. Next double click on ThisWorkbook (again 'ThisWorkbook' should be visible
in the Project Explorer window. If it is not visible, click on the + sign in
front of 'Microsoft Excel Objects' folder in the Project Explorer window.
7. You will now see another blank space in the window to the right of the
Project Explorer. (Just like you did when you did step#4)
8. There will be two drop downs in the top part of this window. Click on the
left drop down and select 'Workbook'. Next click on the right drop down and
select 'Open'. You will then see a piece of Visual Basic Code like the
following
Private Sub Workbook_Open()
End Sub
9. Now type ExportData in between those two lines like this
Private Sub Workbook_Open()
ExportData
End Sub
By doing this you are telling Excel to start running the ExportData
procedure as soon as you open the workbook.
Alok
"mikejhelms" wrote:
> Hi Alok and thanks for the response. When you read what I'm about to write
> you'll groan and mutter , "What a complete novice!" But what can I say? My
> follow up question is, Where do I put the code you have suggested? In each
> of the two Excel cells I mentioned in my post? Also, what variable in your
> code represents the location of the text file it is writing to? And
> finally, what is this code? Visual Basic, a macro or something else? Thank
> you for your patience.
>
> Michael
>
> "Alok" wrote:
>
> > Try using the following procedure
> >
> > Sub ExportData()
> >
> > Dim sFile$
> > Dim f%, wNumTimes%
> >
> > Debug.Print Now
> >
> > Application.Cursor = xlWait
> > sFile$ = "C:\Data.txt"
> > f = FreeFile
> > On Error GoTo ErrorInFileOpen
> > Open sFile For Output As f
> > On Error GoTo 0
> > Write #f, Sheet1.Cells(1, 1).Value, Sheet1.Cells(1, 2).Value
> > Close f
> > Application.OnTime Now + TimeValue("00:01:00"), "ExportData"
> > Application.Cursor = xlDefault
> >
> > Exit Sub
> >
> > ErrorInFileOpen:
> > Application.Cursor = xlDefault
> > If wNumTimes = 10 Then
> > MsgBox "Err:" & Err & " " & Error & vbCrLf & "The file " & sFile & "
> > could not be opened even after " & wNumTimes & " tries."
> > End If
> > wNumTimes = wNumTimes + 1
> > Resume
> >
> > End Sub
> >
> >
> > "mikejhelms" wrote:
> >
> > > Hi. I am working with an Excel spreadsheet that has a DDE link which plucks
> > > two
> > > pieces of data out of a live data stream so that about once a second those
> > > two pieces of data pop up in the first two cells of the spreadsheet. Can you
> > > suggest a way I can export these two data points as they appear in real-time
> > > to an analysis program which will accept them only if it is able to read them
> > > from a text file? That is, how can I send these two mini data streams each
> > > appearing in its own cell (the stream being composed of two values that each
> > > change about once a second and as often as not donâ??t change together) from
> > > the Excel spreadsheet to a text file where they can be read by the analysis
> > > program. These values would then need to be overwritten in the text file by
> > > the next two Excel exported values. Any help would be greatly appreciated.
> > > Thanks.
> > >
> > > Michael