Solved: Modified code to work in Microsoft Publisher

[Shazam]

This is a great code. When you run this code in Microsoft Word it pops up a input box then I type the file name in the input box and it will open the word document file. I would like to use this code for Microsoft Publisher. Can this code be modified for Microsoft publisher ?


[VBA]
Sub Test1()
Dim myFile$
myFile = InputBox _
("Enter the Word document file name:", _
"What File do you wish to open?", _
"YourFileName")
If myFile = "" Then Exit Sub

Dim myPath$, myDoc$
myPath = "C:\Your\File\Path\"
myDoc = myPath & myFile & ".doc"

If Dir(myDoc) = "" Then
MsgBox "The file ''" & myDoc & "'' was not found.", 48, "No such animal."
Exit Sub
End If

Application.ScreenUpdating = False
Dim appWord As Object
Dim wdDoc As Object
Set appWord = CreateObject("Word.Application")
appWord.Visible = True
Set wdDoc = appWord.Documents.Open(myDoc)
Set wdDoc = Nothing
Set appWord = Nothing
Application.ScreenUpdating = True
End Sub

[/VBA]

[chocobochick]

The trick is to get information about the application's object model. In the case of Microsoft Publisher, you can find it here.

So if you have code already within MS Publisher, you can presumably call for files using the Open method of the Application object, like so:

[VBA]
Application.Open(MyDoc)
[/VBA]

Like MS Word, you probably don't even need to create a new Application object variable just to run the Open method, since the application is already running to execute the macro in the first place. So I think the above line should do the trick, but I'm just guessing as I don't have MS Publisher to try it.

[TonyJollans]

Hi Shazam,

I'm confused! Word has a perfectly good Open Dialog. Why do you need a macro to do this from Word?

Anyway, do you want a Publisher macro which does the same - that is, from Publisher, create a new instance of Publisher and open your document in it? Or do you want to open Publisher from Word? Or do you want to open Word from Publisher?

[chocobochick]

Tony makes a valid point -- the macro doesn't do anything hitting CTRL-O couldn't do, and you can type the full path into the File Name textbox that appears just as you could in the InputBox. My guess is you're going for customizational flare.

Additionally, there's another trick you could use to open files: the ShellExecute API. Proper use of this command will actually open any file with its default program, just as if you had right-clicked the file from the My Computer window and selected Open. You could use it like the following:

[VBA]
Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub OpenAnyFile()

Dim strFilePath as String
On Error GoTo OpenAnyFileError
strFilePath = InputBox("File:", "Open File")
ShellExecute 0, "Open", strFilePath, "", "", 0
Exit Sub

OpenAnyFileError:
MsgBox "Unable to open file.", vbWarning, "Error"
End Sub
[/VBA]

I think that works, but I don't have time to test it out right now. Give it a whirl.

[Shazam]

Hi chocobochick,

I use your code and it gave me a compile error so I took out vbWarning out of the code. Now when I run the code in Microsoft Publisher the Input Box does pop up but when I enter the file name nonthing happens. Am I missing something?

Also thak you for replying TonyJollans,

And yes I would like Publisher macro which does the same thing.
The reason I dont do CTRL + O is because it has a default setting to my documents folder that I like. The Microsoft Publisher files are stored at my works network server Z:\ Drive.

How would I store the macro in the Microsoft Publisher? It seems like I create a module and put the macro in the module, but when I hit save, It ask me to save it as a file. Its a little different than word or excel.

[chocobochick]

Oops... my mistake. Not only was I trying to remember code off the top of my head, but my mind was stuck in "print" mode from another project. The last argument in ShellExecute needs to be 1, not 0. It was actually opening your file (employing a process viewable from Task Manager), but it wasn't actually displaying anything with it.

Here's the corrected code; I tested it myself this time:

[VBA]
Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub OpenAnyFile()

Dim strFilePath As String
On Error GoTo OpenAnyFileError
strFilePath = InputBox("File:", "Open File")
ShellExecute 0, "open", strFilePath, "", "", 1
Exit Sub

OpenAnyFileError:
MsgBox "Unable to open file.", vbExclamation, "Error"
End Sub
[/VBA]

Good luck with MS Publisher. I'm no more help there, I'm afraid.

[Shazam]

I tried your code but there is something weird. I ran the code and nonthing happens. I did at least 10 times and nonthing happen. In the input box I put Dog.pub and nonthing happened. So I went to my C:\Drive and open the Microsoft Publisher document that named "Dog" and open if there is something wrong with the file and everything looked OK. Then I ran the code again and it worked So I closed out publisher and reopen it and put the code in the module again and ran the code still nonthing happened So I'm thinking let me open the Publisher document again. So I open it and closed the document and then I ran the the code and it works. So the code only works if I open and close the Publisher document at least once. Why is that? Do you think I'm missing a reference? I do have the Microsoft Publisher 11.0 Oject Library check. Is there something I'm missing?

[TonyJollans]

Hi Shazam,

Firstly, your original Word macro shouldn't really be creating a new instance of Word; surely it's sufficient to open your document in the currently running one? Whatever your reasons, you don't have the option in Publisher.

You can change your original to:[vba]Sub Test1()
Dim myFile$
myFile = InputBox _
("Enter the Word document file name:", _
"What File do you wish to open?", _
"YourFileName")
If myFile = "" Then Exit Sub

Dim myPath$, myDoc$
myPath = "C:\Your\File\Path\"
myDoc = myPath & myFile & ".doc"

If Dir(myDoc) = "" Then
MsgBox "The file ''" & myDoc & "'' was not found.", 48, "No such animal."
Exit Sub
End If

Documents.Open myDoc

End Sub[/vba]

and the Publisher equivalent is:[vba]Sub Test1()
Dim myFile$
myFile = InputBox _
("Enter the Word document file name:", _
"What File do you wish to open?", _
"YourFileName")
If myFile = "" Then Exit Sub

Dim myPath$, myDoc$
myPath = "C:\Your\File\Path\"
myDoc = myPath & myFile & ".doc"

If Dir(myDoc) = "" Then
MsgBox "The file ''" & myDoc & "'' was not found.", 48, "No such animal."
Exit Sub
End If

Application.Open myDoc

End Sub[/vba]

However, I would consider just adding your 'special' locations to the Office Places bar - see this Microsoft Article for details.

[Shazam]

Thank You for replying back Tony,


I tried to use your code and nonthing happen.
Is there anything we could do?

[TonyJollans]

Do you mean the publication didn't open?

I just realised that I didn't change .doc to .pub in the Publisher code. I presume you did that - or you would have got a file not found.

Or are you saying nothing at all happens?

[Shazam]

Yes I did change it to .pub and nonthing happen. Meaning the input box did not pop up.

Any Suggestions?

[Shazam]

Ok So far I got the input box to pop up and when I enter the file name the hour glass is running but the Publisher document does not open. Do you know what I'm missing from this code?[VBA]

Sub Test1()
Dim pubApp As New Publisher.Application
Dim pubDocument As Publisher.Document

myFile = InputBox _
("Enter the Word document file name:", _
"What File do you wish to open?", _
"YourFileName")
If myFile = "" Then Exit Sub

pubDocument = pubApp = "C:\.pub"

If Dir(myDoc) = "" Then
MsgBox "The file ''" & myDoc & "'' was not found.", 48, "No such animal."
Exit Sub
End If

End Sub


[/VBA]

[TonyJollans]

What is this:[vba]
pubDocument = pubApp = "C:\.pub" [/vba]supposed to do?

[Shazam]

I'm not a vba coder I was researching and found that code. When I put that code below the code look like it was running but it does not open a file. Is this possible to open a publisher document by using a inputbox?


[VBA] pubDocument = pubApp = "C:\.pub" [/VBA]

[TonyJollans]

Yes it's possible and the code I first posted does it (subject to changing .doc to .pub). It's not good code, just a quick edit of what you had for Word, but it works.

If you didn't even get the input box (nor an error) then the code can't have been running. Where did you put it and how did you try to run it?

I would still recommend you edit your places bar to make for simple directory switching rather than go this VBA route.

[Shazam]

Ok I got it to work. The problem is the Visual Basice Editor is a little different to store macros. But anyhow I got this code to work.

[VBA]

Sub Test1()
Dim myFile$
myFile = InputBox _
("Enter the Word document file name:", _
"What File do you wish to open?", _
"YourFileName")
If myFile = "" Then Exit Sub

Dim myPath$, myDoc$
myPath = "C:\"
myDoc = myPath & myFile & ".Pub"

If Dir(myDoc) = "" Then
MsgBox "The file ''" & myDoc & "'' was not found.", 48, "No such animal."
Exit Sub
End If

Application.Open myDoc

End Sub

[/VBA]