VBA Excel - Getting the ActiveWorkbooks.Name that is having spaces

Hi  @IsabelleCharest-5234 , and thanks for posting your question. 

ActiveWorkbook.Name should return the workbook’s complete file name, including spaces and the extension. Spaces in 20240312 ALL MAJ_DATA.xlsm do not cause VBA to truncate the value. (excel.workbook.name)

To verify the value immediately after assignment, use:

Dim sImportFileName As String

If ActiveWorkbook Is Nothing Then

    MsgBox "There is no active workbook."

    Exit Sub

End If

sImportFileName = ActiveWorkbook.Name

Debug.Print "Name: [" & sImportFileName & "]"

Debug.Print "FullName: [" & ActiveWorkbook.FullName & "]"

MsgBox "Active workbook: " & sImportFileName

Open the Immediate window in the VBA editor by pressing Ctrl+G. The expected output is similar to:

Name: [20240312 ALL MAJ_DATA.xlsm]

FullName: [C:\Import\20240312 ALL MAJ_DATA.xlsm]

ActiveWorkbook refers to the workbook in the currently active Excel window, while ThisWorkbook refers to the workbook containing the VBA code. Therefore, relying on ActiveWorkbook can reference the wrong file if focus changes during the macro. (excel.application.activeworkbook)

A safer solution is to store the workbook returned by Workbooks.Open:

Dim wbImport As Workbook

Dim sImportFileName As String

Set wbImport = Workbooks.Open("C:\Import\20240312 ALL MAJ_DATA.xlsm")

sImportFileName = wbImport.Name

Debug.Print "Import file: [" & sImportFileName & "]"

Debug.Print "Import path: [" & wbImport.FullName & "]"

Workbooks.Open returns the opened workbook, so this avoids depending on whichever workbook happens to be active later. (Excel.Workbooks.Open)

If the Immediate window still shows only 20240312 ALL, please share:

1. The complete code that opens the import file.
2. The code that subsequently reads or modifies sImportFileName.
3. The output of:

Debug.Print ActiveWorkbook.Name

Debug.Print ActiveWorkbook.FullName

Debug.Print ThisWorkbook.Name

Debug.Print Len(ActiveWorkbook.Name)

The value may be modified later in the code, or ActiveWorkbook may not be the workbook you expect. Before posting code or output, please remove file paths, credentials, connection strings, and any private or business-sensitive information.

If this instruction is applicable to your situation, I would greatly appreciate it if you could follow the instruction here so others experiencing similar behavior can benefit from it as well.  

ActiveWorkbook.Name should normally return the complete workbook filename, including the extension. If you are getting only 20240312 ALL, the likely issue is that Excel is hiding the extension in the workbook name being returned, or the value is being altered elsewhere in your code.

For the workbook currently active in Excel, you can explicitly obtain the full path and filename using FullName:

Dim sImportFileName As String sImportFileName = ActiveWorkbook.FullName

This would return something like C:\SomeFolder\20240312 ALL MAJ_DATA.xlsm. If you only want the filename and extension, without the folder path, use:

Dim sImportFileName As String sImportFileName = Dir(ActiveWorkbook.FullName)

That should give you exactly 20240312 ALL MAJ_DATA.xlsm.

Another approach for your situation, since Archives_Years.xlsm is the workbook containing the VBA code and the other workbook is the file being imported, is to explicitly identify the active workbook before or after the import. For example:

Dim sImportFileName As String sImportFileName = ActiveWorkbook.Name

If ActiveWorkbook.Name is unexpectedly returning 20240312 ALL, check whether the workbook is actually saved with that truncated name, or whether some other code is modifying sImportFileName. You can also use ActiveWorkbook.FullName to verify exactly which file Excel considers active.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin