Saving an Excel sheet in a current directory with VBA

I have created a sheet in vba Excel. I would like to save it the current directory, but not in absolute path, 开发者_如何学Gothen, when this is executed somewhere else, there won't be problem.

Can somebody help ?

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 & Application.PathSeparator & ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=relativePath
End Sub

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.

If the Path is omitted the file will be saved automaticaly in the current directory. Try something like this:

ActiveWorkbook.SaveAs "Filename.xslx"

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 & "\" & "newFileName.xlsx"

Only works if your file structure contains one instance of the text used to replace. YMMV.