Monetary Unit Sampling

claytonsamples

Forums

Hello,
I am an advanced beginner with VBA and novice IDEAScript writing.
I am trying to set-up a macro that prompts the user to select their file from a shared network drive and then process a plan and select the sample. I tried to re-work the process by recording a macro and reviewing the script.
Below I have copied the script generated from the macro. The deficiencies compared to what I would like to achieve are:
(1) Display a prompt allowing the user to select any file type via any file path
(2) Prompt to allow user to name database for Sample file
Thanks for the help.

Sub Main
Call ExcelImport() 'I:\Audit\Inventory.xlsx'
Call MUSExtraction() 'Inventory.IDM'
End Sub

' File - Import Assistant: Excel
Function ExcelImport
Set task = Client.GetImportTask("ImportExcel")
dbName = "I:\Audit\Inventory.xlsx"
task.FileToImport = dbName
task.SheetToImport = "Inventory"
task.OutputFilePrefix = "Inventory"
task.FirstRowIsFieldName = "TRUE"
task.EmptyNumericFieldAsZero = "TRUE"
task.PerformTask
dbName = task.OutputFilePath("Inventory")
Set task = Nothing
Client.OpenDatabase(dbName)
End Function

' Sampling: Monetary Unit
Function MUSExtraction
Const WI_HighValueHandling_AGGREGATE = 0
Const WI_HighValueHandling_FILE = 1
Const WI_RangeOfValues_POSITIVES = 0
Const WI_RangeOfValues_NEGATIVES = 1
Const WI_RangeOfValues_ABSOLUTES = 2
Const WI_TaskType_FIXED = 0
Const WI_TaskType_CELL = 1

Set db = Client.OpenDatabase("Inventory.IDM")
Set task = db.MUSExtraction
task.IncludeAllFields
task.TaskType = WI_TaskType_FIXED
task.RangeOfValues = WI_RangeOfValues_POSITIVES
task.HighValueHandling = WI_HighValueHandling_FILE
task.HighValueFilename = "High Values2.IDM"
task.SampleInterval = 12942.17
task.RandomValue = 9830.55
task.FieldToSample = "EXT_COGS_COST"
dbName = "Monetary Sample3.IDM"
task.MUSExtractionFilename = dbName
task.CreateVirtualDatabase = False
task.PerformTask
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function

Brian Element Wed, 05/17/2017 - 19:15

Hi Clayton, here you go. I added an open dialog to get the file and an input box for the user to enter the sameple name.


Sub Main
	Call ExcelImport()	'I:\Audit\Inventory.xlsx'
	Call MUSExtraction()	'Inventory.IDM'
End Sub

' File - Import Assistant: Excel
Function ExcelImport
	
	Set task = Client.GetImportTask("ImportExcel")
		Set obj = client.CommonDialogs
			dbName = obj.FileOpen("", "", "All Files (*.*)|*.*||;")
		Set obj = Nothing
		'dbName = "I:\Audit\Inventory.xlsx"
		task.FileToImport = dbName
		task.SheetToImport = "Inventory"
		task.OutputFilePrefix = "Inventory"
		task.FirstRowIsFieldName = "TRUE"
		task.EmptyNumericFieldAsZero = "TRUE"
		task.PerformTask
		dbName = task.OutputFilePath("Inventory")
	Set task = Nothing
	Client.OpenDatabase(dbName)
End Function

' Sampling: Monetary Unit
Function MUSExtraction
	Const WI_HighValueHandling_AGGREGATE = 0
	Const WI_HighValueHandling_FILE = 1
	Const WI_RangeOfValues_POSITIVES = 0
	Const WI_RangeOfValues_NEGATIVES = 1
	Const WI_RangeOfValues_ABSOLUTES = 2
	Const WI_TaskType_FIXED = 0
	Const WI_TaskType_CELL = 1
	
	dbName = InputBox("Enter the sample name, do not add an extension", "Sample File Name", "")
	dbName = dbName & ".IDM"
	
	Set db = Client.OpenDatabase("Inventory.IDM")
		Set task = db.MUSExtraction
			task.IncludeAllFields
			task.TaskType = WI_TaskType_FIXED
			task.RangeOfValues = WI_RangeOfValues_POSITIVES
			task.HighValueHandling = WI_HighValueHandling_FILE
			task.HighValueFilename = "High Values2.IDM"
			task.SampleInterval = 12942.17
			task.RandomValue = 9830.55
			task.FieldToSample = "EXT_COGS_COST"
			'dbName = "Monetary Sample3.IDM"
			task.MUSExtractionFilename = dbName
			task.CreateVirtualDatabase = False
			task.PerformTask
		Set task = Nothing
	Set db = Nothing
	Client.OpenDatabase (dbName)
End Function