Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-31-2017, 06:35 AM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,770
Thanks: 624
Thanked 332 Times in 304 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Exclamation Excel SaveAs Path Length is limited to 218 Characters - Error is difficult to trap

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.

https://support.microsoft.com/en-us/...e-is-not-valid
Excel Max Length of SaveAs is 218 characters
Code:
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.

End If

End Sub
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.

Rx_ is offline   Reply With Quote
Reply

Tags
error , excel , path , saveas , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel SaveAs Error 1004 ChrisEberhart Modules & VBA 4 02-06-2017 11:00 PM
MDB database field limited to 64 or 255 characters galapogos Tables 2 04-06-2010 05:59 AM
msgbox has limited characters? vinzz Modules & VBA 4 05-21-2008 03:30 PM
Do Bookmarks have a limited length Fiona H Modules & VBA 1 12-05-2006 03:17 AM
Unbound text box is limited to 20 characters tilman Forms 0 01-28-2005 03:30 AM




All times are GMT -8. The time now is 03:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World