OLE import() with vbscript

olli · December 3, 2018, 12:10pm

Hi,
I do not see any imported data after running the following vbscript. Where is the mistake in the code?

    ' WIN 7 HomePremium SP1, ABx64 AmiBroker 6.30.0 RC November 18, 2018 18:13 
	' run: cscript /nologo 0_IMPORT_AQH2AB.vbs 
	' or  c:\windows\syswow64\cscript.exe 0_import_aqa2ab_temp.vbs
	' OLE import in vbs
	' full installtion on AB 6.3
	' run ABReg64
	' Run AmiQuote Ticker=AA start_date=16.11.2018 - automatic import unchecked
	' Database is given by AB
	' I read 
	'   AB Help / Reference Guide / AmiBroker's OLE Automation Object Model
	'   Return codes for ole calls
	'     0=ok, 4=no file, 2/3=bad data errors - see import.log for more info
	'   Using Import() of OLE object
	'   Import data through ole with example: www.amibroker.com/newsletter/02-2001.html
	'   Using Specific Format with Batch "Data Import ASCII"
	'   Running OLE automation scripts with 32- and 64-bit versions of AmiBroker 
	'        c:\windows\syswow64\cscript.exe 0_import_aqa2ab_temp.vbs
    '
	' Other infos
	' coding vbscript and datafile in ansi
	' 
	' in AB File / Import ASCII with aqh format works

	
    MYDATABASE = "C:\PROGRAM FILES\AMIBROKER\DATA"
	' call Broker as first object
	Set oAB = GetObject("","Broker.Application")
	' or Set oAB = CreateObject("Broker.Application")
	oAB.Visible = True
	if not oAB.LoadDatabase(MYDATABASE) Then
		MsgBox "Can't Load Database" + MYDATABASE
		errorlevel = 1
	end if
	WScript.Sleep 2000
	' doesn't work: aqfilename = "C:\\PROGRAM FILES\\AMIBROKER\\AMIQUOTE\\DOWNLOAD\\AA.AQH" 
	' doesn't work: aqfilename = "C:\PROGRAM FILES\AMIBROKER\AMIQUOTE\DOWNLOAD\AA.AQH" 
	aqfilename = "C:\PROGRAM FILES\AMIBROKER\AMIQUOTE\DOWNLOAD\AA" 
	aqformat = "aqh.format" 

	if oAB.Import(0,aqfilename,aqformat) then
		oAB.RefreshAll()
		WScript.Echo aqfilename & " imported"
		errorlevel = 0
	else
		MsgBox "can't import "+ aqfilename
		errorlevel = 1
	end if
	WScript.Sleep 2000
	oAB.SaveDatabase()
	oAB.Quit()
	' no import.log generated

' AA.aqh file
'$NAME AA
'# Date,Open,High,Low,Close,Adj Close,Volume
'19-11-2018,35.810001,35.994999,32.250000,33.490002,33.490002,4603100
'20-11-2018,32.419998,33.200001,31.980000,32.680000,32.680000,2938300
'21-11-2018,33.200001,33.389999,32.709999,32.799999,32.799999,1697200
'23-11-2018,32.049999,32.580002,31.629999,32.310001,32.310001,1346600
'26-11-2018,32.400002,32.860001,32.009998,32.570000,32.570000,2348200
'27-11-2018,32.139999,32.349998,30.950001,31.190001,31.190001,2964900
'28-11-2018,31.350000,34.169998,30.959999,33.599998,33.599998,5636700
'29-11-2018,33.450001,33.480000,30.979000,30.990000,30.990000,8105500
'30-11-2018,31.040001,32.459999,30.941999,31.809999,31.809999,5353800

' aqh.format file (as given)
' # AmiQuote historical quotes download format (.AQH extension)
'# Revision 1.4 - changed the order of columns (due to July 22, 2017 Yahoo site change)
'# Revision 1.3 - changed the order of columns (due to May 17, 2017 Yahoo site change)
'# Revision 1.1 - removed $VOLFACTOR 0.01
'# The format line below allows to get data adjusted for SPLITS
'# to get data adjusted for SPLITS AND DIVIDENDS replace 'Skip' by 'AdjClose'
'# 
'$FORMAT Date_DMY,Open,High,Low,Close,Skip,Volume
'$SKIPLINES 0
'$BREAKONERR 0
'$SEPARATOR ,
'$DEBUG 1
'$AUTOADD 1
'$CONT 1
'$GROUP 254
'# the following switches are optional - please consult
'# the read-me for description of those options
'# $RAWCLOSE2OI 1   
'# $RECALCSPLITS 1
'# $ROUNDADJ 4

travick · December 3, 2018, 1:18pm

Try the following
Dim oAB As Object
Add line before Set oAb = Getobject...
Make sure AB is already running.

Also, AB and VBA in my case are running as normal user, not UAC.
If either one is not the same, you probably wont see the change in same instance of AB.

While, deleting quotes for example, i run my Script as UAC and no AB instance is running.

Can you specify how you are running the code? Is it from Excel or any other office VBA or using CSCRIPT?

You can checkout the info on 32/64bit here
http://www.amibroker.com/kb/2015/01/12/ole-automation-scripts-with-32-and-64-bit/

File extension is required if the file has one

aqfilename = "C:\PROGRAM FILES\AMIBROKER\AMIQUOTE\DOWNLOAD\AA"
it's currently commented

Try running this bit of code on separate line like this

Call oAB.Import ( 0, aqfilename, aqformat)

and don't do any if checks for now.

I just compared your code to a working one I have and these seem to be the difference.

Also, try importing the file manually from Ascii importer with the same Format file, does it work?
You haven't mentioned whether you tried this.

olli · December 6, 2018, 9:37am

Thanks for the reply and the suggestions - nothing worked.
Importing the file from AB Ascii importer works very well.
I changed the UAC to the lowest level
I shortened the CSCRIPT and run it again from the commandline as administrator
c:\windows\syswow64\cscript.exe 0_IMPORT_AQH.vbs.
The code runs without error and commandline shows x=0 (import ok)

Again, what is wrong with my vbscript?

        ' This filename = c:\0_IMPORT_AQH.vbs
	' WIN 7 HomePremium SP1, ABx64 AmiBroker 6.30.0 RC November 18, 2018 18:13 

	' full fresh installtion on ABx64 6.3 only (no ABx32 on the computer)
	' run ABReg64 for registration
	' to deactivate UAC for Win 7 run C:\Windows\System32\UserAccountControlSettings.exe and to lowest value
	' run AB in administration mode and DOS-CMD-Window in administration mode
	' run AmiQuote Ticker=AA start_date=16.11.2018 automatic import unchecked
	' use AB - File / Import ASCII with aqh-format: it works well
	' delete the imported quotes and save database
        ' close AB
	' run c:\windows\syswow64\cscript.exe 0_IMPORT_AQH.vbs
	' I read 
	'   AB Help / Reference Guide / AmiBroker's OLE Automation Object Model
	'   Return codes for ole calls
	'     0=ok, 4=no file, 2/3=bad data errors - see import.log for more info
	'   Using Import() of OLE object
	'   Import data through ole with example: www.amibroker.com/newsletter/02-2001.html
	'   Using Specific Format with Batch "Data Import ASCII"
	'   Running OLE automation scripts with 32- and 64-bit versions of AmiBroker 
	
	' Other infos
	' coding vbscript and datafile in ansi


	' call Broker as first object
        Dim oAB
	Set oAB = CreateObject("Broker.Application")
	oAB.Visible = True
	MYDATABASE = "C:\PROGRAM FILES\AMIBROKER\DATA"
	oAB.LoadDatabase(MYDATABASE)
	WScript.Sleep 2000
	aqfilename = "C:\PROGRAM FILES\AMIBROKER\AMIQUOTE\DOWNLOAD\AA.AQH" 
	aqformat = "aqh.format" 
        x = oAB.Import(0,aqfilename,aqformat)
	WScript.Echo "x="+cstr(x)
	oAB.RefreshAll()
	WScript.Sleep 2000
	oAB.SaveDatabase()
	oAB.Quit()
	' no import.log generated

Tomasz · December 6, 2018, 11:55am

You probably need a full path to format file (in your your aqformat variable)

beppe · December 6, 2018, 3:16pm

@olli

try this variation (defining the aqh.format as a constant).

	aqfilename = "C:\PROGRAM FILES\AMIBROKER\AMIQUOTE\DOWNLOAD\AA.AQH" 
	const aqformat = "aqh.format" 
    x = oAB.Import(0, aqfilename, aqformat)

The returned "x" value is still zero (the documentation does not specify what are the return values) but this works on my PC (for my test I used the 6.10.0 32-bit of AB).

Seems to be something related to the function call that expects the format parameter as a "variant" (I'm not a VB expert so I may be wrong here).

travick · December 6, 2018, 6:11pm

Admin and other elevated stuff isn't required.

Try installing in a folder under c:
C:\Amibroker just to test. If possible, try 32 bit AB

Also try and document everything you try.
Run from cwd > cur working dir, get out of system32
These all are nor required bcos of COM but that's how one can troubleshoot

Add this line

If isNull( oAB) Then

To check if object is really set after SET line

Use the //E switch in CScript to specify engine explicitly

travick · December 7, 2018, 6:20am

Above path is for the 32 bit AB. Your 1st post mentions 64bit AB.

Path for 64bit AB===> C:\Windows\System32\CScript.exe

Can you clarify bitness of AB you're using ?

In CMD, are you in the current working Dir of file "0_IMPORT_AQH.vbs", otherwise add full path of that too.

olli · December 12, 2018, 1:03pm

Thank you - it didn't work.
I changed line 34 to:

aqformat = "C:\Program Files\AmiBroker\Formats\aqh.format"

olli · December 12, 2018, 1:04pm

Is my script working on any machine other than mine?

olli · December 12, 2018, 1:16pm

Thank you - that did it.
I changed to

const aqformat = "C:\Program Files\AmiBroker\Formats\aqh.format"

return values for oAB.import() please read: Return codes for ole calls in the support area

almetz · December 28, 2022, 10:17am

Hi beppe and Olli,

I ran across your issue yesterday, because I also tried to call the importer with dynamically assigned stringvalues . After searching arround for hours throughout various VBA-WebSites I found the following solution for my case:
When calling oAB.Import, you need to put Parenthesis around the String Variables:

x = oAB.Import(0, (aqfilename), (aqformat))

With Parenthesis the calls by reference with the string variables are treated like a call by value and you don't need to treat them as constants.
This works perfect in my case, so I can use aqfilename with variable Symbol names and paths.

Tomasz · December 28, 2022, 12:23pm

No, you don't need to put parentheses. String parameters should be passed by value.
You can use CStr() function in VBScript if you have doubt about the type of variable.