Re: How to use VB with Excel Spread***?

Richard,

If what you are asking is "How do I get VB to talk to an Excel
spread***, I can answer that. As to whether to use Access or Excel,
I cannot answer that without hearing a lot more about your project. To
write and read spreadsheets, the following will get youi going.


1.You need the following reference from your Project dropdown:
Microsoft Excel 9.0 Object Library. (This works with Excel 2000)

2.You need the folowing objects declared in the
Declarations section:

Dim objWorkbook As Object
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

3.You need the following code in your own Sub-Procedure
(I call mine "OpenSS") :

Set xlApp = New Excel.Application
xlApp.Workbooks.Add
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Open _
(FileName:="C:\"MyWork***.xls", _
updatelinks:=0, _
ReadOnly:=False, _
Format:=2)

' (ReadOnly True or False depending on your needs.)

' Then you can access cells as follows:
xlBook.Sheets(1).Cells(1, 2).Value = "Hello world!"

' With 1 being row number and 2 being column number and the
Sheets(1) being the page of your SS.

4. In an Exit sub or command button you need the following:

'Close Excel:
Application.Interactive = True 'This line returns Excel
communication for users that you closed above.

xlApp.ActiveWorkbook.Close True 'Needed to Save changes,
(don't need if read only).

Set xlApp = Nothing
Set xlBook = Nothing
Application.Interactive = True
Application.Quit


You must be very careful that you have all these components or your app
will error out and lock up Excel for which the only remedy is a
re-boot.
Because of this, debugging an Excel project can be somewhat slow going
and frustrating at times. Comparatively speaking, Access is a snap.

Beware, this code was snipped out of various places, I hope nothing got
lost and the margins are probably a mess after the posting process.


Hope this helps,
Dave

richardv2 wrote:
> I'm writing a program for stock market analysis. The only data I need
> is date and closing price. I can do...
>
> Text file: XYZ.txt
>
> "09/02/05", 13.25
> "09/03/05", 13.31
> etc. YUK!
>
> Access Database: XYZ.mdb
> I know how to use data controls to hook up to Access.
>
> Because I want to see moving averages, RSI, MACD and other technical
> indicators, I think an Excel spread*** would be best for me to see
> all I want on one page. Problem is, I have no clue how to "talk to" an
> Excel spread*** with Visual Basic. Any pointers on where to look?
>
> ...or would I be better off to do it in Access??

.

Quantcast