Announcement

Collapse
No announcement yet.

Access to Excel

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

  • Fred Harris
    replied
    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

    Leave a comment:


  • Michael Mattias
    replied
    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.

    Leave a comment:


  • Fred Harris
    replied
    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?

    Leave a comment:


  • Martin Dorfinger
    started a topic Access to Excel

    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.
Working...
X