Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Save file in the same folder as the active workbook?

  Asked By: Durril    Date: Jan 11    Category: MS Office    Views: 810
  

I have several files that I need to save as Distribution files that
are in different folders. The job requires saving the files as
values (to eliminate the formulas of the original file), adding DIST
to the file name and leaving them in the same folder as the original
file. Everything works except it continues to save the files to my
desktop. Ive searched everywhere on how to fix but changing the
drive to thisworkbook.path or activeworkbook.path doesnt help. here
is the coade I have so far. I have been testing it using a macro on
a workbook saved on my desktop. Could this be the probelm? Do I
have to have it in personal macro WB?

Dim DISTNAME As Variant
DISTNAME = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) -
4) & " DIST" & ".xls"

Application.DisplayAlerts = False
Application.ScreenUpdating = False

ChDir (ActiveWorkbook.Path)

Sheets.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone



ActiveWorkbook.SaveAs DISTNAME

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Botan Suzuki     Answered On: Jan 11

Try this

Option Explicit

Sub stuff()
Dim DISTNAME As String 'Better to be more specific
DISTNAME = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "
DIST" & ".xls"
DISTNAME = ActiveWorkbook.Path & Application.PathSeparator & DISTNAME
'Tell it where to put it
Application.DisplayAlerts = False 'Not sure why this is needed
Application.ScreenUpdating = False
Sheets.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
ActiveWorkbook.SaveAs DISTNAME
Application.DisplayAlerts = True 'Tidy up after you finish
Application.ScreenUpdating = True 'Tidy up after you finish

End Sub

If the DIST file  already exists it will fail and not warn you.

 
Didn't find what you were looking for? Find more on Save file in the same folder as the active workbook? Or get search suggestion and latest updates.




Tagged:          

 
 
 
 

Related Post