• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

ChDir Not Working Properly in Excel

I want to open a workbook in one folder, and then use a command button to have a completed worksheet saved to a different folder.  The filename is based on the contents of two cells in the worksheet.  Here is the code that I am using for the command button.

ChDir "\\lpofs01\groups\Engineering\LAB\MI Calculations for Offspec Types"
    ActiveWorkbook.SaveAs Filename:= _
    Range("C3") & "_" & Range("C5") & "_MI Calc", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

My problem is that the file will save in the folder that the worksheet is opened, not the folder that I designate in the ChDir statement.

I am puzzled because I have other worksheets that I do the same thing and they work fine.

Thanks for the help.
0
Steve Lowry
Asked:
Steve Lowry
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
Roy CoxGroup Finance ManagerCommented:
Are you sure the entries in the cells are correct?
0
 
Martin LissOlder than dirtCommented:
No need to ChDir

ActiveWorkbook.SaveAs Filename:= _
"\\lpofs01\groups\Engineering\LAB\MI Calculations for Offspec Types\" _
    Range("C3") & "_" & Range("C5") & "_MI Calc", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
0
 
Steve LowryPrincipal ChemistAuthor Commented:
Martin, when I pasted the above for the code for my command button, I got a compile/syntax error.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Saurabh Singh TeotiaCommented:
Small changes to what Martin posted..use this..

ActiveWorkbook.SaveAs Filename:= _
"\\lpofs01\groups\Engineering\LAB\MI Calculations for Offspec Types\" _
    Range("C3").Value  & "_" & Range("C5").value & "_MI Calc.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Open in new window


If this still give you an error..can you take a screenshot of error message and paste here..
0
 
Martin LissOlder than dirtCommented:
There was a missing ampersand

ActiveWorkbook.SaveAs Filename:="\\lpofs01\groups\Engineering\LAB\MI Calculations for Offspec Types\" _
      & Range("C3") & "_" & Range("C5") & "_MI Calc", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
0
 
Saurabh Singh TeotiaCommented:
Lol@Martin It still missing that.. it will be..Friday...it's always been crazy...

ActiveWorkbook.SaveAs Filename:= _
"\\lpofs01\groups\Engineering\LAB\MI Calculations for Offspec Types\" _
   & Range("C3").Value  & "_" & Range("C5").value & "_MI Calc.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Open in new window

0
 
Martin LissOlder than dirtCommented:
No, it's there.
0
 
Saurabh Singh TeotiaCommented:
Hahaha@You edited that one.. :-)
0
 
Steve LowryPrincipal ChemistAuthor Commented:
Thanks to both of you for your quick help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now