How to Copy Format without using Clipboard?

[Ceeyee]

I want to copy the Format of a large range to another cell location using VBA.

I don't want to use .Copy and .Pastespecial because it will interupt with my PC front end copy and paste operations.

I found some code from the internet which does what I want but it's extremely slow as it uses loops to run through each cell.


Is there any way to do it without using the clipboard and looping through each cell to = the format? I am using Excel 2010.

Thanks.

 

[Ceeyee]

Win 7 64 bit, but Excel 2010 32bit.

 

[Jaafar Tribak]

Win 7 64 bit, but Excel 2010 32bit.

I am not entirely sure (Someone correct me if I am wrong) but maybe you will need to change the API declares to make them 64bit compatible.

Take a look at this link for the 64bit declares :http://www.jkp-ads.com/articles/apideclarations.asp

 

[Kenneth Hobson]

It does not restore the clipboard for me either. I use XPPro and 2010 all 32bit.

If speed is a problem with your old method, you need to turn off screen updating and calculation. I made some routines to make that easy. http://vbaexpress.com/kb/getarticle.php?kb_id=1035

 

[Jaafar Tribak]

Are the contents of the clipboard (Text only) preserved if the constante XML_SPREADSHEET = 49365 is replaced with XML_SPREADSHEET = 1 ?

 

[Ceeyee]

Better now!

It stores and restores texts copied from within cells.

But when I clicked a whole cell and clicked copy, then Test() will clear the cell from clipboard.

Also formats are not stored.

 

[Kenneth Hobson]

Yes, that saves the clipboard data to text format with:

Public Const XML_SPREADSHEET = 1

I would use a DataObject if a text string was all that we needed. http://www.cpearson.com/excel/Clipboard.aspx

A scatch file would be clunky way to do what you want. A vb.net EXE could store your data and retrieve it but then you would need the framework files.

 

[Jaafar Tribak]

Yes, that saves the clipboard data to text format with:

Public Const XML_SPREADSHEET = 1

I would use a DataObject if a text string was all that we needed. http://www.cpearson.com/excel/Clipboard.aspx

A scatch file would be clunky way to do what you want. A vb.net EXE could store your data and retrieve it but then you would need the framework files.

The DataObject woudn't preserve the initial text string if a a copy operation was performed afterwards.

 

[Kenneth Hobson]

Right, DataObject is fairly limited but for strings alone, it is fine. This is why I did not suggest the DataObject method earlier in the thread.

To see why just saving the text clipboard format won't suffice, download the files in the link, change the path to it in my code, and run this to see the formats.

You may not need to change drive and change dir parts but I added them anyway. If you copy Excel data cells and run this, you may have to run a 2nd time as I did not bother with a shell wait routine. Or just run cbdump.exe from a DOS window after the copy cells in Excel.

'http://www.mschaef.com/blog/tech/excel/what_is_in_your_clipboard.txt
Sub cbdump()
  Dim sCurDir As String, sCBdump As String, sTXTfile As String
  sCurDir = CurDir
  sCBdump = "X:\Clipboard\cbdump\"
  sTXTfile = "c:\temp\cbdump.txt"
  ChDrive Left(sCBdump, 1)
  ChDir sCBdump
  Shell "cmd /c " & sCBdump & "cbdump.exe > " & sTXTfile, vbNormalFocus
  Shell "Notepad " & sTXTfile, vbNormalFocus
  ChDrive Left(sCurDir, 1)
  ChDir sCurDir
  Kill sTXTfile
End Sub

For two cells copied with one having a red font, the formats were:

c009: "DataObject" (4 bytes)
000e: CF_ENHMETAFILE (0 bytes)
0003: CF_METAFILEPICT (16 bytes)
0002: CF_BITMAP (0 bytes)
c31e: "Biff12" (6083 bytes)
c2da: "Biff8" (15872 bytes)
c2be: "Biff5" (10752 bytes)
0004: CF_SYLK (1521 bytes)
0005: CF_DIF (121 bytes)
c31c: "XML Spreadsheet" (1098 bytes)
c0ba: "HTML Format" (2081 bytes)
000d: CF_UNICODETEXT (14 bytes)
0001: CF_TEXT (7 bytes)
c2d9: "Csv" (7 bytes)
c2e5: "Hyperlink" (199 bytes)
c14e: "Rich Text Format" (3210 bytes)
c00b: "Embed Source" (26624 bytes)
c004: "Native" (26624 bytes)
c003: "OwnerLink" (46 bytes)
c00e: "Object Descriptor" (152 bytes)
c00d: "Link Source" (230 bytes)
c00f: "Link Source Descriptor" (152 bytes)
c2d3: "Link" (61 bytes)
0081: CF_DSPTEXT (13 bytes)
c002: "ObjectLink" (82 bytes)
c013: "Ole Private Data" (728 bytes)
0010: CF_LOCALE (4 bytes)
0007: CF_OEMTEXT (7 bytes)
0008: CF_DIB (37280 bytes)
0011: CF_DIBV5 (37364 bytes)
170352 bytes

 

[Jaafar Tribak]

Hi Kenneth.

Thanks for the followup.

The DataObject won't preserve even string text if followed by a copy operation. (tried it didn't work)

As for the clipboard format dump , can you try replacing the XML_SPREADSHEET Const in my code with Const XML_SPREADSHEET= &HC31C (Taken from the dump text file) and see if you can preserve the Clipboard contents including the cell Formats.

Thanks.

 

[Kenneth Hobson]

No, that constant removes the clipboard content.