GL Consolidation via VBA 1

[downwitchyobadself]

Hi,

Using macros recorded from the interface as a template, I am trying to automate the export (and, later, import to consolidated db) of GL consolidation data. This code appears to fire the processing half of the export, but not the export.

    glnkComp.OpenView "ZC0007", mvExport
    Set mfExportFields = mvExport.Fields

    mfExportFields("PROCESS").PutWithoutVerification ("BALANCE")
    mfExportFields("UNITID").PutWithoutVerification ("01")
    mfExportFields("EXPACCTS").PutWithoutVerification ("1")
    mfExportFields("EXPSRC").PutWithoutVerification ("1")
    mfExportFields("EXPSEGS").PutWithoutVerification ("1")
    mfExportFields("EXPTRANS").PutWithoutVerification ("1")
    mfExportFields("EXPACCTGRP").PutWithoutVerification ("1")
    mfExportFields("FISCYR").PutWithoutVerification ("2008")
    mfExportFields("FISCPERD").PutWithoutVerification ("06")

    mvExport.Process

Also, when I read the field values, it would appear that destination directory (EXPDIR) is not exposed, and cannot be written at runtime?

Thanks for any light you might shed.

 

[ettienne]

Look in the Accpac Shared Data folder in \COMPANY\ORGID\ZCEXPORT\YYYY-MM-DD

You will see the export files there.

 

[downwitchyobadself]

Thanks, the data is there. Is there no way to specify a directory at runtime?

 

[ettienne]

I don't know, but I did try. It seems to always go to the ZCEXPORT directory, they must be doing something in the UI to copy the files to another folder.

 

[dgillz]

I'd use F9.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[downwitchyobadself]

Yeah, I've actually found that using stored procs directly against underlying data is far faster and much more flexible than working with consolidation, and am building out reports accordingly, but need to give the users another option (without buying more software).

 

[downwitchyobadself]

Tangentially related question:

As part of this process, users are dumping and loading--copying from a template database to provide a clean copy into which they consolidate data. I was hoping to automate this step through the API (as opposed to dropping and restoring dbs on the server), but the .OpenView method for AS0025 fails with a "Method 'OpenView' of object 'IAccpacDBLink' failed" error. Is this Administrative Service operation locked down via macro for security purposes? (My code to open this view is no different from the dozens of other OpenView calls I make in my apps.)

Thanks for lending your expertise.

 

[ettienne]

You cannot load data into the same company that is opened.

 

[downwitchyobadself]

It's failing before it even gets to the process step though--on the .OpenView alone.

And I'm specifying a different company, using the ORGIDTARG (I think, that's from memory) field to copy.

 

[ettienne]

Show some code, and say where it fails when you step through it. Do you have an error handler, and what does it report?

 

[downwitchyobadself]

I would have posted the code sooner if it was anything less generic:

glnkComp.OpenView "AS0025", vCopy

My code is set to break on errors. The error message is as reported above: Method 'OpenView' of object 'IAccpacDBLink' failed

 

[Jay Converse]

AS0025 is a System view, not a Company view.

 

[downwitchyobadself]

The macro that Accpac generates uses the DBLINK_COMPANY AccpacDBLinkType Link to initialize the view. Restated in accordance with your post

glnkSys.OpenView "AS0025", vCopy

generates an "ActiveX component can't create object" error.

 

[ettienne]

This works for me

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Dim ASCOPY As AccpacCOMAPI.AccpacView

Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

mDBLinkCmpRW.OpenView "AS0025", ASCOPY

ASCOPY.Init
ASCOPY.Fields("ORGID").PutWithoutVerification ("SAMINC")
ASCOPY.Fields("DSETDIR").PutWithoutVerification ("C:\Dump")
ASCOPY.Fields("OPERATION").PutWithoutVerification ("1")
ASCOPY.Fields("DSET").PutWithoutVerification ("SAMINC.DCT")
ASCOPY.Process

 

[downwitchyobadself]

Are you running it from inside Accpac? That won't work for me.

Assuming you're not, can you post your session initialization code also?

Thanks again.

 

[ettienne]

That was a VBA macro run in Accpac.
Here is a VBS that should work, just copy to a text file and save as a .vbs file, then run it from Windows Explorer.
Note that I open SAMINC and then load into SAMLTD.

Dim Session
Set Session = CreateObject("ACCPAC.xapisession")
Session.Open "ADMIN", "ADMIN", "SAMINC", Date, 0

Dim dbCopy

Set dbCopy = Session.OpenView("AS0025", "AS")
dbCopy.Fields("ORGID").PutWithoutVerification ("SAMLTD")
dbCopy.Fields("DSETDIR").PutWithoutVerification ("C:\Dump")
dbCOPY.Fields("OPERATION").PutWithoutVerification ("1")
dbCOPY.Fields("DSET").PutWithoutVerification ("SAMLTD.DCT")
dbCopy.Process

Set dbCopy = Nothing
Set Session = Nothing

MsgBox "Database Load Complete"

 

[downwitchyobadself]

Thanks for that. So it appears that it works using an XAPI session, and fails using an AccpacCOMAPI session. Do you know why this would be? Is it because it's AS? I haven't had to use an XAPI session for anything else thus far.

 

[ettienne]

It should work using COM, I only used XAPI in the example because I had a dbdump VBS that uses XAPI. For something simple like this you do not need COMAPI.

 

[downwitchyobadself]

It doesn't work using COM. (That was the only difference between your code and mine. The first version you gave me was exactly what was not working from outside Accpac for me.) I'm guessing that xAPI somehow drops in lower and can administer?

FYI for anyone reading this thread down the line, be sure to explicitly .Close (views, sessions, etc.) and set = Nothing any object variables involved in both copying and exporting/importing, because leftover variables can cause crashes, as I've been learning the hard way.

Thanks again for all your help.