Saving an Excel sheet in a current directory with VBA

Question

I have created a sheet in vba Excel  I would like to save it the current directory  but not in absolute path  then  when this is executed somewhere else  there won t be problem   Can somebody help

User · Answer

VBA has a CurDir keyword that will return the "current directory" as stored in Excel. I'm not sure all the things that affect the current directory, but definitely opening or saving a workbook will change it.

MyWorkbook.SaveAs CurDir & Application.PathSeparator & "MySavedWorkbook.xls"

This assumes that the sheet you want to save has never been saved and you want to define the file name in code.

User · Answer

If the Path is omitted the file will be saved automaticaly in the current directory  Try something like this   ActiveWorkbook SaveAs  Filename xslx

User · Answer

Taking this one step further  to save a file to a relative directory  you can use the replace function  Say you have your workbook saved in  c  property california sacramento workbook xlsx  use this to move the property to berkley   workBookPath   Replace ActiveWorkBook path   sacramento    berkley   myWorkbook SaveAs workBookPath  amp       amp   newFileName xlsx    Only works if your file structure contains one instance of the text used to replace  YMMV

User · Answer

I am not clear exactly what your situation requires but the following may get you started  The key here is using ThisWorkbook Path to get a relative file path  Sub SaveToRelativePath       Dim relativePath As String     relativePath   ThisWorkbook Path  amp  Application PathSeparator  amp  ActiveWorkbook Name     ActiveWorkbook SaveAs Filename  relativePath End Sub

[vba] Saving an Excel sheet in a current directory with VBA

Examples related to vba

Examples related to excel