How to display the content of the clipboard in a dialog box

[mayajuju]

Hello,

I'm trying to find the VBA code that will allow me to display the content of the clipboard in a dialog box through a simple msgbox command). Can someone help?

Thx in advance

 

[Joe Was]

The code for that is here:

http://www.mrexcel.com/forum/showthread.php?t=321833

 

[mayajuju]

Thanks Joe. This helps a lot.

Now I have another related question:
Depending on the nature of the content of the Clipboard, I need my VBA code to do different command.
For example:
if the content is staright text then it needs to do Action 1
if the content is a copy from an excel sheet then it needs to do Action 2, etc.

Is there a way in VBA to check the format of the clipboard content (txt, sheet, etc.) or maybe its origin so the VBA code performs different actions.

Thanks!

 

[Joe Was]

You are looking at the problem the wrong way. You need to check before the copy and branch the process accordingly. First start with a list of what you have and what you want to end up with then look for a way to make it happen. Decide if each thing you want to happen is to be done as a separate process or if you want the whole list of things to happen in a single navagational tool?

You may build into your application a dash-board of individual controls or a single control with a menu for all the options.

The clipboard is not the best way to get things done in most cases, direct range work is generally a better way.

If you want to copy a sheet then ask the user to select a new sheet name or a destination and have your code complete it. If you want to copy a selected range then ask the user to select a destination or automatically select the destination based on how the overall application works.

As for, working with the clipboard, you can test indirectly, you can insert a temp sheet, paste the clipboard contents and test for patterns or characteristics such as the cell count. And, then branch the next steps depending upon what you want to happen. Then do the house cleaning and delete the temp sheet!

 

[mayajuju]

hmm... I'm not sure I'm explaining what I have in mind correctly.

1) This VBA code will be part of a macro in Excel

2) Prior to running this macro, the user will have to select and copy a table with values in the clipboard.

3) this table can either come from a text file (.log) or from an xls file (or .csv), both options are providing the same information.

I was hoping i could find a way that my VBA code could recognize the origin of the table copied in the clipboard, either csv or xls but from what I understand in your response, the user will need to specify where its coming from.

Do I understand this correcty?

Thanks!

 

[Joe Was]

You will need to give the user a menu of choices, each helping the user do the task, forget the clipboard as this may not be the best for what the user and you want to do. Think about how you can limit the key-press or mouse-click count the user will need to do and definitly limit the amount of direct input is needed. If what is required for a choice is a folder and file name, then use a dialog like FileNameGet to get that file for the user rather than have the user type that file location and file name into Excel. The key is to automate the process as much as you can for the user. You do not want the user doing a manual import!

 

[mayajuju]

Thanks for pointing to GetFileName! That seems to be the best compromise between useability and my knowledge and what I'm able to achieve in a reasonable amount of time.

Initially, I was thinking of developping some kind of resident program in VB, that woul dmake some results appear in a tooltip once something is selected in notepad (or once the user hit a shortcut) but I have no idea how to do that (unless you can give me a few pointers?), this is how I came up with the idea of using the clipboard.