Here is yet another number to memorize, 218
Evidently, when Excel uses a Link to another workbook / worksheet in a formula, the string length of the worksheet is reserved as part of the total SaveAs Path Length.
For Excel VBA remote automation from MSAccess code, the handshake is not pretty.
One of the Automated Excel Reports reads a Database field for an Account name. An Access For Each loop reads that field, uses queries to look up records, applies this to an Excel Template, applies more dashboard Excel VBA, then SavesAs on a network drive.
It automatically creates around 300 workbooks this way.
After receiving a new UNC netowork path (just 3 characters longer), my automatic job is running along until workbook 185... and errors.
At this point, the program has been running fine for a couple of weeks.
After checking the length of the SaveAs path (including the extra long workbook name) it was well under 255 characters.
However, Excel doesn't play by the same rules!
After starting a post, the answer came across. Thought it might be worthy of further discussion.
The Excel Reports have 2 formats for SaveAs
1. QA - runs from desktop folder, goes to Output Sub- Folder based on relative path of the Access Application. (see code below)
2. Production - Based on a UNC path entered into an Access "Parameter" table. I have limited the string length of this table's field to prevent users from making this mistake of overflow UNC.
Excel Max Length of SaveAs is 218 characters
Private Sub Form_Open(Cancel As Integer)
Dim LengthOfPath As Integer ' The application folder path lenght can not exceed
LengthOfPath = Len(Left(Application.CurrentProject.Path, InStrRev(Application.CurrentProject.Path, "\")))
If LengthOfPath > 80 Then
MsgBox "The character length of the Current Path for this application is too long! When The Executive Reports are run, the length of the Current Path and the " & _
" Excel Workbook Name can not be more than 218 characters. Place the Weekly Folder in a shorter path to continue", vbCritical, "Path of folder is too long at " & Len(Left(Application.CurrentProject.Path, InStrRev(Application.CurrentProject.Path, "\")))
' In the future, add code to find the longest Excel Workbook name (that is based on reading a field the Database) then subtract the UNC or SaveAS folder path.
B.T.W. my error was just 1 character over. So, this validates the MSDN article.
Old programming dogs don't learn new tricks. They just blindly copy and paste code to prevent issues they have forgotten about.