How do I run a function stored in MS Publisher from MS Access VBA?

[jasonp128]

I have recently started trying to fully automate a process in our office and
have hit a bit of a snag. I have a significant amount of code already
created in a publisher file that I would like to execute as part of a process
that I am creating in MS Access. Right now I am working with Office 2003.

I have not been able to find anything that allows me to access the publisher
VBA from within Access. I have the ability to essentially recreate my
publisher code in Access, but if possible I'd like to avoid the headache of
cleaning up a couple thousand lines of code just because I can't run a VBA
function stored in the Publisher file I already have open and am working with.

Any help would be GREATLY appreciated.

Thank you in advance for your time in reading this post,

Jason

 

[Ed Bennett]

I have recently started trying to fully automate a process in our office and
have hit a bit of a snag. I have a significant amount of code already
created in a publisher file that I would like to execute as part of a process
that I am creating in MS Access.

VBA is VBA. Unless you're interacting with Publisher's object model,
then you should just be able to copy and paste the code straight over
into your Access file.

Otherwise, you can add a reference to the Publisher object library, and
use code along the lines of:

Dim pApp As New Publisher.Application
Dim aDoc As Publisher.Document
Set aDoc = pApp.Open("\\path\to\your\file.pub")
aDoc.SubName

(provided that the Sub is not Private)

 

[jasonp128]

Ed,

Thank you very much. I am actually already using the publisher object
library to add a bunch of info to the document so this is exactly what I
needed. I knew there had to be something I was missing...

Thanks again,
Jason

 

[jasonp128]

Ed,

I'm sorry to belabor the point, but I am having a bit of trouble in
implimenting your solution and have a followup question. I already have a
reference to the publisher object library and am doing quite a bit of
tweaking of the pub file, but I still cannot access any of the subs or
functions that reside within the publisher file. When you mentioned adding a
reference to the publisher object library, did you mean having to create a
custom reference for the sub I'm trying to run, or just to the Publisher
object library as a whole? If I have to create a custom reference, do you
happen to have a reference I could look at as I have been scouring the
internet for a bit today trying to fill in the blanks.

To better understand the entire process, below is a snippet of my code that
I am working on.

---THIS IS THE ACCESS FUNCTION---
Function LoopThroughPages()
Dim appPub As New publisher.Application
Dim Pub As publisher.Document
Dim aa As String
Dim objAcc As Object
Dim ShapeType As Integer
Dim Location As String

'Function to open a different access DB where info I need to pull resides
Open_DB

'Get Publisher file. In the future the NL will be determined based on a
database query, but haven't gotten that far yet...
NL = "Standard"
Location = Dir("c:\Intouch\*" & NL & ".pub", vbNormal)
Location = "c:\Intouch\" & Location

Set Pub = appPub.Open(Location)

---A whole long amount of code that loops through the pages of the publisher
document to find and populate text boxes with queries from the open
database---

--Here is where I am trying to run a sub that is located in the publisher
file but I get the error "Method or Data Member not Found". I've tried
declaring the publisher code both as a public sub as well as just sub but to
no avail...

pub.RunProcess

Thank you again for your help,
Jason

 

[Ed Bennett]

I'm sorry to belabor the point, but I am having a bit of trouble in
implimenting your solution and have a followup question. I already have a
reference to the publisher object library and am doing quite a bit of
tweaking of the pub file, but I still cannot access any of the subs or
functions that reside within the publisher file. When you mentioned adding a
reference to the publisher object library, did you mean having to create a
custom reference for the sub I'm trying to run, or just to the Publisher
object library as a whole?

Just to the Publisher object library as a whole...

This isn't something I've tried to do; I presumed it would work because
Publisher's IntelliSense suggested it would.

Are the subs in the Publisher file declared as Private, Public, or plain
Subs?

 

[jasonp128]

I've actually tried declaring the subs as both plain and public. I even
tried swapping it to a function hoping maybe that would do the trick, but to
no avail.

When you say that Publisher's Intellisense suggested it would work, where
were you trying it? Were you in publisher or access?

 

[Ed Bennett]

I've actually tried declaring the subs as both plain and public. I even
tried swapping it to a function hoping maybe that would do the trick, but to
no avail.

When you say that Publisher's Intellisense suggested it would work, where
were you trying it? Were you in publisher or access?

I was in Publisher but in the Immediate pane. This isn't something I've
tried before and I was short on time so I made an assumption (apparently
a bad one).

Word has a Call() method on the Application object that achieves this;
Publisher lacks this method. It seems you will need to copy and paste
your code into your Access file, or into an external module.

 

[jasonp128]

Thank you for your reply. I appreciate you taking the extra time to look
into this and helping me.