Access to Excel

[Martin Dorfinger]

How can I write and read to a still opened Excel sheet? I know the code to create a new Excel appliaction and use it to write and read. But I do not know how to detemine which Excel sheets are still opend and how to write into the sheet.

[Fred Harris]

Excel Workbooks can contain many Excel Sheets, and to write to a sheet it needs to be selected with programming code. I don't believe any particular user has to have the sheet selected to have it written to by your PB program. However, if the sheet would be selected and your PB program was writting data to it, the user would see it miraculously appear. If it was being pumped to a sheet he didn't have selected, he would only see the new data when he selected it within the UI of Excel.

Do you need code to show how to select a particular worksheet?

[Michael Mattias]

There's a demo here of writing to an Excel spreadsheet using DDE....
A simple DDE client example July 02, 2001

.. but somehow I think you are not using that. But hey, it works!

There are likely one or more examples of using the COM interface here, somewhere.

[Fred Harris]

Excel Minipulations of WorkSheets

What might help is getting/setting the 'ActiveSheet'. Below is some code I just tested with PBWin 9.01 and it seems to work OK. The program iterates through all the sheets in a Workbook and prints out the name of each one to a MsgBox. You need to change the file name to one you have somewhere. The PBExcel.inc file I created with the new PB COM Browser by checking the box 'Only Create IDispatch Interfaces', which sets up the IDBind option. Also, I filled in the textbox to preface interfaces with XL.

#Compile Exe
#Dim All
#Include "PBExcel.inc"

Function PBMain()
  Local vBool,vVnt,vFileName,vInSheet As Variant
  Local objExcelWorksheet As XL_Worksheet
  Local objExcelWorkbook As XL_Workbook
  Local objExcelApp As XL_Application
  Local iNum As Long, i As Long
  Local strExcelVersion As String

  strExcelVersion="Excel.Application"
  Set objExcelApp = XL_Application In strExcelVersion
  If IsFalse IsObject(objExcelApp) Then
     Set objExcelApp = New XL_Application In strExcelVersion
     If IsFalse IsObject(objExcelApp) Then
        MsgBox("Problem Opening Excel!")
        Exit Function
     End If
  End If
  vBool = 1
  Object Let objExcelApp.Visible = vBool

  ''!!! Change to an Excel File On Your Computer!!!
  vFileName = "C:\Tallies\RawData\01200101.xls"
  ''!!! Change to an Excel File On Your Computer!!!

  Object Call objExcelApp.WorkBooks.Open(vFileName) To vVnt
  Set objExcelWorkbook = vVnt
  Object Get objExcelWorkbook.Worksheets.Count To vVnt
  iNum = Variant#(vVnt)
  MsgBox("Count = " & Str$(iNum))
  Object Get objExcelWorkBook.ActiveSheet To vVnt
  Set objExcelWorkSheet = vVnt
  Object Get objExcelWorkSheet.Name To vVnt
  MsgBox("Sheet Name = " + Variant$(vVnt))
  For i = 1 To iNum
    vInSheet = i
    Object Call objExcelWorkbook.Sheets(vInSheet).Select
    Object Get objExcelWorkbook.ActiveSheet To vVnt
    Set objExcelWorkSheet = vVnt
    Object Get objExcelWorkSheet.Name To vVnt
    MsgBox("Sheet Name = " + Variant$(vVnt))
  Next i
  Object Call objExcelApp.Quit
  Set objExcelWorkSheet=Nothing
  Set objExcelWorkBook=Nothing
  Set objExcelApp=Nothing

  PBMain=0
End Function