VBA: File Character Count

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
784
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.
 

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
271
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
30,523
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
784
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
30,523
Office Version
365
Platform
Windows
How about
Code:
MsgBox "You filename is " & Len(Wb.FullName) & " characters and the max is 218"
 

Forum statistics

Threads
1,078,441
Messages
5,340,298
Members
399,364
Latest member
farhan11941234

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top