• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1518
  • Last Modified:

dataobject in excel 2010 vba

Hi! With some help from the experts I compiled the following macro.

Sub getfname()
Dim cltxt As dataobject
Set cltxt = New dataobject

Dim txt As String
txt = Right(ActiveDocument.Path, Len(ActiveDocument.Path) - InStrRev(ActiveDocument.Path, "\")) & " - " & Left(ActiveDocument.name, Len(ActiveDocument.name) - InStrRev(ActiveDocument.name, "."))
cltxt.SetText (txt)
cltxt.PutInClipboard
End Sub

It works fine in Excel 2007, but in 2010 I get the error User-type defined not defined

Can anyone help?
0
etech0
Asked:
etech0
  • 6
  • 5
  • 2
  • +1
3 Solutions
 
redmondbCommented:
Hi, etech0.

DataObject needs a reference to "Microsoft Forms 2.0 Object Library". Let me know if you need any more on that.

Regards,
Brian.
0
 
Patrick MatthewsCommented:
The Excel library does not have a type of DataObject.

Your earlier effort probably used a reference to an external library.  Check your current references, and see if any are listed as missing.
0
 
Patrick MatthewsCommented:
Slow fingers :)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
etech0Author Commented:
Do you know which reference it is?
Alternatively, maybe you know how to pass a variable to the clipboard in vba...
0
 
redmondbCommented:
Nice to be (slightly) ahead of you, for once!
0
 
regmigrantCommented:
tools, references, Microsoft forms 2.0 Object Library  - at least on my machine
0
 
etech0Author Commented:
Thanks to both of you.
However, I don't have "Microsoft Forms 2.0 Object Library" in the list of references!
help!!!
0
 
redmondbCommented:
etech0,

(1) Please see attached...

(2) "pass a variable to the clipboard in vba" - do you mean more than replacing 3.1415926 by a variable name?

Thanks,
Brian.
0
 
etech0Author Commented:
@redmondb: Huh?
as you may have noticed in my code, I have a variable called txt which stores the file name and location in the format I require.
I would like to "copy" the data in this variable to the clipboard, so that I can paste it in the location of my choice.
0
 
redmondbCommented:
Oops.

(See 4th item).

 Forms Reference
0
 
etech0Author Commented:
I see, but I don't have that item in my list!
0
 
redmondbCommented:
etech0,

Apologies, there's another DataObjects question open at the moment and I cross-posted.

The Forms reference is in the list, but the sequence in that dialogue can be peculiar.

Regards,
Brian.
0
 
regmigrantCommented:
try clicking browse, navigate to c:\windows\system32 - you are looking for FM20.dll - if its there and it lets you add it then ok!

if (more likely) its not there or doesnt let you add it -
run diagnostics and repair installation
or you may have to resinstall Excel

- unless someone else has a better idea?
0
 
etech0Author Commented:
Thanks all for your help!
"try clicking browse, navigate to c:\windows\system32 - you are looking for FM20.dll" did the trick!
0
 
redmondbCommented:
Thanks, etech0. Good thinking regmigrant.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now