CODEWITHSUNDEEP
vbadirectorysave-as
blindy
blindy

Reputation: 13

Is there a way to Get Last Directory so I can Save As into?

I am able to create a new directory on my desktop, my issues is that I don't know how to save multiple files into that folder, within the same Sub, since it has a dynamic name.

Option Explicit

Sub Make_Folder_On_Desktop()

Dim selectionsheet As Worksheet
Dim Group As Variant
Dim amount As Long
Dim BU As Long
Dim BUname As Variant
Dim sFilename As Variant

Set selectionsheet = Sheets("Project Selection")
Group = selectionsheet.Range("A19").Value
amount = selectionsheet.Range("B19").Value
BU = selectionsheet.Range("B6").Value
BUname = selectionsheet.Range("C6").Value
sFilename = BU & " - " & BUname

MkDir Group & " - " & amount & " - " & Format(Date, "mm-dd-yyyy") & " - " 
& Format(Time, "hhmmss")
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sFilename

End Sub

Last line is where I'm having the issue. I have "ThisWorkbook.Path" but can't figure out how to get it into the new folder I just created.

Upvotes: 1

Views: 101

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

MkDir Group & " - " & amount & " - " & Format(Date, "mm-dd-yyyy") & " - " & Format(Time, "hhmmss")

It's hard to know what the folder name is that you just created, because that instruction is responsible for too many things. Split it up.

  • Build/concatenate a folder name
  • Make a directory by that name

If we split up the work, things get much simpler:

Dim path As String
path = Group & " - " & amount & " - " & Format(Date, "mm-dd-yyyy") & " - " & Format(Time, "hhmmss")

MkDir path

And now we have the path in the ...path variable, readily usable for anything you might want to do with it:

ActiveWorkbook.SaveAs path & "\" & sFilename

As a side note, if you make the date format yyyy-mm-dd instead, you're ISO-compliant (i.e. the date is unambiguous everywhere in the world), and the folders become sortable by name.

Note that the procedure's name is misleading: it doesn't care where the folder is, and there's nothing that says it's under %USERPROFILE%\Desktop. Use Environ$("USERPROFILE") to retrieve the base path for the current user's profile directory.

Upvotes: 1

Related Questions