Macro for adding a spreadsheet into workbook - Statistica Visual Basic - Statistica - Dell Community

Macro for adding a spreadsheet into workbook

Macro for adding a spreadsheet into workbook

This question is not answered

 

Hello!

 

I am fairly new to Statistica (version 12) and am struggeling with some general issues in programming macros.

 

For a weekly report I want to querry data from a SQL db and the process the data. The data I get from the db needs to be processed into a second table. Aftwerwards I want to generate a report out of the second tables data. What I am looking for is code for creating a spreadsheet and insert it into the existing workbook, afterwards I need the handle to the spreadsheet. I tried the following code, which does not give me the handle to the spreadsheet at the end!?

How do I get the handle?

 

Thx a lot!

-Jean-

Sub Main
 Dim myWB As Workbook
 Dim NewItem As WorkbookItem
 Dim NewFolder As WorkbookItem
 Dim newssheet As Spreadsheet

 ' Create the new Workbook
 Set myWB=ActiveWorkbook


 ' Create a new Spreadsheet
 Set newssheet=Spreadsheets.New
 Set NewItem=myWB.InsertObject(newssheet,myWB.Root,scWorkbookFirstChild )
 ' Name the new Workbook item (Spreadsheet)
 NewItem.Name="This is a new Spreadsheet in the Folder"

 MsgBox TypeName(NewItem)

End Sub
Verified Answer
  • Hi, Jean,

    You're very, very close to what you need.  Try the revised code below to get what you need (My changes are in red):

    Sub Main
    Dim myWB As Workbook
    Dim NewItem As WorkbookItem
    Dim NewFolder As WorkbookItem
    Dim newssheet As Spreadsheet

    ' Create the new Workbook
    Set myWB=ActiveWorkbook

    Dim p as long

    ' Create a new Spreadsheet
    Set newssheet=Spreadsheets.New
    Set NewItem=myWB.InsertObject(newssheet,myWB.Root,scWorkbookFirstChild )
    ' Name the new Workbook item (Spreadsheet)
    NewItem.Name="This is a new Spreadsheet in the Folder"

    p = NewItem.ID

    MsgBox Cstr(p)

    End Sub

     

    -BC

     

  • Hi BC!

     

    Thanks for your quick reply. It solved my problem only partially, but got me onto the right track. I found the property .object which is exactly what I needed. Now I can use S2 like any other spreadsheet handle.

     

    Cheers

    -Jean-

     

    ' get handle to input spreadsheet
    Set S1 = ActiveDataSet
    ' create final spreadsheet
    Set S2 = Spreadsheets.New
    ' Insert the new Spreadsheet into the Workbook, at root
    Set NewItem=myWB.InsertObject( S2, myWB.Root,scWorkbookFirstChild)
    ' Name the new Workbook item (Spreadsheet)
    NewItem.Name="T_test_extract"
    Set S2 = NewItem.Object
All Replies
  • Hi, Jean,

    You're very, very close to what you need.  Try the revised code below to get what you need (My changes are in red):

    Sub Main
    Dim myWB As Workbook
    Dim NewItem As WorkbookItem
    Dim NewFolder As WorkbookItem
    Dim newssheet As Spreadsheet

    ' Create the new Workbook
    Set myWB=ActiveWorkbook

    Dim p as long

    ' Create a new Spreadsheet
    Set newssheet=Spreadsheets.New
    Set NewItem=myWB.InsertObject(newssheet,myWB.Root,scWorkbookFirstChild )
    ' Name the new Workbook item (Spreadsheet)
    NewItem.Name="This is a new Spreadsheet in the Folder"

    p = NewItem.ID

    MsgBox Cstr(p)

    End Sub

     

    -BC

     

  • Hi BC!

     

    Thanks for your quick reply. It solved my problem only partially, but got me onto the right track. I found the property .object which is exactly what I needed. Now I can use S2 like any other spreadsheet handle.

     

    Cheers

    -Jean-

     

    ' get handle to input spreadsheet
    Set S1 = ActiveDataSet
    ' create final spreadsheet
    Set S2 = Spreadsheets.New
    ' Insert the new Spreadsheet into the Workbook, at root
    Set NewItem=myWB.InsertObject( S2, myWB.Root,scWorkbookFirstChild)
    ' Name the new Workbook item (Spreadsheet)
    NewItem.Name="T_test_extract"
    Set S2 = NewItem.Object