Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

vba - Copy Excel theme color in a new workbook

I want to make a macro that copies two sheets in a new workbook. But the theme color in the new created workbook is different.

Sub Export_File()
Dim Wb3 As Workbook
Dim strSaveName As String

strSaveName = Worksheets("Communication").Range("a2").Value
Set Wb3 = ThisWorkbook
 
'copy sheets to new workbook
Sheets(Array("Auswertung", "Communication")).Copy
ActiveWorkbook.SaveAs strSaveName

Workbooks(Wb3).Colors = Workbooks(strSaveName).Colors

End Sub

This line does not work for me:

Workbooks(Wb4).Colors = Workbooks(strSaveName).Colors

I think it has something to do with Set Wb4 = ThisWorkbook Need help...

Greetings

enter image description here

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

This appears to be working for me, to copy a workbook theme:

    '   copy the colors and themes
    '
    resultWorkbook.Colors = sourceWorkbook.Colors
    'Theme is not the same as colors
    Dim sourceTheme As Microsoft.Office.Core.ThemeColorScheme = sourceWorkbook.Theme.ThemeColorScheme 
    Dim resultTheme As Microsoft.Office.Core.ThemeColorScheme = resultWorkbook.Theme.ThemeColorScheme 

    For i = 1 To sourceTheme.Count  ' there are 12 theme colors: https://msdn.microsoft.com/en-us/library/aa432704(v=office.12).aspx
        'Debug.WriteLine(String.Format("{0, -2} ~ {1}", i, sourceTheme.Colors(i).RGB))
        resultTheme.Colors(i).RGB = sourceTheme.Colors(i).RGB
    Next i

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...