VBA: File Character Count

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
794
I was wanting to see if there was away to add some code to my personal workbook so when I open and or save a workbook where the file path is more than 218 characters I will get a prompt telling me Ive exceeded the recommended character limit.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
305
Office Version
2016
Platform
Windows
Hi bama

if "my personal workbook" is the macroworkbook, and you want to automatically run it on every workbook opened or save, i think you cannot do that.
But this could help you out:
Code:
Sub pathlength()
    pathlength = Len(ActiveWorkbook.FullName)
    If pathlenght > 218 Then
    MsgBox ("The active workbook path lenght is >218")
    Else
    MsgBox ("The active workbook path lenght is <219")
    End If
End Sub
I suggest add this macro to "personal.xlsb" and make an icon to titlebar(or something like this) by this you can one-click-run the check.

I hope this helps.
J
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,034
Office Version
365
Platform
Windows
Try putting this in the ThisWorkbook module of your Personal.xlsb file
Code:
Option Explicit

Private WithEvents XlApp As Application

Private Sub Workbook_Open()
    Set XlApp = Application
End Sub

Private Sub XlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Len(Wb.FullName) > 218 Then MsgBox "Path/name is too long"
End Sub

Private Sub xlapp_workbookopen(ByVal Wb As Workbook)
    If Len(Wb.FullName) > 218 Then MsgBox "Path/name is too long"
End Sub
 

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
794
That works great. Could the prompt actually show how many characters is in the filename and maybe say something like. "You filename is 220 characters and the max is 218"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,034
Office Version
365
Platform
Windows
How about
Code:
MsgBox "You filename is " & Len(Wb.FullName) & " characters and the max is 218"
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,015
Office Version
2016
Platform
Windows
.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,690
Messages
5,470,165
Members
406,683
Latest member
LJH

This Week's Hot Topics

Top