Create folder with current date as name(Done), then save fil

[gene_sc]

Hi
using VBA I created a folder with current date as name(works fine),then in the next bit of code- I create some files, that I want to save in the new folder( with date in title). The problem is telling excel what the folder name is, when the folder name changes each day?
thanks
regards
gene

 

[DonkeyOte]

Set the folder as variable - i.e. Folder_Name = Today()

then

ActiveWorkbook.saveas ("C:" & Folder_Name & "blah.xls"

See if that works...??

 

[gene_sc]

Hi lasw10
I see in your example that you used C:\ why the double slashes?? I will give it a try :^)
Here the code I have been trying to work out... (newbe here)
Sub folder()
'//create folder
Dim sFolderName As String, set_Path As String, name As String, datpath As String
Set fs = CreateObject("Scripting.FileSystemObject")
'//////////change path/file name below
set_Path = "C:Daily ReportsEOD_CurrentEOD_"
sFolderName = Format(Now(), "mm-dd-yy")
'///Name=cat name & date to use later
[name] = (set_Path & sFolderName)
datpath = [name]
'//create folder
If Error <> 0 Then
msgbox " Folder exists", vbInformation, "EOD Report Filter"'Resume Next if folder exists
End If
On Error Resume Next
fs.CreateFolder [name]
'above code works fine creates a folder with text & date
'//////////////
'save file to folder just created...
Windows("Credits_Current.iif").Activate
ChDir "datpath" '/Pathwanted>"C:Daily ReportsEOD_CurrentEOD_DateCredits_Current.iif"
'/ 9/14 add date to file name
'//////////
Dim sFileName As String, sPath As String
'//////////change path/file name below
sPath = "datpath" '//////"C:Daily 'ReportsEOD_CurrentEOD_Date"
'////////
sFileName = Format(Now(), "mm-dd-yy")
ActiveWorkbook.SaveAs (sPath & "EOD_" & sFileName), FileFormat:=xlNormal, _
CreateBackup:=False
'ActiveWindow.Close
'END DATE ADDED
'///////////////////////
End Sub
Thanks
regards gene

 

[PaddyD]

gene,

double slashes is a bug with the board - take a look at your message & you'll see that each single slash is posted up as two.

Paddy

 

[gene_sc]

Thanks PaddyD
I wondered if I typed all of them

Lasw10-- Works great!! I changed it to yesterday (the actual date of the report)
Is ther a way to skip a date if it is a holiaday or Sunday??(no report is generated)
thanks Alot
regards gene
This message was edited by gene_sc on 2002-09-16 21:13

 

[DonkeyOte]

You should be able to use the WEEKDAY function to determine as to whether or not Today() is a weekend day etc... if you used this as an IF argument in your Macro you could then exit sub if not weekday.

 

[gene_sc]

Hi
Having trouble with the weekday test in my code. What I want to happen if "Today" is Saturday then, I want the 1st "if" to run. If "today" is not saturday then the 2nd "if" to run .
Thanks alot-- I have looked at vbWeekday ect but I am totally confused
regards gene

Sub mydateTest()
Dim Today 'report date = today - 1
Dim vbWeekday
vbWeekday = Now()
If vbWeekday = vbSaturday Then
Today = Format(Now() - 2, "mm-dd-yy")
Else
Today = Format(Now() - 1, "mm-dd-yy")
End If

Folder_Name = ("EOD_" & Today)

ActiveWorkbook.SaveAs ("C:Daily ReportsEOD_Current" & Folder_Name & "EOD_" & sFileName), FileFormat:=xlText, _
CreateBackup:=False

 

[Dragracer]

Try changing your code to this<pre>
Sub mydateTest()
Dim Today 'report date = today - 1
Dim vbWeekday

myWeekday = WeekDay(Now())
If myWeekday = vbSaturday Then
Today = Format(Now() - 2, "mm-dd-yy")
Else
Today = Format(Now() - 1, "mm-dd-yy")
End If

Folder_Name = ("EOD_" & Today)

ActiveWorkbook.SaveAs ("C:Daily ReportsEOD_Current" & Folder_Name & "EOD_" & sFileName), FileFormat:=xlText, _
CreateBackup:=False
End Sub</pre>
This message was edited by Dragracer on 2002-09-21 15:48

 

[gene_sc]

Thanks Dragracer :^)
Works perfect
regards
gene

 

[Jack in the UK]

Hi (I dedicate this post to a guy i never knew a Fire Fighter in the USA New York - this i felt was some of my finest work, and he was fine finer than i he will be on the net somewhere from World Trade Center loss)

I explain nothing --- dedicated to -

TERRY "Manhatton" Hatton (RIP friend)
Remember all the loss

|||||||||||||||||||||||||||||

Taking things forward a bit this was code i wrote in Jack_0_sence in June this year kinda popluar, guys have edited it to their ways of cause but will do as you want with a tweak here and there, has error handles like if DIR exits and so on, this was a back you

Cheek feed
http://www.mrexcel.com/board/viewtopic.php?topic=10750&forum=2