VBA: File Character Count

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
821
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
How about
Code:
MsgBox "You filename is " & Len(Wb.FullName) & " characters and the max is 218"
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,724
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top