Updating the VB Version (8967/8714)

[BobHunter]

I have a few users who experience problems with their spreadsheets that no one else does.

I checked through the VB editor (Help>About)what version of VB they are running with, and some are on version 8714, and some on 8967.

Is this updated via service packs or by some other method ?

Any help much appreciated.

Bob.

 

[SteveBausch]

Love to help here, perhaps I don't know what to ask, perhaps you haven't provided enough information.

Operating system? Are they all up to current update?

What about the Excel? The problems occur in Excel, is that correct?

What exactly is the behavior? Does the behavior follow a specific file, or does it stay with a computer?

 

[BobHunter]

The user gets a run time error -2147221036 (800401d4)
DataObjectutInClipboard CloseClipboard Failed

The code as below :

Sub copytext()
Dim d As DataObject, s As String
Set d = New DataObject
Workbooks.Add
ThisWorkbook.ActiveSheet.UsedRange.Copy ActiveSheet.Range("A1&quot
ActiveSheet.UsedRange.Copy
ActiveWorkbook.Close False
d.GetFromClipboard
s = d.GetText
d.SetText s
d.PutInClipboard
Set d = Nothing
End Sub


Excel 2000, Win 2K professional. The only difference between my PC and the problem PC is the VB version embedded in Excel.

?Stumped?

 

[SteveBausch]

Thanks, you did a good job of explaining the situation. Thank You.

I am now looking at your response, will see if a Google or MSN search returns anything on the error message.

Have you stepped through the code? Which line errors out?

When you know which line, repost the code, and bold the line which causes the error.

Steve

 

[BobHunter]

d.PutInClipboard

It's the above line it bombs out on..

I have already Googled this and one way round this is to put an error handler in. This is cheating. It must be something to do with the VB runtime version...

http://groups.google.com/groups?hl=...InClipboard+CloseClipboard+Failed&sa=N&tab=wg

 

[SteveBausch]

What exactly is happening here?

If you would, give me a step-by-step, what I would see while looking over the user's shoulder.

I don't understand the need for the DataObject. That's not an intrinsic object, it doesn't come in Object Browser (for me). It does support Drag-and-Drop operations. Are you doing Drag-and-Drop?

From MSDN

What is the difference between the DataObject and the Clipboard?


The DataObject and the Clipboard both provide a means to move data from one place to another. As an application developer, there are several important points to remember when you use either a DataObject or the Clipboard:

You can store more than one piece of data at a time on either a DataObject or the Clipboard as long as each piece of data has a different data format. If you store data with a format that is already in use, the new data is saved and the old data is discarded.


The Clipboard supports picture formats and text formats. A DataObject currently supports only text formats.


A DataObject exists only while your application is running; the Clipboard exists as long as the operating system is running. This means you can put data on the Clipboard and close an application without losing the data. The same is not true with the DataObject. If you close the application that put data on a DataObject, you lose the data.


A DataObject is a standard OLE object, while the Clipboard is not. This means the Clipboard can support standard move operations (copy, cut, and paste) but not drag-and-drop operations. You must use the DataObject if you want your application to support drag-and-drop operations.
Tip You can define your own data format names when you use the SetText method to move data to the Clipboard or a DataObject. This can help distinguish between text that your application moves and text that the user moves.
​

 

[BobHunter]

The idea behind this piece of code is to take data from cells and place into the clipboard WITHOUT formatting. For example, if you select a range of cells, and copy straight to the clipboard, then paste into Lotus Notes for example, it pastes the formatting of the cells also (patterns, font, etc). I do not want this to happen, therefore it gets placed in the DataObject which can only hold text, and then placed back into the clipboard, therefore no formatting. Just like the user doing a pastespecial text only, but without the need....

 

[SteveBausch]

I am definitely learning some new tricks here. Thanks for the education.

DataObject comes along for the ride when a form is inserted in the project.

While I am studying the code, I have a question.

How practical would it be to recompile the code while the project is residing on the troublesome machine?

I am thinking the VB version may not be as critical as the "MSForms 2.0" file date.

 

[BobHunter]

FM20.dll timestamp matches on both machines.

The handy piece of code above came from RobBroekhuis on this forum - credit where credit is due !

 

[BobHunter]

Away now for the weekend....