Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-22-2025, 08:34 AM
MJH001 MJH001 is offline Find the first chord in a song Windows 11 Find the first chord in a song Office 2019
Novice
Find the first chord in a song
 
Join Date: Sep 2025
Posts: 6
MJH001 is on a distinguished road
Default Find the first chord in a song

I'm writing a macro which extracts songs from a master songbook and puts them a new document. I've got it all working (thanks to macropod), but I need an enhancement.

I want to find the first instance of "[x]" in red (where [x] is the musical key of the song eg [Am], [C] etc) within the range of the song I have just set.

I want to copy this (including the brackets) into a variable (SongKey) for later pasting into a table also with the song title.

Here is the section of code which extracts the song and it's title. I've marked the sport where I need to copy the [x], but I have no idea how to select text by colour.
Also the string may be 3 OR 4 characters long, including brackets, but always ends with a "]".

Any help appreciated. Apologies I do not yet know how to make pasted code look neat.

' GET WHOLE SONG (always only one page)
Windows(LatestSongbook).Activate
Set rng = ActiveDocument.GoTo(What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=NextPage) ' marks START of page range


Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=NextPage ' marks END of page range (ie 1 page only)
rng.End = Selection.Bookmarks("\Page").Range.End ' marks physical end of page
rng.Copy

' HERE: need to find first instance of "[x]" in red (where [x] is the musical key of the song, eg [C], [Am] etc)
' need to copy this text, including start and end bracket into a variable SongKey for later insertion into the set list table

Set rng = rng.Paragraphs(1).Range ' gets song title (para 1) for later insertion into set list table
SongTitle = rng.Text
SongTitle = Left(SongTitle, Len(SongTitle) - 1) ' trim style separator off end of title

PS: the Red is RGB(255,0,0)

Last edited by MJH001; 09-22-2025 at 08:46 AM. Reason: Added a PS to clarify colour
Reply With Quote
  #2  
Old 09-24-2025, 12:30 AM
macropod's Avatar
macropod macropod is offline Find the first chord in a song Windows 10 Find the first chord in a song Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

It would be helpful if you attached a sample document to your post showing exactly what you're dealing with.

Perhaps you could also explain what Windows(LatestSongbook).Activate is for, too. Its presence in your code suggests you're switching back & forth between documents, which shouldn't be necessary and really slows down your code.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-26-2025, 03:26 AM
MJH001 MJH001 is offline Find the first chord in a song Windows 11 Find the first chord in a song Office 2019
Novice
Find the first chord in a song
 
Join Date: Sep 2025
Posts: 6
MJH001 is on a distinguished road
Default

Attached is a link to a Word doc sample of the Songbook from which I am extracting whole songs to go into a new doc "GigName". The list of page numbers is in a workbook named "SetListCreator". Whilst extracting the songs, I'm also extracting the title to paste next to the page number.

The reason for activating the sheets is that I don't know any other way. I assume I have to Activate the SetListCreator.doc to get the next page number, Activate the LatestSongbook to copy the relevant page, Activate the GigName doc to paste the song in, and Activate the SetListCreator again to paste the song title next to the pabe number.

https://www.msofficeforums.com/attac...1&d=1758882020

The additional extraction is the song key, so on the case of the attached sample the red [G] from page 1, [Am] from p2, [G] from p3, and [F] from p4. Note that on p4 I would not want the [F / / /]. The song key is always 1 or 2 A-Z characters inside square brackets [ ].

Your assistance is greatly appreciated.
Attached Files
File Type: docx Songbook Sample.docx (131.7 KB, 3 views)
Reply With Quote
  #4  
Old 09-26-2025, 03:05 PM
macropod's Avatar
macropod macropod is offline Find the first chord in a song Windows 10 Find the first chord in a song Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Now I'm confused. You keep referring to worksheets - which are Excel properties - and a workbook named "SetListCreator", but then you seem to refer to it as SetListCreator.doc - which implies a Word document.

Perhaps you would be so good as to explain precisely what you're doing and post the code too.

For a macro to Find content in one document and insert it into another document without all the window switching, see: https://www.msofficeforums.com/128621-post2.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 09-27-2025, 01:34 AM
MJH001 MJH001 is offline Find the first chord in a song Windows 11 Find the first chord in a song Office 2019
Novice
Find the first chord in a song
 
Join Date: Sep 2025
Posts: 6
MJH001 is on a distinguished road
Default

Sorry to cause confusion. It's all in word. I got myself muddled as the original SetListCreater was a spreadsheet, but I'm switching to do it all in Word.

Here's the code link: (I hope it works - it says it's attached in the little paper clip on the ribbon)

GigBook Macro1.docx

Meanwhile, I'll take a look at the link you sent about accessing different docs without Activating them every time.

Thanks again.
Reply With Quote
  #6  
Old 09-28-2025, 08:13 PM
macropod's Avatar
macropod macropod is offline Find the first chord in a song Windows 10 Find the first chord in a song Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try the code below. I've tidied up your code somewhat, reducing it to just 84 lines instead of close to 200, plus it's now way more efficient.

Some points to note:
1. Your hard-coded LatestSongbook has been replaced by a FilePicker dialog
2. Nothing gets selected or copied & pasted and no window switching is done and you'll see no window flickering, etc.
3. I wasn't sure where you want the musical key to go, so I've output it to an assumed column 4 of the table. You might want to change that.

Code:
Sub Demo()
Application.ScreenUpdating = False
Dim GigNum As String, GigName As String, GigDate As String, GigLoc As String, GigPost As String, GigTime As String, GigFile As String
Dim DocSrc As Document, DocRef As Document, DocTgt As Document, Rng As Range, r As Long, p As Long, Fld As Field, StrKey as String
With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
  .Title = "Select the Song Book": .AllowMultiSelect = False
  .Filters.Clear: .Filters.Add "Documents", "*.doc; *.docx; *.docm", 1
  .InitialFileName = "D:\Documents\MJH-20\MJH-Music\LotSU\LotSU Songbooks\"
  If .Show = -1 Then
    Set DocRef = Documents.Open(.SelectedItems(1), ReadOnly:=True, Visible:=False, AddToRecentFiles:=False)
  Else
    MsgBox "No Song Book selected. Exiting", vbExclamation: Exit Sub
  End If
End With
Set DocSrc = Documents.Open(FileName:="C:\Users\" & Environ("Username") & "\Desktop\SetListCreator.docx", ReadOnly:=True, Visible:=False, AddToRecentFiles:=False)
Set DocTgt = Documents.Add
With DocSrc
  With .Tables(1)
    GigNum = Split(.Cell(1, 1).Range.Text, vbCr)(0)
    GigName = Split(.Cell(1, 2).Range.Text, vbCr)(0)
    GigDate = Split(.Cell(1, 3).Range.Text, vbCr)(0)
    GigPost = Split(.Cell(2, 1).Range.Text, vbCr)(0)
    GigLoc = Split(.Cell(2, 2).Range.Text, vbCr)(0)
    GigTime = Split(.Cell(2, 3).Range.Text, vbCr)(0)
    GigFile = Mid(GigDate, 4) & Mid(GigDate, 4, 2) & Left(GigDate, 2) & GigName & " Songs"
  End With
  With DocTgt
    With .PageSetup
      .Orientation = wdOrientPortrait
      .TopMargin = CentimetersToPoints(0.5)
      .BottomMargin = CentimetersToPoints(0.5)
      .LeftMargin = CentimetersToPoints(1)
      .RightMargin = CentimetersToPoints(0.5)
      .Gutter = CentimetersToPoints(0)
      .HeaderDistance = CentimetersToPoints(0.5)
      .FooterDistance = CentimetersToPoints(0.5)
      .PageWidth = CentimetersToPoints(21)
      .PageHeight = CentimetersToPoints(29.7)
      .FirstPageTray = wdPrinterDefaultBin
      .OtherPagesTray = wdPrinterDefaultBin
      .SectionStart = wdSectionNewPage
      .VerticalAlignment = wdAlignVerticalTop
      .BookFoldPrintingSheets = 1
      .GutterPos = wdGutterPosLeft
    End With
    With .Range
      .ParagraphFormat.Alignment = wdAlignParagraphCenter
      .Text = vbCr & vbCr & vbCr & GigNum
      .Paragraphs.Last.Range.Font.Size = 24
      .InsertAfter vbCr & vbCr & GigName & vbCr & vbCr & GigLoc & vbCr & vbCr & GigPost & vbCr & vbCr & GigDate & vbCr & vbCr & GigTime & Chr(12)
      .Paragraphs(7).Range.Font.Size = 36
    End With
  End With
  With .Tables(1)
    For r = 3 To .Rows.Count
      If Len(.Cell(r, 1).Range.Text) = 2 then Exit For
      p = Split(.Cell(r, 1).Range.Text, vbCr)(0) + 6: StrKey = ""
      Set Rng = DocRef.GoTo(What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=p).GoTo(What:=wdGoToBookmark, Name:="\page")
      DocTgt.Range.Characters.Last.FormattedText = Rng.FormattedText
      .Cell(r, 1).Range.Text = r - 2: .Cell(r, 3).Range.Text = Split(Rng.Paragraphs.First.Range.Text, vbCr)(0)
      With Rng
        With .Find
          .ClearFormatting
          .Replacement.ClearFormatting
          .Font.Color = RGB(255, 0, 0)
          .Execute FindText:="\[[A-G]*\]", MatchWildcards:=True, Wrap:=wdFindStop, Forward:=True, Format:=True, ReplaceWith:=""
        End With
        If .Find.Found = True Then StrKey = .Text
      End With
      .Cell(r, 4).Range.Text = StrKey
    Next
  End With
  With DocTgt
    For Each Fld In .Fields
      If Fld.Type = wdFieldTOCEntry Then
        Fld.Code.Paragraphs(1).Range.Text = vbNullString
      End If
    Next
    .SaveAs2 FileName:="D:\Documents\MJH-20\MJH-Music\LotSU\Gig Sheets\" & GigFile & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
    .SaveAs2 FileName:="D:\Documents\MJH-20\MJH-Music\LotSU\Gig Sheets\" & GigFile & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
    .Close False
  End With
  .SaveAs2 FileName:="D:\Documents\MJH-20\MJH-Music\LotSU\Gig Sheets\" & GigFile & "Set.pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
  .Close False
End With
DocRef.Close False: Set Rng = Nothing: Set DocSrc = Nothing: Set DocRef = Nothing: Set DocTgt = Nothing
Application.ScreenUpdating = True
End Sub
PS: I really don't support the brute-force approach to formatting you've taken. It would be far better to design a suitable template for your GigFiles, with Styles that generate the appropriate fonts & paragraph spacing.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 09-29-2025, 03:23 AM
MJH001 MJH001 is offline Find the first chord in a song Windows 11 Find the first chord in a song Office 2019
Novice
Find the first chord in a song
 
Join Date: Sep 2025
Posts: 6
MJH001 is on a distinguished road
Default

Thank you so much. This is fantastic and I shall try to learn from this by studying and trying to understand what you've written.

Most of my limited macro coding has been done by recording keystrokesand then tweaking. I guess this makes for inefficient code.

I've tried to run the code, but get a compile error. This was in this section near the end:

For Each Fld In .Fields
If Fld.Type = wdFieldTOCEntry Then
Fld.Paragraphs(1).Range.Text = vbNullString
End If
Next

The word 'Paragraphs' was highlighted with a 'Method or data member not found' message.

Thanks again,

Jeremy
Reply With Quote
  #8  
Old 09-29-2025, 03:11 PM
macropod's Avatar
macropod macropod is offline Find the first chord in a song Windows 10 Find the first chord in a song Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Oops! That should have been:
Fld.Code.Paragraphs(1).Range.Text = vbNullString
Fixed. Try it now.

PS: I've added a few more tweaks to the code.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 10-06-2025, 03:53 AM
MJH001 MJH001 is offline Find the first chord in a song Windows 11 Find the first chord in a song Office 2019
Novice
Find the first chord in a song
 
Join Date: Sep 2025
Posts: 6
MJH001 is on a distinguished road
Default

Thanks again, Paul. I've gotten myself all tied up on page numbering.

In this bit of code:

Code:
p = Split(.Cell(r, 1).Range.Text, vbCr)(0) + 6: StrKey = ""
      If p = 6 Then Exit For
      Set Rng = DocRef.GoTo(What:=wdGoToPage, Name:=p).GoTo(What:=wdGoToBookmark, Name:="\page")
      DocTgt.Range.Characters.Last.FormattedText = Rng.FormattedText
... adding 6 gets a song 6 further on. This is because of "Name:=p" gets the page with that number in the Header. Adding 6 on was for when I was using "Count:=p". If I remove the "+6" it gets the correctly numbered page, unless the p is 1, 2, 3 or 4, in which case it returns the first four physical pages, which do not have page numbers in their header (being the cover, 2 index pages and a notes page. The first song (page 1) is on the 5th physical page of the document.

If instead I use "Count:=p", I do need to add 6 to get the correct song. I found it was 6 through trial and error, as the first song is on the 5th physical page so adding 6 should get page 7, but it gets the 5th.

This works apart from the first song. Whatever p number is first in the list, the command returns the whole of the source document from that song onwards. So if I have 1, 2, 3 in my list, I get songs 1-138, then song 2, then song 3.

In summary, if I use "Name:=p" (without the +6) I can't retrieve the first 4 songs by number.
If I use "Count:=p" (with +6) I get the rest of the song book from that first listed page on, followed by the correct songs.

Incidentally, the end of list detection throws up an error - presumably because we can't use Split on an empty cell? I got round this be testing for len=2 as the next empty table cell will just contain Cr & Lf.

Your continued help is greatly appreciated.

Jeremy

Last edited by MJH001; 10-06-2025 at 03:55 AM. Reason: typo
Reply With Quote
  #10  
Old 10-06-2025, 02:06 PM
macropod's Avatar
macropod macropod is offline Find the first chord in a song Windows 10 Find the first chord in a song Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

In that case, you could probably use something like:
Code:
      If Len(.Cell(r, 1).Range.Text) = 2 then Exit For
      p = Split(.Cell(r, 1).Range.Text, vbCr)(0) + 6: StrKey = ""
      Set Rng = DocRef.GoTo(What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=p).GoTo(What:=wdGoToBookmark, Name:="\page")
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 10-08-2025, 01:37 AM
MJH001 MJH001 is offline Find the first chord in a song Windows 11 Find the first chord in a song Office 2019
Novice
Find the first chord in a song
 
Join Date: Sep 2025
Posts: 6
MJH001 is on a distinguished road
Default

Thanks Paul - that did the trick.
Reply With Quote
Reply

Tags
color font, find character

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting song lyrics Lakshman Word 2 08-17-2021 07:17 AM
Find the first chord in a song Song in Powerpoint aclare PowerPoint 5 04-09-2017 01:48 AM
Find the first chord in a song Set song as different animation beedee PowerPoint 1 05-04-2014 10:21 AM
Find the first chord in a song Is it possible to play a song even after the slides loop? Mihai_VRD PowerPoint 1 04-17-2012 01:06 AM
Please help......[embed song in presentation] elmcity PowerPoint 0 01-09-2010 12:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft