Copy Workbook Name

[cwojtak]

Looking to get the name of an activeworkbook copied so I can paste it into my code. I'm sick of typing them out all the time especially when writing macros that interact with multiple workbooks. I can get it in a msgbox but you cannot copy text from a message box.

MsgBox ActiveWorkbook.Name

[Paul_Hossler]

Q1 - why do you want to paste the name of the WB into your code?

Q2 - why not just use the ActiveWorkbook as an object?

Q3 - you could put

sWB = Activeworkbook.Name

and use sWB

[cwojtak]

Q1 - The workbooks I use are linked to a business planning add-in which I haven't found a way to manipulate using VBA so I have to open the workbooks with one sub, refresh the data manually, then start another sub to continue with the update process. In the second sub I run I must use each workbooks name at least once.

Q2 - I do use activeworkbook, but it's setting the workbook I need as the activeworkbook where I'd like to just copy and paste the workbook name.


Although I minimize how often I reference a workbook by its name, sometimes it's just not possible. I posted the question as it's something I'd use long term in writing VBA to reduce errors in my code.. Some of the workbook names I refer to can get quite long.

Is there a way you know of to print the name to the debugger maybe? I'd just like it in a place that is copyable but preferably not in a cell as to do that I'd need to add a new workbook which just seems like it would take longer than is necessary.

[cwojtak]

Here's what I came up with and it's relatively fast, certainly faster than typing. Let me know if you find anything shorter though as typing this code it takes longer to type than it would to type two to three workbook names manually so in shorter projects it doesn't always make sense -

Sub CopyWorkbookName()

Name = ActiveWorkbook.Name
Workbooks.Add
Range("A1").Value = "Workbooks(" & Chr(34) & Name & Chr(34) & ").Activate"
Range("A1").Copy
ActiveWorkbook.Close savechanges:=False


End Sub

[Paul_Hossler]

Well, I'm still not understanding the workflow, but I think it'd be easier to just put the active workbook name in the clipboard and paste as needed

Option Explicit


'ref https://www.thespreadsheetguru.com/blog/2015/1/13/how-to-use-vba-code-to-copy-text-to-the-clipboard


'Handle 64-bit and 32-bit Office
#If VBA7 Then
    Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
    Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, _
        ByVal dwBytes As LongPtr) As LongPtr
    Private Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
    Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
    Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
        ByVal lpString2 As Any) As LongPtr
    Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As LongPtr, _
        ByVal hMem As LongPtr) As LongPtr


#Else
    Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
    Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
    Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
        ByVal dwBytes As Long) As Long
    Private Declare Function CloseClipboard Lib "user32" () As Long
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function EmptyClipboard Lib "user32" () As Long
    Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
        ByVal lpString2 As Any) As Long
    Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _
        As Long, ByVal hMem As Long) As Long
#End If


Const GHND = &H42
Const CF_TEXT = 1
Const MAXSIZE = 4096


Function ClipBoard_SetData(MyString As String)
    'PURPOSE: API function to copy text to clipboard
    'SOURCE: www.msdn.microsoft.com/en-us/library/office/ff192913.aspx
    
    Dim hGlobalMemory As Long, lpGlobalMemory As Long
    Dim hClipMemory As Long, X As Long


    'Allocate moveable global memory
    hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)


    'Lock the block to get a far pointer to this memory.
    lpGlobalMemory = GlobalLock(hGlobalMemory)


    'Copy the string to this global memory.
    lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)


    'Unlock the memory.
    If GlobalUnlock(hGlobalMemory) <> 0 Then
        MsgBox "Could not unlock memory location. Copy aborted."
        GoTo OutOfHere2
    End If


    'Open the Clipboard to copy data to.
    If OpenClipboard(0&) = 0 Then
        MsgBox "Could not open the Clipboard. Copy aborted."
        Exit Function
    End If


    'Clear the Clipboard.
    X = EmptyClipboard()


    'Copy the data to the Clipboard.
    hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)


OutOfHere2:
    If CloseClipboard() = 0 Then
        MsgBox "Could not close Clipboard."
    End If
End Function


Sub CopyTextToClipboard()
    'PURPOSE: Copy a given text to the clipboard (using Windows API)
    'SOURCE: www.TheSpreadsheetGuru.com
    'NOTES: Must have above API declaration and ClipBoard_SetData function in your code
    
    Dim txt As String
    
    'Put some text inside a string variable
     txt = ActiveWorkbook.Name
    
    'Place text into the Clipboard
    ClipBoard_SetData txt
    
    'Notify User
     Call MsgBox("The active workbook" & vbCrLf & vbCrLf & _
        txt & vbCrLf & vbCrLf & _
        "is now copied to your clipboard!", vbInformation + vbOKOnly, "Clipboard")
End Sub

[cwojtak]

Thank you for that, I saw that but was hoping for a code that's just a couple lines. If I had to type something like that out or copy and paste it from somewhere it would be easier for me just to type out the workbook name. Some of the workbooks I deal with have names like "08 Product_VT_Loc_Planning StartedChanges.xlsm" and I often make mistakes typing the name to activate the workbook. Figured it's easier just to copy and paste the workbook name. I use activeworkbook when I can but it's just not always possible.

There are also 5-6 workbooks in some of the macros I have to create and I've found when I assign them to things like sWB = name I spend more time trying to figure out which variable is linked to which workbook than I would if I just referred to it as the actual workbooks name, especially when I review my code after writing it.

There probably is a better workflow than the one I use to approach writing VBA, if you have any resources that provide information on how to improve VBA workflow I'd be very interested! I started using forums like this and other resources on the internet to teach myself to write VBA ~3 years ago.. Ah, the days when I had no idea what Dim meant haha

[Paul_Hossler]

Put the macro in Personal.XLSM and see

[cwojtak]

Works great, thank you Paul!