Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use the following code to save a new workbook with a new name into the same directory:

    dirf = ActiveWorkbook.Path
    ChDir (dirf)

    fname = Range("F_Order").Value
    ActiveWorkbook.SaveAs Filename:= _
    fname, _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False


    The range "F_Order" is on the workbook.
    Sometimes this works fine, but on some machines, I get a runtime error 1004 message and the path is incorrect:
    "C:\Documents and Settings\User\My Documents\R001.xls could not be found"

    The problem is that the path is not changing to the path of the document, but instead the default user path. Why does this happen and how do I resolve it?

    The code was created in 2003 and some users are using 2007, if this helps.

    Cheers, and thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of using Chdir, try specifying the path in the SaveAs method:
    Code:
    dirf = ActiveWorkbook.Path
    If Not Right(dirf, 1) = "\" Then
      dirf = dirf & "\"
    End If
    fname = Range("F_Order").Value
    ActiveWorkbook.SaveAs Filename:=dirf & fname

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Note that the code will fail for a NEW workbook. ActiveWorkbook.Path will still be empty, so the workbook will automatically be saved to the default workbook path.

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    A likely reason for the difference between the PCs that work and the ones that do not is that chPath will only switch to another folder on the same drive. You would need to use chDrive first if the target folder is on a different drive.

    Hans solution doesn't rely on chPath and so will always use the correct folder.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans - thanks for the code tip - works perfectly.

    StuartR - thanks for the explanation, makes sense and was exactly the problem.

    Cheers,
    Tedoz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •