CODEWITHSUNDEEP
vbaexcel
Ndiol Dia
Ndiol Dia

Reputation: 255

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, then, when this is executed somewhere else, there won't be problem.

Can somebody help ?

Upvotes: 18

Views: 125613

Answers (4)

Alex P
Alex P

Reputation: 12489

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

Upvotes: 21

Jonas Arnout
Jonas Arnout

Reputation: 51

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

ActiveWorkbook.SaveAs "Filename.xslx"

Upvotes: 5

Tim Schimandle
Tim Schimandle

Reputation: 802

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.

Upvotes: 2

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

Upvotes: 8

Related Questions