Jump to content

  •  

Photo

Open MS Word Document Using a VBScript


  • Please log in to reply
15 replies to this topic

#1 randlem  member

randlem
  • Members
  • 19 posts
  • FM Application:13
  • Platform:Windows 8.1
  • Skill Level:Novice
  • Time Online: 1d 6h 41m 37s

Posted 09 April 2014 - 05:56 AM

I have a vbscript which is executed by an export script that opens a word document in read only format then populates several bookmarks and finally saves it with a different name and different folder. This works as I would like but the document does not open in the foreground, just in the taskbar. My operating system is Windows 8.1

 

The following is my VBScript:

"Set objWord = CreateObject('Word.Application')¶
objWord.Visible = True¶
Set objDoc = objWord.Documents.Open( 'C:\Dernier & Hamlyn Sales Database\Templates\Standard Order Acknowledgement.doc',,False)¶
Set objRange = objDoc.Bookmarks('ContactName2').Range¶
objRange.Text = '" & Entries::QuoteContact & "'¶
Set objRange = objDoc.Bookmarks('ContactName').Range¶
objRange.Text = '" & Entries::QuoteContact &"' ¶
Set objRange = objDoc.Bookmarks('Company').Range¶
objRange.Text = '" & Entries::CUSTOMER & "'¶
Set objRange = objDoc.Bookmarks('InvoiceAddress').Range¶
objRange.Text = '" & Entries::QuoteAddress1 & "'¶
Set objRange = objDoc.Bookmarks('InvoiceAddress2').Range¶
objRange.Text = '" & Entries::AddressL2 & "'¶
Set objRange = objDoc.Bookmarks('InvoiceAddress3').Range¶
objRange.Text = '" & Entries::AddressL3 & "'¶
Set objRange = objDoc.Bookmarks('InvoiceAddress4').Range¶
objRange.Text = '"  & Entries::AddressL4 & "'¶
Set objRange = objDoc.Bookmarks('InvoiceAddress5').Range¶
objRange.Text = '"  & Entries::AddressL5 & "'¶
Set objRange = objDoc.Bookmarks('OrderNumber').Range¶
objRange.Text = '"  & " "& Entries::CLIENT_ORD & "'¶
Set objRange = objDoc.Bookmarks('OrderDate').Range¶
objRange.Text = '" & " " & Globals::gPO Date & "'¶
Set objRange = objDoc.Bookmarks('LeadTime').Range¶
objRange.Text = '" & " " & Globals::gLead Time & "'¶
Set objRange = objDoc.Bookmarks('Entry').Range¶
objRange.Text = '" & Entries::ENTRY_NO & "'¶
objDoc.SaveAs('" & $WordFilePath & "')¶
¶"
 
I have tried adding objWord.Activate to the end of the coding but it just makes the word tile in the taskbar flash.
 
Any help would be greatly appreciated.
 
Kind regards, Mark.

  • 0

#2 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,589 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 34d 11h 54m 44s

Posted 09 April 2014 - 06:38 AM

One option would be to leave the "visible" to false in your VBscript

and use a second "Send Event" script step to just open the Word document and toggle on the "bring target application to the front" in the Send Event script step

That 2nd Send Event would not use a VBscript but just the path to the Word document


  • 0

#3 randlem  member

randlem
  • Members
  • 19 posts
  • FM Application:13
  • Platform:Windows 8.1
  • Skill Level:Novice
  • Time Online: 1d 6h 41m 37s

Posted 09 April 2014 - 09:50 AM

Hi Wim,

 

Thanks for the advice.

 

I will let you know how I get on.

 

Regards, Mark.


  • 0

#4 randlem  member

randlem
  • Members
  • 19 posts
  • FM Application:13
  • Platform:Windows 8.1
  • Skill Level:Novice
  • Time Online: 1d 6h 41m 37s

Posted 10 April 2014 - 12:26 AM

Hi Wim,

 

I tried using Send Event & Open URL both seemed a bit flaky, Sometimes they opened and other times they opened for a second and then closed.

So it would appear that Send Event & Open URL script was running before the VBScript had finished and hence closing the document. So I ended up using the Open URL with a 3 sec pause script to delay the Open URL.

That seems to have done the trick.

 

Many thanks for pointing me in the right direction.

 

Regards, Mark.


  • 0

#5 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,589 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 34d 11h 54m 44s

Posted 10 April 2014 - 02:45 AM

That's a typical conundrum: hand-off between the VBscript and FM.  FM has an ActiveX interface itself too so you could make the last script in your FM script be launching the VBscript.  The VBscript can then run a script in FM when it is done, that script would then open the Word doc.

In this case it's probably easier solving it with an arbitrary pause like you did, but it's something to keep in mind for future integrations.


  • 0

#6 GlennS  newbie

GlennS
  • Members
  • 9 posts
  • LocationNabiac NSW Australia
  • FM Application:12 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 12h 32m 44s

Posted 10 July 2014 - 04:52 AM

You can also add some additional VBScript (to your original) that basically looks for your Word document window and restores or maximises it.

 

Let me know if you are interested in some code for this.

 

Regards

Singo


  • 0

#7 randlem  member

randlem
  • Members
  • 19 posts
  • FM Application:13
  • Platform:Windows 8.1
  • Skill Level:Novice
  • Time Online: 1d 6h 41m 37s

Posted 24 July 2014 - 03:49 AM

You can also add some additional VBScript (to your original) that basically looks for your Word document window and restores or maximises it.

 

Let me know if you are interested in some code for this.

 

Regards

Singo

Hi Singo,

 

Sorry for the delay in getting back to you.

 

I hadn't checked the forum as I was quite happy with the solution.

 

When the script is being run over the vpn I have to change the pause to 25s.

So any code the you can suggest to open the Word Doc in the foreground without having to close and re-open would be greatly appreciated.

 

Kind regards, Mark.


  • 0

#8 jlamprecht  member

jlamprecht
  • Members
  • 20 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Intermediate
  • Certification:12, 13
  • Membership:TechNet
  • Time Online: 9h 7m 42s

Posted 24 July 2014 - 05:46 AM

Set objDoc = objWord.Documents.Open( 'C:\Dernier & Hamlyn Sales Database\Templates\Standard Order Acknowledgement.doc',,False)

 

Try taking out that optional parameter False. It could be that you are setting the Visible attribute to false in that above statement. 

 

Hope this helps

-JohnAustin


  • 0

#9 randlem  member

randlem
  • Members
  • 19 posts
  • FM Application:13
  • Platform:Windows 8.1
  • Skill Level:Novice
  • Time Online: 1d 6h 41m 37s

Posted 24 July 2014 - 07:38 AM

Set objDoc = objWord.Documents.Open( 'C:\Dernier & Hamlyn Sales Database\Templates\Standard Order Acknowledgement.doc',,False)

 

Try taking out that optional parameter False. It could be that you are setting the Visible attribute to false in that above statement. 

 

Hope this helps

-JohnAustin

Hi John,

 

Thanks John for your response.

 

No difference, just sits in the Taskbar and flashes when the bookmarks are populated and the document is being saved with a different name.

 

I am wondering if there is a possibility that the problem could be the combination of Win 8.1 & Word 2010.

 

Regards, Mark.


  • 0

#10 jlamprecht  member

jlamprecht
  • Members
  • 20 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Intermediate
  • Certification:12, 13
  • Membership:TechNet
  • Time Online: 9h 7m 42s

Posted 24 July 2014 - 07:59 AM

Ah phooey. Ok can you show us the entire Export script that you use to call the VBscript? Could give us some clues.


  • 0

#11 randlem  member

randlem
  • Members
  • 19 posts
  • FM Application:13
  • Platform:Windows 8.1
  • Skill Level:Novice
  • Time Online: 1d 6h 41m 37s

Posted 24 July 2014 - 08:17 AM

Ah phooey. Ok can you show us the entire Export script that you use to call the VBscript? Could give us some clues.

 

Here Goes:

 

"Set objWord = CreateObject('Word.Application')¶
objWord.Application.Visible = True¶
Set objDoc = objWord.Documents.Open( '///192.168.21.1/Company\Dernier & Hamlyn Sales Documentation\Templates\quotation template.doc',,True)¶
Set objRange = objDoc.Bookmarks('QuoteDate').Range¶
objRange.Text = '" & Quotes::FormatDate & "'¶
Set objRange = objDoc.Bookmarks('QuoteRef').Range¶
objRange.Text = '" & $QuoteRef &"' ¶
Set objRange = objDoc.Bookmarks('QuotePhone').Range¶
objRange.Text = '" &" " & Quotes::QuotelPhone & "'¶
Set objRange = objDoc.Bookmarks('QuoteClient').Range¶
objRange.Text = '" &Quotes::Client & "'¶
Set objRange = objDoc.Bookmarks('QuoteAddress').Range¶
objRange.Text = '" & Quotes::QuoteAddress1 & "'¶
Set objRange = objDoc.Bookmarks('QuoteAddress2').Range¶
objRange.Text = '" & Quotes::AddressL2 & "'¶
Set objRange = objDoc.Bookmarks('QuoteAddress3').Range¶
objRange.Text = '"  & Quotes::AddressL3 & "'¶
Set objRange = objDoc.Bookmarks('QuoteAddress4').Range¶
objRange.Text = '"  & Quotes::AddressL4 & "'¶
Set objRange = objDoc.Bookmarks('QuoteAddress5').Range¶
objRange.Text = '"  & Quotes::AddressL5 & "'¶
Set objRange = objDoc.Bookmarks('QuoteName').Range¶
objRange.Text = '"  & Quotes::QuoteContact & "'¶
Set objRange = objDoc.Bookmarks('Text1').Range¶
objRange.Text = '"  &" " & Quotes::QuoteContact & "'¶
Set objRange = objDoc.Bookmarks('QuoteWeeks').Range¶
objRange.Text = '" & Quotes::Weeks & "'¶
Set objRange = objDoc.Bookmarks('SalesPerson').Range¶
objRange.Text = '" & $SP & "'¶
Set objRange = objDoc.Bookmarks('Position').Range¶
objRange.Text = '" & $Position & "'¶
objDoc.SaveAs('" & $WordFilePath & "')¶"
  
Regards, Mark

  • 0

#12 jlamprecht  member

jlamprecht
  • Members
  • 20 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Intermediate
  • Certification:12, 13
  • Membership:TechNet
  • Time Online: 9h 7m 42s

Posted 24 July 2014 - 08:19 AM

I have a vbscript which is executed by an export script

 

What I am asking for is that export script above. What does it contain?


  • 0

#13 randlem  member

randlem
  • Members
  • 19 posts
  • FM Application:13
  • Platform:Windows 8.1
  • Skill Level:Novice
  • Time Online: 1d 6h 41m 37s

Posted 24 July 2014 - 08:26 AM

I have a vbscript which is executed by an export script

 

What I am asking for is that export script above. What does it contain?

 

Is this what you are looking for:

 

If [ Quotes::Doc Num>0 ]

Show Custom Dialog [ Title: "Warning!"; Message: "Quote Document already created."; Default Button:   “OK”, Commit: “Yes” ]

Else

If [ Quotes::Sales Person ="JQ" ]

Set Variable [ $SP; Value:"Jeremy Quantrill" ]

Set Variable [ $Position ; Value:"Director" ]

Else If [ Quotes::Sales Person = "BS" ]

Set Variable [ $SP; Value:"Brian Spiking" ]

Set Variable [ $Position ; Value:"Director" ]

Else If [ Quotes::Sales Person = "MH" ]

Set Variable [ $SP; Value:"Mark Harper" ]

Set Variable [ $Position ; Value:"Director" ]

Else If [ Quotes::Sales Person = "NI" ]

Set Variable [ $SP; Value:"Natalie Ions" ]

Set Variable [ $Position ; Value:"Administrator" ]

End If

Set Variable [ $Date; Value:Get(CurrentDate) ]

Set Field [ Quotes::Doc Num ; 1 ]

Set Variable [ $QuoteRef ; Value:If (not IsEmpty ( Quotes::EB_NO ) ; Quotes::Sales Person & "/" & Quotes::EB_NO & "/Q" &

Quotes::Quote No & "-" & Quotes::Doc Num ; Quotes::Sales Person & "/Q" & Quotes::Quote No & "-" & Quotes::Doc

Num ) ]

#Define VBScript filename relative to current user's Documents folder (e.g. typically filewin:/C:

/Users/User/Documents/Generate_Word.VBS)

Set Variable [ $VBSFilePath; Value:"filewin:" & Get(DocumentsPath) & "Generate_Word.VBS"  ]

#Define Output Word filespec relative to current user's Documents folder (e.g. typically filewin:/C:/Users/User/Documents/Word

Sample.doc)

Set Variable [ $WordFilePath ; Value:"///192.168.21.1/Company\Dernier & Hamlyn Sales

Documentation\Quotations\Word/Quotation " & Quotes::Quote No & "-" & Quotes::Doc Num &".doc"    ]

Set Variable [ $PDFFilePath ; Value:"///192.168.21.1/Company/Dernier & Hamlyn Sales

Documentation/Quotations/PDF/Quotation " & Quotes::Quote No & "-" & Quotes::Doc Num &".pdf"     ]

#Trim first character (slash) from filename so it is acceptable to Windows

Set Variable [ $WordFilePath ; Value:Right ( $WordFilePath; Length($WordFilePath)-1 )  ]

Set Variable [ $PDFFilePath ; Value:Right ( $PDFFilePath; Length($PDFFilePath)-1 )  ]

#Define VBScript used to generate Word file. NOTE: For readability, single quotes are used

Set Field [ Quotes::VBscript ; "Set objWord = CreateObject('Word.Application')¶

objWord.Application.Visible = True¶

Set objDoc = objWord.Documents.Open( '///192.168.21.1/Company\Dernier & Hamlyn Sales

Documentation\Templates\quotation template.doc',,True)¶

Set objRange = objDoc.Bookmarks('QuoteDate').Range¶

objRange.Text = '" & Quotes::FormatDate & "'¶

Set objRange = objDoc.Bookmarks('QuoteRef').Range¶

objRange.Text = '" & $QuoteRef &"' ¶

Set objRange = objDoc.Bookmarks('QuotePhone').Range¶

objRange.Text = '" &" " & Quotes::QuotelPhone & "'¶

Set objRange = objDoc.Bookmarks('QuoteClient').Range¶

objRange.Text = '" &Quotes::Client & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress').Range¶

objRange.Text = '" & Quotes::QuoteAddress1 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress2').Range¶

objRange.Text = '" & Quotes::AddressL2 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress3').Range¶

objRange.Text = '" & Quotes::AddressL3 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress4').Range¶

objRange.Text = '" & Quotes::AddressL4 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress5').Range¶

Set objRange = objDoc.Bookmarks('QuoteAddress5').Range¶

objRange.Text = '" & Quotes::AddressL5 & "'¶

Set objRange = objDoc.Bookmarks('QuoteName').Range¶

objRange.Text = '" & Quotes::QuoteContact & "'¶

Set objRange = objDoc.Bookmarks('Text1').Range¶

objRange.Text = '" &" " & Quotes::QuoteContact & "'¶

Set objRange = objDoc.Bookmarks('QuoteWeeks').Range¶

objRange.Text = '" & Quotes::Weeks & "'¶

Set objRange = objDoc.Bookmarks('SalesPerson').Range¶

objRange.Text = '" & $SP & "'¶

Set objRange = objDoc.Bookmarks('Position').Range¶

objRange.Text = '" & $Position & "'¶

objDoc.SaveAs('" & $WordFilePath & "')¶" ]

#Replace all single quotes with double quotes so that script executes under Windows

Set Field [ Quotes::VBscript ; Substitute ( Quotes::VBscript; "'"; "\"" )  ]

#Save VBScript and open it (automatically open must be checked, to open it (execute it) following its generation)

Export Field Contents  [ Quotes::VBscript ; “$VBSFilePath”; Automatically open ]

Set Variable [ $QName ; Value:Quotes::Client & " - Quotation " & Quotes::Quote No & "-" & Quotes::Doc Num    ]

Set Variable [ $QNum ; Value:Quotes::Quote No ]

Set Variable [ $QClientID ; Value:Quotes::Client ID ]

Set Variable [ $QType ; Value:"MS Word" ]

Set Variable [ $QTypePDF ; Value:"PDF" ]

Set Variable [ $QProjectID ; Value:Quotes::ProjectID ]

Set Variable [ $WordFilePathToStore ; Value:"192.168.21.1\Company\Dernier & Hamlyn Sales

Documentation\Quotations\Word/Quotation " & Quotes::Quote No & "-" & Quotes::Doc Num &".doc"    ]

Set Variable [ $PDFFilePathToStore ; Value:"192.168.21.1\Company\Dernier & Hamlyn Sales

Documentation/Quotations/PDF/Quotation " & Quotes::Quote No & "-" & Quotes::Doc Num &".pdf"     ]

Go to Layout [ “L46 - Insert Quote Documents - Desktop” (Documentation)   ]

New Record/Request

Set Field [ Documentation::Document No ; $QNum ]

Set Field [ Documentation::ProjectID ; $QProjectID ]

Set Field [ Documentation::ClientID ; $QClientID ]

Set Field [ Documentation::DocumentName ; $QName ]

Set Field [ Documentation::DocumentType ; "Quotation" ]

Set Field [ Documentation::DocumentFormat ; $QType ]

Set Field [ Documentation::DocumentFileName ; $WordFilePathToStore ]

New Record/Request

Set Field [ Documentation::Document No ; $QNum ]

Set Field [ Documentation::ProjectID ; $QProjectID ]

Set Field [ Documentation::ClientID ; $QClientID ]

Set Field [ Documentation::DocumentType ; "Quotation" ]

Set Field [ Documentation::DocumentName ; $QName ]

Set Field [ Documentation::DocumentFormat ; $QTypePDF ]

Set Field [ Documentation::DocumentFileName ; $PDFFilePathToStore ]

Go to Layout [ original layout ]

Show/Hide Toolbars

[ Hide ]

Set Variable [ $Path2; Value:"file://192.168.21.1/Company\Dernier & Hamlyn Sales

Documentation\Quotations\Word/Quotation " & Quotes::Quote No & "-" & Quotes::Doc Num &".doc"    ]

Set Variable [ $file ; Value:"file:" & $WordFilePath ]

End If

 

Regards, Mark.


  • 0

#14 jlamprecht  member

jlamprecht
  • Members
  • 20 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Intermediate
  • Certification:12, 13
  • Membership:TechNet
  • Time Online: 9h 7m 42s

Posted 24 July 2014 - 08:33 AM

Yes, that's it. Thank you.

 

I have not used Export Field Contents to execute a VBScript before. I suggest rather export your VBScript to the file location and then run a Send Event using that location as such:

 

"wscript.exe "& $VBSFilePath


  • 0

#15 randlem  member

randlem
  • Members
  • 19 posts
  • FM Application:13
  • Platform:Windows 8.1
  • Skill Level:Novice
  • Time Online: 1d 6h 41m 37s

Posted 30 July 2014 - 01:31 AM

That's a typical conundrum: hand-off between the VBscript and FM.  FM has an ActiveX interface itself too so you could make the last script in your FM script be launching the VBscript.  The VBscript can then run a script in FM when it is done, that script would then open the Word doc.

In this case it's probably easier solving it with an arbitrary pause like you did, but it's something to keep in mind for future integrations.

 Hi Wim,

 

Could I trouble you for some assistance please ActiveX, Adding to my VBScript to run a Filemaker Script

 

Please see my script below:

 

#Define Sales Person's Full Name

#Define VBScript filename relative to current user's Documents folder (e.g. typically filewin:/C:/Users/User/Documents/Generate_Word.

VBS)

Set Variable [ $VBSFilePath; Value:"filewin:" & Get(DocumentsPath) & "Generate_Word.VBS"        ]

#Define Output Word filespec relative to current user's Documents folder (e.g. typically filewin:/C:/Users/User/Documents/Word

Sample.doc)

Set Variable [ $WordFilePath ; Value:"/S:\Dernier & Hamlyn Sales Documentation\Quotations/Quotation " & Quotes::Quote No & "-" &

Quotes::Doc Num &".doc" ]

Set Variable [ $PDFFilePath ; Value:"/S:/Dernier & Hamlyn Sales Documentation/Quotations/Quotation " & Quotes::Quote No & "-" &

Quotes::Doc Num &".pdf"   ]

#Trim first character (slash) from filename so it is acceptable to Windows

Set Variable [ $WordFilePath ; Value:Right ( $WordFilePath; Length($WordFilePath)-1 )  ]

Set Variable [ $PDFFilePath ; Value:Right ( $PDFFilePath; Length($PDFFilePath)-1 )        ]

#Define VBScript used to generate Word file. NOTE: For readability, single quotes are used

Set Field [ Quotes::VBscript ; "Set oFileObj = CreateObject(\"Scripting.FileSystemObject\")¶

Set WshShell = WScript.CreateObject(\"WScript.Shell\")¶

Set objWord = WScript.CreateObject (\"Word.Application\")¶

Set objDoc = objWord.Documents.Open( \"C:\Dernier & Hamlyn Sales Database\Templates\quotation template.doc\",,True)¶

objWord.Visible = True¶

Set objRange = objDoc.Bookmarks('QuoteDate').Range¶

objRange.Text = '" & Quotes::FormatDate & "'¶

Set objRange = objDoc.Bookmarks('QuoteRef').Range¶

objRange.Text = '" & $QuoteRef &"' 

Set objRange = objDoc.Bookmarks('QuotePhone').Range¶

objRange.Text = '" &" " & Quotes::QuotelPhone & "'¶

Set objRange = objDoc.Bookmarks('QuoteClient').Range¶

objRange.Text = '" &Quotes::Client & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress').Range¶

objRange.Text = '" & Quotes::QuoteAddress1 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress2').Range¶

objRange.Text = '" & Quotes::AddressL2 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress3').Range¶

objRange.Text = '" & Quotes::AddressL3 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress4').Range¶

objRange.Text = '" & Quotes::AddressL4 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress5').Range¶

objRange.Text = '" & Quotes::AddressL5 & "'¶

Set objRange = objDoc.Bookmarks('QuoteName').Range¶

objRange.Text = '" & Quotes::QuoteContact & "'¶

Set objRange = objDoc.Bookmarks('Text1').Range¶

objRange.Text = '" &" " & Quotes::QuoteContact & "'¶

Set objRange = objDoc.Bookmarks('QuoteWeeks').Range¶

objRange.Text = '" & Quotes::Weeks & "'¶

Set objRange = objDoc.Bookmarks('SalesPerson').Range¶

objRange.Text = '" & $SP & "'¶

Set objRange = objDoc.Bookmarks('Position').Range¶

objRange.Text = '" & $Position & "'¶

objDoc.SaveAs( \"C:\Dernier & Hamlyn Sales Database\Test.doc\")¶

objWord.Quit¶

Set FMProApp = GetObject('',\"FMPRO.Application\")¶

Dim FMProDoc¶

FMProDoc.DoFMScript (\"MyScript\")¶

Set FMProDoc = nothing¶" ]

#Replace all single quotes with double quotes so that script executes under Windows

Set Field [ Quotes::VBscript ; Substitute ( Quotes::VBscript; "'"; "\"" )          ]

#Save VBScript and open it (automatically open must be checked, to open it (execute it) following its generation)

Export Field Contents             [ Quotes::VBscript ; “$VBSFilePath”; Automatically open ]

 

The word document opens in the taskbar populates the bookmarks and saves the document with the required name. Then word closes.

Then the code to run the filemaker script (which is a send event to open the word document) I get a windows script host error:

 

Line: 38

Char: 2

Error: Object Required: ''

Code: 800A01A8

 

Any pointers would be greatly appreciated

 

Regards Mark.


  • 0

#16 randlem  member

randlem
  • Members
  • 19 posts
  • FM Application:13
  • Platform:Windows 8.1
  • Skill Level:Novice
  • Time Online: 1d 6h 41m 37s

Posted 30 July 2014 - 06:27 AM

 Hi Wim,

 

Could I trouble you for some assistance please ActiveX, Adding to my VBScript to run a Filemaker Script

 

Please see my script below:

 

#Define Sales Person's Full Name

#Define VBScript filename relative to current user's Documents folder (e.g. typically filewin:/C:/Users/User/Documents/Generate_Word.

VBS)

Set Variable [ $VBSFilePath; Value:"filewin:" & Get(DocumentsPath) & "Generate_Word.VBS"        ]

#Define Output Word filespec relative to current user's Documents folder (e.g. typically filewin:/C:/Users/User/Documents/Word

Sample.doc)

Set Variable [ $WordFilePath ; Value:"/S:\Dernier & Hamlyn Sales Documentation\Quotations/Quotation " & Quotes::Quote No & "-" &

Quotes::Doc Num &".doc" ]

Set Variable [ $PDFFilePath ; Value:"/S:/Dernier & Hamlyn Sales Documentation/Quotations/Quotation " & Quotes::Quote No & "-" &

Quotes::Doc Num &".pdf"   ]

#Trim first character (slash) from filename so it is acceptable to Windows

Set Variable [ $WordFilePath ; Value:Right ( $WordFilePath; Length($WordFilePath)-1 )  ]

Set Variable [ $PDFFilePath ; Value:Right ( $PDFFilePath; Length($PDFFilePath)-1 )        ]

#Define VBScript used to generate Word file. NOTE: For readability, single quotes are used

Set Field [ Quotes::VBscript ; "Set oFileObj = CreateObject(\"Scripting.FileSystemObject\")¶

Set WshShell = WScript.CreateObject(\"WScript.Shell\")¶

Set objWord = WScript.CreateObject (\"Word.Application\")¶

Set objDoc = objWord.Documents.Open( \"C:\Dernier & Hamlyn Sales Database\Templates\quotation template.doc\",,True)¶

objWord.Visible = True¶

Set objRange = objDoc.Bookmarks('QuoteDate').Range¶

objRange.Text = '" & Quotes::FormatDate & "'¶

Set objRange = objDoc.Bookmarks('QuoteRef').Range¶

objRange.Text = '" & $QuoteRef &"' 

Set objRange = objDoc.Bookmarks('QuotePhone').Range¶

objRange.Text = '" &" " & Quotes::QuotelPhone & "'¶

Set objRange = objDoc.Bookmarks('QuoteClient').Range¶

objRange.Text = '" &Quotes::Client & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress').Range¶

objRange.Text = '" & Quotes::QuoteAddress1 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress2').Range¶

objRange.Text = '" & Quotes::AddressL2 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress3').Range¶

objRange.Text = '" & Quotes::AddressL3 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress4').Range¶

objRange.Text = '" & Quotes::AddressL4 & "'¶

Set objRange = objDoc.Bookmarks('QuoteAddress5').Range¶

objRange.Text = '" & Quotes::AddressL5 & "'¶

Set objRange = objDoc.Bookmarks('QuoteName').Range¶

objRange.Text = '" & Quotes::QuoteContact & "'¶

Set objRange = objDoc.Bookmarks('Text1').Range¶

objRange.Text = '" &" " & Quotes::QuoteContact & "'¶

Set objRange = objDoc.Bookmarks('QuoteWeeks').Range¶

objRange.Text = '" & Quotes::Weeks & "'¶

Set objRange = objDoc.Bookmarks('SalesPerson').Range¶

objRange.Text = '" & $SP & "'¶

Set objRange = objDoc.Bookmarks('Position').Range¶

objRange.Text = '" & $Position & "'¶

objDoc.SaveAs( \"C:\Dernier & Hamlyn Sales Database\Test.doc\")¶

objWord.Quit¶

Set FMProApp = GetObject('',\"FMPRO.Application\")¶

Dim FMProDoc¶

FMProDoc.DoFMScript (\"MyScript\")¶

Set FMProDoc = nothing¶" ]

#Replace all single quotes with double quotes so that script executes under Windows

Set Field [ Quotes::VBscript ; Substitute ( Quotes::VBscript; "'"; "\"" )          ]

#Save VBScript and open it (automatically open must be checked, to open it (execute it) following its generation)

Export Field Contents             [ Quotes::VBscript ; “$VBSFilePath”; Automatically open ]

 

The word document opens in the taskbar populates the bookmarks and saves the document with the required name. Then word closes.

Then the code to run the filemaker script (which is a send event to open the word document) I get a windows script host error:

 

Line: 38

Char: 2

Error: Object Required: ''

Code: 800A01A8

 

Any pointers would be greatly appreciated

 

Regards Mark.

 Guys,

 

I managed to sort it:

 

Set objFM = WScript.CreateObject ('FMPRO.Application')¶

If objFM.ScriptStatus = 0 then¶

Set objFMfiles = objFM.Documents.Open(‘Database.fmp12','un','pw')¶

objFM.Visible = True¶

objFMfiles.DoFMScript ('OpenQuotationScript')¶

End If¶"

 

Although it is very slow in eventually opening about 30s, not sure if that is because I testing over a vpn.

 

Regards, Mark.


  • 0




FMForum Advertisers