How to assign the clipboard value to a variable?

fosterpapillon · #1 03-05-2017, 09:03 PM

I've written several large VB macros for Excel so I know how it works but I now need a simple one for MS Word (in Office 2011 for Mac) where I have no experience at all. Is there a good site to learn from?

In the meantime, this is what I want to do:

Public Sub Auto_Open()
' On opening the file, this procedure in TextConverter.doc opens the main procedure
' which opens one .doc file in the folder and saves it as a separate text file.

ConvertToText
End Sub

Private Sub ConvertToText()
Dim FileName1 As String
FileName1 = clipboard()
Documents.Open FileName:=FileName1, ConfirmConversions:=False, _
ReadOnly:=False
ActiveDocument.SaveAs FileName:= FileName1 & ".txt", FileFormat:=wdFormatText
ActiveWindow.Close
Quit
EndSub

It just opens a .doc file, saves it as a text file and closes the original. Obviously, the line, "FileName1 = clipboard()" throws an error. What is the correct code to assign the clipboard value to the variable, FileName1?

I need to convert hundreds of .doc files (in batches of 10 or 20) to text and I propose to call this macro for each one in a group, using Panorama, a database management package that can open a Word document. It's clumsy but I only need to do it once.

If there's a simple way for VB to handle a batch of files, great! I just have no idea how to get the file names into the system. In Excel, I had them in cells and stepped through them.

michael

gmaxey · #2 03-06-2017, 08:56 AM

The first part:

Code:

Sub CopyTextBoxToClipBoard(ByRef strIn As String)
Dim myCopy As DataObject
'Must have a reference to Microsoft Forms 2.0 Object Library enabled
Set myCopy = New DataObject
myCopy.SetText strIn
myCopy.PutInClipboard
End Sub
Function GetFromCB() As Variant
Dim myPaste As DataObject
Set myPaste = New DataObject
'Must have a reference to Microsoft Forms 2.0 Object Library enabled
myPaste.GetFromClipboard
GetFromCB = myPaste.GetText(1)
End Function
Sub Demo()
  MsgBox GetFromCB
End Sub

The second part. Yes. See: http://gregmaxey.com/word_tip_pages/...der_addin.html

fosterpapillon · #3 03-06-2017, 05:50 PM

Thanks Greg - the batch option looks the way to go so I'll try that.

Your first part solution includes the admonition, "Must have a reference to Microsoft Forms 2.0 Object Library enabled". Does the code do this or is it some juju that I have to invoke? If so, how?

michael

gmaxey · #4 03-06-2017, 06:25 PM

With the VB Editor open (Alt+F11) using the Tools>References menu, scroll through, find and add the reference to the project.

fosterpapillon · #5 03-06-2017, 07:02 PM

Thanks - I'll add that to the list (I'm tied up another project for the next few days).

Another site suggested this simple AppleScript code:

set sourceFolder to choose folder
tell application "Finder" to set theFiles to files of sourceFolder whose name extension is in {"doc", "docx"} or creator type is "MSWD"
repeat with oneFile in theFiles
do shell script "textutil -convert txt " & quoted form of POSIX path of (oneFile as text)
end repeat

Very elegant - if you're on a Mac.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Run-time error 91 object variable or with block variable not set	JUST ME	Word VBA	4	03-25-2014 06:56 AM
assign variable name to WORD text	norwood	Word VBA	2	09-13-2013 08:00 AM
object variable or with block variable not set	MJP143	Excel	1	02-11-2013 05:07 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM