Populating TextBoxes with values from an imported database

2 posts / 0 new
Last post
Joined: 01/12/2018 - 12:18
Populating TextBoxes with values from an imported database

I have a macro now that performs extractions and random samples based on a number of user-entered edit boxes on the dialog screen. The user entered data comes from a standardized Excel workbook that contains the proper data in a structured manner. What am I wondering is if there is a way to "import" those values such that the text boxes are essentially filled in with the data contained in the Excel workbook?
Thanks for any information!

Brian Element's picture
Brian Element
Joined: 07/11/2012 - 19:57

Hi there,

I have put together a little demo program that will read an excel spreadsheet and use the values in a dialog, I have attached both files for you to look at.

The spreadsheet has the following info:

It contains the text for the labels and default values for the text boxes.

This is what the dailog looks like:

You can see that the above is the same as from the excel spreadsheet.  Below is a copy of the attached code, llet me know if there is something that doesn't make sense.

'* Script:	Populate Text Box from Excel.iss
'* Author: Brian Element - brian.element@ideascripting.com
'* Date: March 13, 2019
'* Purpose: This script is a demo on how you can extract information from an excel spreadsheet and use it
'*		to populate a dialog.  This script needs the Populate TextBoxes Example.xlsx in the project folder
'* Disclaimer: This ideascript is provided as is without any warranties.

Option Explicit
Dim sFilename As String
Dim sFieldname As String
Dim bExitScript As Boolean

'commonly used objects - define here don't have to keep 
'redifining them in each function
Dim db As database
Dim table As tableDef
Dim field As field
Dim task As task
Dim sExcelSpreadseet As String  'name of the spreadsheet that holds the items
'variables to hold the items from the excel spreadsheet
Dim sTextBox1 As String
Dim sTextBox2 As String
Dim sContent1 As String
Dim sContent2 As String

Sub Main
	'set-up the variable with the spreadsheet name and path
	sExcelSpreadseet = Client.WorkingDirectory() & "Populate TextBoxes Example.xlsx"
	Call getValuesFromExcel() 'call a function to extract the values from the spreadsheet
	Call menu()
	If Not bExitScript Then
		MsgBox "Script Complete"
		MsgBox "Script Cancelled"
	End If
End Sub

Function getValuesFromExcel()
	Dim excel As Object
	Dim oBook As Object
	Dim oSheet As Object
	'create the excel object in order to read the spreadsheet
	Set excel = CreateObject("Excel.Application")
		'don't make the spreadsheet visible
		excel.Visible = False
		'open a workbook to extract the information
		Set obook = excel.Workbooks.Open(sExcelSpreadseet)
			'items should be located in first worksheet if not this needs to be changed
			Set oSheet = oBook.Worksheets.Item(1)
				'extract the information and place them in variables to be used in the dialog
				sTextBox1 = oSheet.Cells(1, 2).Value
				sTextBox2 = oSheet.Cells(2, 2).Value
				sContent1 = oSheet.Cells(3, 2).Value
				sContent2 = oSheet.Cells(4, 2).Value
			Set oSheet = Nothing
		Set oBook = Nothing
		'close the objects and close excel, if you don't use the quit excel will appear in the task manager and will have to closed manually
	Set excel = Nothing
End Function

Function menu()
	Dim dlg As NewDialog
	Dim button As Integer
	On Error Resume Next
	sFilename = Client.CurrentDatabase.Name
	'place the information from the excel spreadsheet into the two edit boxes
	dlg.TextBox1 = sContent1
	dlg.TextBox2 = sContent2
	button = Dialog(dlg)
	If button = 0 Then bExitScript = True 'user select x to exit dialog
End Function

Function DisplayIt(ControlID$, Action%, SuppValue%)
	Dim bExitMenu As Boolean
	Select Case Action%
		Case 1
		Case 2
			Select Case ControlID$
				Case "CancelButton1"
					bExitMenu = True
					bExitScript = True
				Case "OKButton1"
					If sFilename = "" Then
						MsgBox "Please select a file"
						bExitMenu = True
					End If
				Case "PushButton1"
					Call GetFilename()

			End Select
	End Select
	If bExitMenu Then
		DisplayIt = 0
	 	DisplayIt = 1
	 End If
	 If sFilename <> "" Then
	 	DlgText "Text2", iSplit(sFilename, "", "\", 1, 1)
	 	DlgText "Text2", "Please select file"
	 End If
	 'set the text based on the information from excel
	 DlgText "Text3", sTextBox1
	 DlgText "Text4", sTextBox2
End Function

Function GetFilename() As String
	Dim obj As ojbect
	Set obj = Client.CommonDialogs
		sFilename = obj.FileExplorer()
	Set obj = Nothing
End Function