VBA

[Jas]

Hello,

I have:

currentDateTime = Format(Now(), "dd-mm-yyyy hh.mm.ss")
currentFolder = Dir(ThisWorkbook.Path, vbDirectory)


How do I concatenate above to create a file name as below:

fileName = currentFolder & " " & CurrentDateTime & " " & Stats.xlsm
currentFolder is variable lenght, named after user names eg Joe Bloggs, Mary Jane etc.

Many Thanks.

 

[NARAYANK991]

Hi ,

What is the purpose of doing this ? The currentFolder variable will contain only the folder name.

If you wish to open / save the file , you either give a full path name , or if you wish to open from / save to the same folder as the workbook which has this VBA , then you do not need a path.

Narayan

 

[GCExcel]

Hello,
Something like this?

Sub Test()
  Dim filename As String
  Dim currentDateTime As String
  Dim currentfolder As String
 
  currentfolder = Dir(ThisWorkbook.Path, vbDirectory)
  currentDateTime = Format(Date, "dd-mm-yyyy hh.mm.ss")
 
  filename = currentfolder & " " & currentDateTime & " " & "stats.xlsm"
 
  Debug.Print filename
 
 
End Sub

or

Sub Test2()
  Dim filename As String
  Dim currentDateTime As String
  Dim currentfolder As String
 
  currentfolder = ThisWorkbook.Path & "\"
  currentDateTime = Format(Date, "dd-mm-yyyy hh.mm.ss")
 
  filename = currentfolder & currentDateTime & "_" & "stats.xlsm"
 
  Debug.Print filename
 
 
End Sub

 

[Jas]

Hi ,

What is the purpose of doing this ? The currentFolder variable will contain only the folder name.

If you wish to open / save the file , you either give a full path name , or if you wish to open from / save to the same folder as the workbook which has this VBA , then you do not need a path.

Narayan

Hi Narayan,

I want to copy/save the current workbook user is working on in vba module and name the file as described above.
The currentfolder in my case is named after the user name e.g Joe Blogs.

Thanks.

 

[NARAYANK991]

Hi ,

In that case , you do not need the currentFolder variable. Just this statement will do it :

filename = Format(Now(), "dd-mm-yyyy hh.mm.ss") & " " & "Stats.xlsm"

Now , use this in a SaveAs statement ; the file will be saved in the current user's folder.

Narayan

 

[Jas]

Hello,
Something like this?

Sub Test()
  Dim filename As String
  Dim currentDateTime As String
  Dim currentfolder As String

  currentfolder = Dir(ThisWorkbook.Path, vbDirectory)
  currentDateTime = Format(Date, "dd-mm-yyyy hh.mm.ss")

  filename = currentfolder & " " & currentDateTime & " " & "stats.xlsm"

  Debug.Print filename


End Sub

or

Sub Test2()
  Dim filename As String
  Dim currentDateTime As String
  Dim currentfolder As String

  currentfolder = ThisWorkbook.Path & "\"
  currentDateTime = Format(Date, "dd-mm-yyyy hh.mm.ss")

  filename = currentfolder & currentDateTime & "_" & "stats.xlsm"

  Debug.Print filename


End Sub

Hi,

I tried similar code but I was getting an error. I read about a 'dirty string' somewhere but didn't fully understand how to 'clean' the string.

 

[Jas]

Hi ,

In that case , you do not need the currentFolder variable. Just this statement will do it :

filename = Format(Now(), "dd-mm-yyyy hh.mm.ss") & " " & "Stats.xlsm"

Now , use this in a SaveAs statement ; the file will be saved in the current user's folder.

Narayan

Thanks. Reason for user name in file is in case it gets moved elsewhere then we will not know which user it belongs to. The folder name (user name) is read into the sheet with a formula but it will be different depending upon which folder the file is in.

 

[NARAYANK991]

Hi ,

In that case just concatenate the currentFolder variable , the way you did it :

filename = Dir(ThisWorkbook.Path, vbDirectory) & " " & Format(Now(), "dd-mm-yyyy hh.mm.ss") & " " & "Stats.xlsm"

Narayan

 

[Jas]

Hi ,

In that case just concatenate the currentFolder variable , the way you did it :

filename = Dir(ThisWorkbook.Path, vbDirectory) & " " & Format(Now(), "dd-mm-yyyy hh.mm.ss") & " " & "Stats.xlsm"

Narayan

Cool! that works as required. Thanks.

Do I need to worry about 'dirty string' issue prior to doing save as?

 

[Jas]

Thanks to GCExcel and Narayank991 for your suggestions.

 

[NARAYANK991]

Hi ,

I don't think you need to worry about that ; a dirty string is one which contains special characters , not just alphabets and digits. As long as your file name does not contain these characters ( such as the forward slash / , the backward slash \ , the asterisk * ,... , you are safe.

Narayan

 

[Jas]

Thanks.

 

[Jas]

Hello,

I using this code to do my saveas of the open file:

fileName = Dir(ThisWorkbook.Path, vbDirectory) & " " & Format(Now(), "dd-mm-yyyy hh.mm.ss") & " " & "Stats.xlsm"

ThisWorkbook.SaveAs fileName:=ThisWorkbook.Path & "\" & fileName
Workbooks(fileName).Close savechanges:=True

How can I keep the original file open?

 

[Jas]

Hello,

I using this code to do my saveas of the open file:

fileName = Dir(ThisWorkbook.Path, vbDirectory) & " " & Format(Now(), "dd-mm-yyyy hh.mm.ss") & " " & "Stats.xlsm"

ThisWorkbook.SaveAs fileName:=ThisWorkbook.Path & "\" & fileName
Workbooks(fileName).Close savechanges:=True

How can I keep the original file open?

I used SaveAsCopy (without the close) and this seems to work

ThisWorkbook.SaveCopyAs fileName:=ThisWorkbook.Path & "\" & fileName