by Steve James (3 Submissions)
Category: Miscellaneous
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Fri 25th July 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Allows you to partially automate the use of the DBase functions in an Excel work sheet. A real pain to do by manual insert.Build on this and
API Declarations
' This is a super program to automate the use of Excel's DataBase
' functions. You will have to add 6 text boxes and 3 command buttons
' As many labels as you feel are necessary
' You will also need 4 listboxes and two combo boxes
' Declarations
Public Excel As Excel.Application ' This line is very important
Dim Worksheet1 As Worksheet
VBcoders
' ' 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
' [email protected] Thanks Steve
'*****************************************************
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 combo box
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)
' Note if you insert a zero you will get an error
' to avoid this get rid of the format below and just use
' list4.list(i)
Worksheet1.Cells(8 + i, 5) = Format(List4.List(i), "$###,###.##")
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
'Here the colon will give you problems
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
Rem in somehting like
'
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) = " Your current List count is "
Worksheet1.Cells(3, 8) = "=" & List1.ListCount
Worksheet1.Cells(4, 8) = " Re enter the formula below without the slash"
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 You will get error 'Expected variable not Project if you
Rem don't use
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 are optional statements to 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
No comments have been posted about Allows you to partially automate the use of the DBase functions in an Excel work sheet. A real pain. Why not be the first to post a comment about Allows you to partially automate the use of the DBase functions in an Excel work sheet. A real pain.
