Client.OpenDatabase

23 posts / 0 new
Log in or register to post comments
Last post
Tue, 04/01/2014 - 11:52
#1
hwes
Offline
Joined: 03/31/2014 - 15:02
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.

Top
Tue, 04/01/2014 - 12:25
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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

Top
Wed, 04/02/2014 - 04:21
hwes
Offline
Joined: 03/31/2014 - 15:02

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

Top
Wed, 04/02/2014 - 07:12
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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

 

Top
Wed, 04/02/2014 - 17:26
hwes
Offline
Joined: 03/31/2014 - 15:02

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.

Top
Wed, 04/02/2014 - 17:47
(Reply to #5)
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Any chance you could send me the script, I am really interested why it is not working.

Top
Wed, 04/02/2014 - 17:25
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Would the location of the 2012 always be in the same position in the working directory?

Top
Wed, 04/02/2014 - 17:27
(Reply to #7)
hwes
Offline
Joined: 03/31/2014 - 15:02

Yes.

Top
Wed, 04/02/2014 - 17:46
(Reply to #8)
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
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

 

Top
Fri, 04/04/2014 - 11:06
hwes
Offline
Joined: 03/31/2014 - 15:02

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.

Top
Fri, 04/04/2014 - 12:24
(Reply to #10)
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
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

 

Top

Pages