0

I have a code where in, I am trying to save the file with a required name.

Dim FilNameRq1 as string
Dim FormattingWbk as workbook
.
.
FormattingWbk.saveas FileNameRq1

FileNameRq1 here is \paer\Test Environment\Prep 01-25-18 12-00-59 PM\Classified Share Drive Report - Privates and Restricteds-en-None Members - WW0533969 - JOHN DIGGLE- 11-05-2017.xlsx

it works if the file name is \paer\Test Environment\Prep 01-25-18 12-00-59 PM\Test.xlsx

Community
  • 1
  • 1
Student of the Digital World
  • 2,971
  • 16
  • 62
  • 116
  • I think there is a 256 character limit on file names/paths. – SANM2009 Jan 25 '18 at 12:09
  • 1
    You haven't really asked a question. Is your question **Why is this happening** or is it **How can I workaround the problem** ? – CLR Jan 25 '18 at 12:29
  • I would want to know How can I solve this actually – Student of the Digital World Jan 25 '18 at 12:30
  • @Sid29 you cannot solve this. Use a shorter path or shorter filename. Full Path + Filename must be less than 260 characters. See [Why does the 260 character path length limit exist in Windows?](https://stackoverflow.com/questions/1880321/why-does-the-260-character-path-length-limit-exist-in-windows) – Pᴇʜ Jan 25 '18 at 12:30
  • Try prepending `\\?\ ` to the full path and filename. If Word passes it to the file system it turns of filename length checks. – ACatInLove Jan 25 '18 at 12:53
  • @ACatInLove No, the full path has the \\ in it and it does exceeds 260 character limits. – Student of the Digital World Jan 25 '18 at 12:56
  • I said prepend the FQFN with `\\?\ ` to turn of filename checks. Win NT (though not Win32) supports paths up to 32K characters. EG `\\?\c:\alongfoldername\alongfilename.ext`. If Word doesn't prevent then it will work in the Win32API. The 260 character limit is a compatability thing. – ACatInLove Jan 25 '18 at 13:02
  • 1
    In the past when limits are reached, I've manually mapped a drive letter to a folder further along the path chain and used that to save the file, before removing the temp drive. No reason why this couldn't be done in vba, although relative references within the sheets might bork so would need to be considered. – CLR Jan 25 '18 at 14:52

2 Answers2

3

Depending on the file system and OS the path limit is changing. For Windows 7 it is 260 characters (starting from a drive letter). It includes drive letter, ":\" and conclusive NULL. For the rest of the path there is only 256 characters left.

Khizhny Andrey
  • 161
  • 1
  • 5
0

Can you try:

FormattingWbk.SaveAs Filename:= FilNameRq1

You're not hitting the character limit on this filename so I don't think that's a concern.

Is the subfolder you want to save to the same for all variables? If so we might be able to work with that by pre-defining that directory.

What's the full path?

How are you assigning a value to the variable? I would open your local window and step through to the variable definition. If you're not getting an error message it might have saved it to a different path or filename you didn't intend.

eau_de_Brute
  • 19
  • 2
  • There is a very high probability that he **does** hit the character limit. You cannot tell if he does or not because you don't know how long the path before `\paer\Test Environment\ ` is because he didn't show it. – Pᴇʜ Jan 25 '18 at 12:37