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

gene_sc

New Member
Joined
Aug 24, 2002
Messages
32
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Set the folder as variable - i.e. Folder_Name = Today()

then

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

See if that works...??
 

gene_sc

New Member
Joined
Aug 24, 2002
Messages
32
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Aug 24, 2002
Messages
32

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Aug 24, 2002
Messages
32

ADVERTISEMENT

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

Board Regular
Joined
Jun 9, 2002
Messages
151
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
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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
 

Forum statistics

Threads
1,144,263
Messages
5,723,320
Members
422,492
Latest member
RobF2112

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top