• Home
 · 
• Miscellaneous
 · 
• A clearer description of an earlier post on creating a data set that you insert into Excel to make

A clearer description of an earlier post on creating a data set that you insert into Excel to make

A clearer description of an earlier post on creating a data set that you insert into Excel to make it easier to use it's DFunctions. This

API Declarations



' ' Declarations
Public Excel As Excel.Application ' This line is very important
Dim Worksheet1 As Worksheet
' *************************************************************



Rate A clearer description of an earlier post on creating a data set that you insert into Excel to make

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

'                'Your Form should look like this
'         Label1          Label2         Label3         Label4
'     (Says Trees)     (Says Height)    (Says Age)   (SaysProfit)
'            
'
'       Textbox1 ,       Textbox2,         Textbox3,    Textbox4
'
'       ( Sample)            ( Sample)       (Sample)   (Sample)
'        ( Oak)               ( 30 )          (45)       (367)
'
'  
'
'       Listbox1,        Listbox2,         Listbox3,    Listbox4
'
'
'
'        ComboBox2                                 ComboBox1
'                                                 (Drop Down)
'
'
'
'   Command Button    Textbox5      TextBox6      Command Button
'
'  (Add to List)   (Greater than)  (Less than)   (Activate Inquiry)
'
'    '  ************************************************

'  Now Insert the words--  Height -- Age --  Profit into Combox1 '  

'    directly into the list part of the properties window.

' All of the other code goes into command Click
******************************************************************
' This is a super program to automate the use of Excel's DataBase
' functions. You will have to add 8 text boxes 2 combo boxes and 
' 3 ' command buttons with as many labels as you feel are necessary
'


'  ****************************************
'                    '     Code
'
Private Sub Command1_Click()
Set Excel = New Excel.Application
Set Workbook = Excel.Workbooks.Add
Excel.Visible = True
Set Worksheet1 = Workbook.ActiveSheet
'*************************************************
' Since I have limited time I was not able to automate
' the following  ----  give me more time and I will
' PS  I could use some help in inserting phrase Such As-->
' Worksheet.Cell(x,y) = "=" & "=Database(A2:B5,"Something",A12:R25"
' Vb won't pass the range due to the colon  --- HELP. Contact 
'
'PS  Does anyone know how to Open Excel's Menu Items for use
'    In a VB program ?
'
'PSS  Does anyone know how to open Maple 6 with vb command line
' and insert like we are doing in Excel ?
' [email protected]  Thanks Steve
'*****************************************************
Rem   This next line is used to Demo the project
Rem    You will want to make Trees etc. whatever you want
**



Worksheet1.Cells(1, 2) = "Trees": Cells(1, 3) = "Height": Cells(1, 4) = "Age": Cells(1, 5) = "Profit"

Worksheet1.Cells(7, 2) = "Trees": Cells(7, 3) = "Height": Cells(7, 4) = "Age": Cells(7, 5) = "Profit"

Worksheet1.Cells(2, 2) = Combo2.Text '  Select your tree

Rem   This is pretty Neat !
Rem the "greater" than sign will move to column 3, 4 or 5  with the
Rem  users chosen item in the Combobox1

Rem I am  moving the > with users selection of combo item
tempv = Combo1.ListIndex + 3 ' Adjust for the indent of the Excel Data Table

Worksheet1.Cells(2, Combo1.ListIndex + 3) = ">" & Text5.Text


Worksheet1.Cells(2, 6) = "<" & Text6.Text    ' This is fixed at cell 2,6


For i = 0 To List1.NewIndex
Worksheet1.Cells(8 + i, 2) = List1.List(i) 'Current Index num in loop

Worksheet1.Cells(8 + i, 3) = List2.List(i)
Worksheet1.Cells(8 + i, 4) = List3.List(i)
Worksheet1.Cells(8 + i, 5) = List4.List(i)            

' ' ********************

Rem  In  the prior version that I posted I used
'      'Format(List4.List(i), "$###,###.##") instead of 
'  '     List4.List(i) --- the old line will cause problems
'   '    for zero values  
'' '  **************************************

Next i

With Excel.Application.ActiveSheet

Rem   The User picks Height,Age or Profit
Rem    Next line enters this pick into the last cell in row 1

Worksheet1.Cells(1, 6) = Combo1.Text

End With


' Now you are ready to start your calculations in excel
'and return those variables to a vb interface


tempsum = 0

Rem Determine the sum of the number under the heading
Rem  That which user picks from combo1
Rem  on the Excel sheet that would be the sum of the range
Rem   B: something  ---> vb won't let me insert a colon directly


'
For t = 8 To 8 + List1.NewIndex
tempsum = tempsum + Worksheet1.Cells(t, Combo1.ListIndex + 3)

Next t

Worksheet1.Cells(1, 8) = tempsum

Worksheet1.Cells(2, 8) = Combo1.ListIndex + 3

Worksheet1.Cells(2, 8) = " You current List count is "
Worksheet1.Cells(3, 8) = "=" & List1.ListCount

' *********
' Rem  This next line will be put on the worksheet
'  to allow the user to go to Insert/Functions/Database   and 
'  have a clue as to how to set up Excel's hard to use Data Base
'   formulas  !!!  
Worksheet1.Cells(4, 8) = " Re enter the formula below without the  slash"
' ***************
''    Note the backslash is used to pass things that contain a colon
'    'or other non transferable stuff
''  *****************************
'
' Be sure to put data into all text boxes 
' Trees where trees should be and numbers where numbers
' should be  ! ! !  Avoid Errors

Worksheet1.Cells(5, 8) = "\=(Daverage ,B3:B9,""Height"",B1:F2)\"

Worksheet1.Cells(6, 8) = " and change the sample B9 to B followed by List count given above"
Worksheet1.Cells(7, 8) = " Use the same pattern for the other DBase formulas found at Insert/Function/Database"


End Sub

Private Sub Command2_Click()


Combo2.AddItem Text1.Text


List1.AddItem (Text1.Text)
List2.AddItem (Text2.Text)
List3.AddItem (Text3.Text)
List4.AddItem (Text4.Text)

Text1.SetFocus

End Sub

Private Sub Form_Load()
Rem You will get error " User Definded type not definded
Rem  if you don't Go to Project / References/ Microsoft
Rem  Excel 9 Object Library

Rem  Rem You will get error " User Definded type not definded
Rem  if you don't Go to Project / References/ Microsoft
Rem  Excel 9 Object Library

Rem You will get error 'Expected variable not Project if you
Rem don't use
Rem  Public Excel As Excel.Application in the Declarations

Rem  Below is optional the statements make it easier to enter text

Text1.SelStart = 0
Text1.SelLength = Len(Text1.Text)

End Sub

Private Sub Text1_Click()
Text1.SelStart = 0
Text1.SelLength = Len(Text1.Text)
End Sub

Private Sub Text1_GotFocus()
Text1.SelStart = 0
Text1.SelLength = Len(Text1.Text)
End Sub

Private Sub Text2_GotFocus()
Text2.SelStart = 0
Text2.SelLength = Len(Text2.Text)
End Sub

Private Sub Text3_GotFocus()

Text3.SelStart = 0
Text3.SelLength = Len(Text3.Text)
End Sub

Private Sub Text4_GotFocus()
Text4.SelStart = 0
Text4.SelLength = Len(Text4.Text)


End Sub

Private Sub Text5_GotFocus()
Text5.SelStart = 0
Text5.SelLength = Len(Text5.Text)
End Sub

Private Sub Text6_GotFocus()
Text6.SelStart = 0
Text6.SelLength = Len(Text6.Text)
End Sub