PDA

View Full Version : VBA Dynamically Saving in a Folder Structure



fda506
08-06-2010, 09:37 AM
Hi

I was wondering if anyone could help? I have made an excel spreadsheet and I need to save one copy a day to a folder. However I was wondering if it was possible to create a button to save the template as a workbook based on the system date to a folder (already set up on the desktop), so for example I have opened and filled out the spreadsheet template I then want the button to save the file as a date (either based on the date written in the spreadsheet or the system date) then depending on the date the file would then be saved into a folder on the desktop called till reconciliation and in that folder (based on the date) the file will be saved into either January, Febuary, March etc

I was thinking an if statement however everything I have thought of is very messy any ideas?

David

gcomyn
08-06-2010, 11:51 AM
Here is a quick and dirty way to do it... it should work, as long as you put in the correct path in the constant, and the cell that you are pulling the date from.


Const CORRECT_PATH As String = "C:\"
Public Sub testinging()
Dim strFileName As String
Dim intPos As Integer
Dim strNewFileName As String
Dim strDate As String

strFileName = ActiveWorkbook.Name
intPos = InStr(1, strFileName, ".")
strNewFileName = Left(strFileName, intPos - 1)
strDate = Format(Range([CELL]).Value, "yyyymmdd")
strNewFileName = strNewFileName & "_" & strDate & Right(strFileName, Len(strFileName) - intPos + 1)
ActiveWorkbook.SaveAs CORRECT_PATH & strNewFileName

End Sub

GComyn
:sleuth: