ACCPAC Macro/VBA

[chinsf]

Hi all,

I'd like to learn ACCPAC Macro / VBA, any suggestions that where should I start off?

Regards,

Benny

 

[zemp]

If you have no programming experience then it may be a good idea to pick up a book on basic programming concepts and maybe Access VBA (it has good documentation and you can learn with it, ACCPAC has very poor documentation for it's VBA). You can also find free VBA (MS Office) tutorials on the web from sites such as

http://www.freeskills.com.

Otherwise, start recording VBA macros and tear them appart. You will see which views are invloved, how the views are composed (or related so that they can 'talk' to each other) and which fields or objects are needed to update something. You can record Macros for almost any module, except bank recs. There are also some existing marcos that come with
Accpac, tear them apart as well.

If you have access to the the Accpac website techinical pages for the COMAPI make sure you go through them and understand what is going on.

And of course when you are stuck visit this forum and we can see if we can't give you a hand.

Thanks and Good Luck!

zemp

 

[chinsf]

I'm using Accpac Advantage Series Corporate Edtion 5.1A.

I'm actually looking for codes that help me to change the AP Invoice Batch List's Year/Period values to current accounting month since the Year/Period values always follow the Document Date value.

I tried record some macros using the macro recorder and glance through the codes that was recorded. The codes only show step by step changes to the records, what I want is a loop to loop through all the Year/Period values to check and make sure the values are stick to current account month.

Any ideas for that?

Regards,

Benny

 

[zemp]

From the macro you know which views and fields are involved and you should see how to update the fields that need updating.

Now you need to use the view to fetch the records that you want to deal with. Loop through those records and make the appropriate changes. The basics are like this.

1. open the view.
2. Browse for specific or all records.
3. Fetch the records
Do while view.Fetch
'update records here...
loop
4. Close the view.

You will forgive me for not being exact with the syntax. I have never written an internal ACCPAC VBA macro with COMAPI. I always go extenally with the xAPI. Try and find some basic syntax (which can also be in the recorded macro's) and when or if you get post again.

Thanks and Good Luck!

zemp

 

[xenzat]

Hi there, I am trying to get a macro going in 5.1 where I export an IC table before the day end processing and then importing the IC table back afterwards. I used the macro recorder and got the process day end automated, but the macro recorder won't record the import and export process of my IC items... it does record the windows being opened and that's it! Any help would be fabulous...

 

[zemp]

Unfortunaltly the macro recorder does not work for all processes. For example it does not work for Bank services (Bank Recs) at all.

First of all, by 'import/export' I am assuming that you are tajing the data out of Accpac and then placing it back in. If this is the case it may be better to use the external xAPI. Very similar to the macro (internal) COMAPI. This will allow you to read an external file and reimport it into Accpac. You canstill use a macro to determine which IC views and fields need to be used and then you just need to organize your data with the correct syntax.

Thanks and Good Luck!

zemp

 

[chinsf]

The macros that recorded within ACCPAC is called COMAPI, but what is xAPI all about? Where can we learn it?

Regards,

Benny

 

[zemp]

The xAPI is almost the same as the COMAPI. COMAPI is used internally in macro's. The xAPI is used externally, usually from a VB application.

Accpac does not have any really good documentation on the xAPI,at least I have not been able to find any over the last few years (correct me if I am wrong), so you may have to use a lot of trial and error.

Check the Accpac website for technical documentation and use the SDK. You can record macros and just make the switch to xAPI using the same views and fields. You will need to create a session and make some slight syntax adjustments. You can search this forum for 'xAPI' and see some more detailed explanations and examples, including the of creating a session object.

The following threads can get your search started.
thread631-583563
thread631-558105
thread631-526541
thread631-578651

Thanks and Good Luck!

zemp

 

[xenzat]

Thanks for the reply... I got my project started in VB and referenced that xAPI... What I'd like to do is cycle through the records of my vendor table to pull out all the info and save that to my file. So far I have:

Private Sub Form_Load()

Dim Session As ACCPACXAPILib.xapiSession
Set Session = CreateObject("ACCPAC.xapisession&quot
Session.Open "ADMIN", "ADMIN", "SAMINC", Date, 0

Dim ARCUSTOMER As ACCPACXAPILib.xapiView
Set ARCUSTOMER = Session.OpenView("IC0310", "IC&quot

At this point, I'd like to do a

do while not(session.EOF)
and get all the values per records
and put then in a file (doesn't matter what file,
but I don't know how to pull data out of the
recordset!
loop

End Sub

Any help would be appreciated!
Thanks

 

[zemp]

First off, your view is being set incorrectly. ARCUSTOMER view needs to be set like this.

Set ARCUSTOMER = Session.OpenView("AR0024", "AR&quot

You have set it to an IC items view.

To open a view recordset you need to browse for the data first.

With ARCUSTOMER
.Init
.Order = 0
.Browse "", True 'all customers
Do While .Fetch Then
msgbox Trim(.Fields("IDCUST&quot.value) & ", " & Trim(.Fields("NAMECUST&quot.value)
loop
.Cancel
End With
Set ARCUSTOMER = Nothing

That is basically how you can pull the records from the recordset.


Thanks and Good Luck!

zemp

 

[xenzat]

Hello Zemp,
Thanks for that awesome reply... I can now grab data and store it in my file. Now I'm trying to return the values into the table and update the values from my file. I was wondering if I should use the BulkPut or Update function. The sub is almost the same:

Set Session = CreateObject("ACCPAC.xapisession&quot
Session.Open "ADMIN", "ADMIN", "SAMLTD", Date, 0

Dim ICVENDOR As ACCPACXAPILib.xapiView
Set ICVENDOR = Session.OpenView("IC0340", "IC&quot
Dim aICVENDOR as Array

'Open file

With ICVENDOR
.Init
.Order = 0
.Browse "", True

Do While .Fetch
'I'm reading from my file here....
'aICVENDOR = readfromfile()
'Here, should I use BLKPUT or UPDATE, and how
'could I use it...
.BlkPut aICVENDOR
.Update
Loop

.Post
End With
Set ICVENDOR = Nothing

What do you think... Any suggestions?

Thanks
Xenzat

 

[zemp]

I am not sure I understand what you are trying to do. Are you taking the updated values from your file and tryiong to update the existing records in Accpac? Or are you trying to add new records into the Accpac database?

I have always looped through or searched (browsed) for the records and done any updates or insertions individually.

Thanks and Good Luck!

zemp

 

[xenzat]

Hi Zemp, thanks for the quick response. I am taking updated values from my file and trying to update the existing records in Accpac... I reading out of an access DB and set the records up in the same order as the accpac table. Here's what I have so far...


Set ICVENDOR = Session.OpenView("IC0340", "IC&quot

myRS.MoveFirst
With ICVENDOR
.Init
.Order = 0

Do While Not (myRS.EOF)

.Browse "ITEMNO = '" & myRS("ITEMNO&quot & "'", True
If .Fetch Then 'update the record
For iCt = 0 To 10
.Fields(iCt).Value = myRS(iCt).Value
Next iCt
Else 'create a new record
For iCt = 0 To 10
.Fields(iCt).Value = myRS(iCt).Value
Next iCt
End If

myRS.MoveNext
.Update 'update Accpac with new info
Loop
.Post 'update accpac database
End With

myRS.Close
myDB.Close
set myRS = nothing
set myDB = nothing
set ICVENDOR = nothing

Not really sure about my syntax... I wish there was more literature abou this stuff... Thanks for looking at this...

 

[xenzat]

Hi Zemp,
Seems I got it working with a little perseverance... But I'd like to post the code nevertheless to get your opinion on it (I think there can be adjustments made to it...)
Here it goes:

Set ICVENDOR = Session.OpenView("IC0340", "IC&quot

myRS.MoveFirst
With ICVENDOR
.Init
.Order = 0

Do While Not myRS.EOF

.Browse "ITEMNO = " & myRS("ITEMNO&quot & "", True
If .Fetch Then 'update the record
For iCt = 0 To 10
If Not (iCt = 8 Or iCt = 9) Then
.Fields(iCt).Value = myRS(iCt).Value
End If
Next iCt

End If
myRS.MoveNext
.Update 'update Accpac with new info
Loop
.Post 'update accpac database
End With

I put a check for field 8 and 9 because Accpac lists these fields as not editable... But the rest seem to be editable!

let me know what you think, thanks
Xenzat

 

[zemp]

Looks good, remember to destroy the view objects when you are done with them to ensure that they are removed from memory. After your .post place a .cancel and the a right at the end place 'Set ICVENDOR = nothing'.

FYI, Accpac lists many fields as non-editable and you can still edit them. Their documentation for the fields is non necesarily for the xAPI. So you will find lots of examples of this. The other way around is more frustrating. When the xAPI tells you you can't edit a field, that needs to be edited, it can cause lots of headaches. The only way is to try them and see what errors you get.

Thanks and Good Luck!

zemp