Announcement

Collapse
No announcement yet.

Access to Excel

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Access to Excel

    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.

  • #2
    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?
    Fred
    "fharris"+Chr$(64)+"evenlink"+Chr$(46)+"com"

    Comment


    • #3
      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.
      Michael Mattias
      Tal Systems Inc. (retired)
      Racine WI USA
      [email protected]
      http://www.talsystems.com

      Comment


      • #4
        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.

        Code:
        #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
        Fred
        "fharris"+Chr$(64)+"evenlink"+Chr$(46)+"com"

        Comment

        Working...
        X