How to create a word cloud in Excel
Vložit
- čas přidán 8. 09. 2024
- This is the cleanest, least labor intensive way I've found to take data you have in excel and create a word cloud in powerpoint. It does require a little effort, and manual positioning of words, but this is the simplest way I've found.
*******Macro 1 (copy and paste between the sub/end sub)*********
For Each Word In Range("A1:A100")
Word.Font.Size = Word.Offset(, 1).Value
Word.Font.Color = Word.Offset(, 2).Value
Next Word
******Macro #2 (copy and paste between the sub/end sub)********
Dim rng As Range
Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim myShape As Object
'Copy Range from Excel
Set rng = ThisWorkbook.ActiveSheet.Range("A1:A100")
'Create an Instance of PowerPoint
On Error Resume Next
'Is PowerPoint already opened?
Set PowerPointApp = GetObject(class:="PowerPoint.Application")
'Clear the error between errors
Err.Clear
'If PowerPoint is not already open then open PowerPoint
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
'Handle if the PowerPoint Application is not found
If Err.Number = 429 Then
MsgBox "PowerPoint could not be found, aborting."
Exit Sub
End If
On Error GoTo 0
/ brianleedick
'Optimize Code
Application.ScreenUpdating = False
'Create a New Presentation
Set myPresentation = PowerPointApp.Presentations.Add
'Add a slide to the Presentation
Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly
'Copy Excel Range
rng.Copy
'Paste to PowerPoint and position
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
'Set position:
myShape.Left = 66
myShape.Top = 152
'Make PowerPoint Visible and Active
PowerPointApp.Visible = True
PowerPointApp.Activate
'Clear The Clipboard
Application.CutCopyMode = False
That was interesting and I'm glad to have the tool in my toolbox. It occurred to me that we could just make it in powerpoint directly. But your way is more fun.
Thank you! Anything else like this you're looking for a tutorial about? Please subscribe!
Thank you for sharing this!
No problem. Please consider subscribing!
Thank you so much. And i tried to do it with photoshop...
Glad this worked out for you... Took me a ton of trial and error, so I'm glad I kept others from having to deal with that
How do i find the notes with the code?
hope to have the site up again soon
great tutorial. thank you. Working with office 365 concatenate and change font did not work for me as shown on the video. I had to modify the function as follows: "=CONCATENATE(RANDBETWEEN(0;255); RANDBETWEEN(0;255);RANDBETWEEN(0;255))".
cool glad it helped
This is a very good follow up to your prior instructional video. If I have more than 100 words, would I just modify your formula here? For Each Word In Range("A1:A100")
Yup, you got it... Just fyi, but I'm guessing you see it, that you have to change the 100 in two places... Glad it's worked out for you! Let me know if there would be any other video tutorials like this you'd find helpful... And please subscribe!
@@BrianLeeDick I only got as far as the first macro. I receive the same error you did, "runtime error '1004': and when I click on "end" nothing else happens. I don't see any fonts popping up in a new window. As an FYI, I modified my rgb range from your (0,255) to (169,253). Would this be a problem or could it be something else?
@@jam29100 changing the rgb color shouldn't impact anything. It's just randomly picking a number over a smaller range which should be fine. Not sure why it wouldn't work otherwise. I wonder if you left everything as is and did the whole process like it is in the video and use the same macro that's in the show notes (so in other words you skip any words you have over 100 and keep the rgb range starting at 0) if you would have the same issue? Maybe if it works like that you slowly start adding in the changes to see which one triggers the failure. If it fails from square one maybe it's a bigger issue like Excel version differences or some default setting on your system that's blocking it from working
@@BrianLeeDick Thanks Brian. I was able to get a bit farther along. I appreciate the suggestions and feedback.
Hello, the video and the explanation was very helpful. Thanks for that. Unfortunately the following command does not work for me =concatenate(randbetween(0;255),",",randbetween(0;255),",",randbetween(0;255),",",randbetween(0;255)) ---> Do you have a hint what is wrong here. Thanks so much. Regards, Jan
you'd need double double quotes "" .. like that
and : not ;
The second Macro doesn't seem to be working for me. Any advice?
what result are you getting
I couldn’t follow this as it was too fast... even when you try to slow it down it’s still too fast to follow ☹️
sorry
Sorry, being stupid, found them
glad you found them.. actually it must have been right before the site went down, hope to have it back soon