Skip to main content

Client.OpenDatabase

Hello,
I'm new here and looking for an easy wildcard (hopefully the right term in English) in the database-name.
At the ? below It's not working with % or * or $ of "?" and I want to make one script for all years.
 
Part of the code:
------------------------------------------------------------------------------------
Dim pad As String
Dim reg As String  
Dim db As Object
Dim dbName As String
pad         = Client.WorkingDirectory   
Set db = Client.OpenDatabase ("SRT" & ? & "_financial_table.IMD")
and so on------------------------------------------------------------------------------------
There are 3 posible databases, each in a different folder:
* \Finance\SRT\2011\ SRT2011_financial_table.imd
* \Finance\SRT\2012\ SRT2012_financial_table.imd
* \Finance\SRT\2013\ SRT2013_financial_table.imd
Byhteway, I use IDEA 8.
Sorry for my English, but it is hard to translate into English jargon.

Brian Element Tue, 04/01/2014 - 12:25

Hello hwes and welcome to the site.

Unfortunately you can not use wildcards but you can use variables in their place.  So you could do something like this:

Dim iYear as integer

for iYear = 2011 to 2013

        set db = Client.OpenDatabase ("SRT" & iYear & "_financial_table.IMD")

next i

hwes Wed, 04/02/2014 - 04:21

Brian,
Thanks for your answer. The part <for iYear = 2011 to 2013> only runs partly. If the database is from 2011 it runs, but not with the database from 2012 of 2013.
If I change it into <for iYear = 2011 to 2012> it works for 2011 and 2012 but that's not what I was looking for.
Harm

Brian Element Wed, 04/02/2014 - 07:12

Hi Harm, here is some sample code for you that hopefully will help you out. It will perform an extract on three files with different years. I just used the Sample-Detailed Sales.IMD file that is in the samples folder and copied and renamed it. If this doesn't help you out maybe you can post your code or explain exactly what you are doing. Good luck.

Sub Main
	Call DirectExtraction()	'Sample-2011-Detailed Sales.IMD
	client.RefreshFileExplorer
End Sub


' Data: Direct Extraction
Function DirectExtraction
	Dim db As database
	Dim task As task
	Dim i As Integer
	For i = 2011 To 2013
		Set db = Client.OpenDatabase("Sample-" & i & "-Detailed Sales.IMD")
		Set task = db.Extraction
		task.IncludeAllFields
		dbName = client.UniqueFilename("Sample-" & i & "-Detailed Sales High Value")
		task.AddExtraction dbName, "", "SALES_PLUS_TAX > 5000"
		task.PerformTask 1, db.Count
	Next i
	Set task = Nothing
	Set db = Nothing
End Function

 

hwes Wed, 04/02/2014 - 17:26

Thanks. I used your code exactly and changed the name of the file (into “Sample-2011 Detailed Sales.IMD”) and I changed a column (into SALES_PLUS_TAX), but it didn't work, sorry.
 
Let's try another one. The year is also in a folder, so is there a way to get it from the path of the working directory (like the code strFilePath I saw on this site)?
 
For example: working directory: C:\finance\srt\2012\financial_tables\
Goal is to extract  "2012" and use it later in the script.

Brian Element Wed, 04/02/2014 - 17:46

In reply to by hwes

You could use something like this.
Sub Main
	sYear = extractYear("C:\finance\srt\2012\financial_tables\")
	MsgBox sYear
End Sub

Function extractYear(sPath As String) As String
	Dim sLen As Integer
	Dim i As Integer
	Dim sChar As String
	
	sLen = Len(sPath)
	For i = sLen - 1 To 1 Step -1
		sChar = Mid(sPath, i, 1)
		If sChar = "\" Then
			extractYear = Mid(sPath, i - 4, 4)
			Exit Function
		End If
	Next i
	
End Function

 

hwes Fri, 04/04/2014 - 11:06

Thanks. It works for me!
I have another question. How can I code the next selection:
If sYear=2010 or 2011 or 2012 then look for the database "SRT & sYear " & "_financial_table_Sum.IMD").
If sYear=2013 then look for the database "SRT" & sYear & "_financial_table.IMD")
 
I tried some things like If and Else in a function but it didn't work.

Brian Element Fri, 04/04/2014 - 12:24

In reply to by hwes

How about trying the something with select case?
Sub Main

	Select Case iYear
		Case 2010
			sDatabase = "SRT_2010_financial_table.IMD"
		Case 2011
			sDatabase = "SRT_2011_financial_table.IMD"
		Case 2012
			sDatabase = "SRT_2012_financial_table.IMD"
		Case 2013
			sDatabase = "SRT_2013_financial_table.IMD"
	End Select
	
	Set db = Client.OpenDatabase(sDatabase)

End Sub