Using Date format As Folder Name

[aba104]

Hi,

I am trying to save a group of files created on the same day in the same folder. Since these files are produced daily, I am trying to group these filesunder in the same folder using date as folder name .

The folder was created by using Dir(Path,vbDirectory) function. But what strange is that after this folder is created, it is still taken by the vba that the folder does not exist and it creates the folder over and over again. I am thinking about if vb can not recognize the folder due to the folder name in date format.

Can any one come up any solution please? Thanks a lot.

JY

 

[MiggyD]

Without seeing your code it would be difficult to offer an answer.

1) you may be using another directory (maybe to open a file) and that is where VB is saying that it's not finding the folder)

2a) you may consider using a global variable (at the beginning of a module/main macro code) to maintain the pathway.

2b) have you consider saving the pathway with savesetting and upon program termination use the deletesetting commands?

3) you may have a NON-Static variable within the 'Saving' code that gets erased.

4) you may ALREADY be within that folder...you may need to move back to the parent directory or start from the root.


Could be anything. Post your code and let us offer more definitive answers.

--MiggyD

 

[aba104]

Miggy,

Thank you for your help. Here is my code:

mycount = Sheets("Head Sheet&quot.Range("O51&quot.Value

a = Format(Now, "mmddyy&quot

Set fs = CreateObject("Scripting.FileSystemObject&quot

If fs.FolderExists(a) = False Then
MkDir a
ChDir a
mycount = mycount + 1
ThisWorkbook.SaveCopyAs Filename:="Teller103-" & mycount & ".xls"
Else
ChDir a
mycount = mycount + 1
ThisWorkbook.SaveCopyAs Filename:="Teller103-" & mycount & ".xls"

End If
Sheets("Head Sheet&quot.Range("O51&quot.Value = mycount

I will try your suggestions. Thank you.

JY

 

[Bowers74]

You need the complete path of the folder before you can create or even add a file to it.

Try setting the "a" variable to the following and then try a test run:

a = "C:\My Documents\" & Format(Now, "mmddyy&quot

Where "C:\My Documents\" is the path where the folder is to be located.

Be sure to have the "a" variable "Dim"ed as a String too! ;-)

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

Check out my FAQ: faq707-4116 - File and Folder Procedures

It might help you as well!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MiggyD]

I, too, have to agree with Bowers74 about the specified pathway. You should follow that advise.

Sorry it took so long to respond but at first glance (and I mean glanced) your code appeared to be viral in nature--which I adamantly abhor. But, I hope I'm wrong AND hope this line will fix your delima.


'---Start of code
mycount = Sheets("Head Sheet&quot.Range("O51&quot.Value
a = Format(Now, "mmddyy&quot
Set fs = CreateObject("Scripting.FileSystemObject&quot
If fs.FolderExists(a) = False Then
MkDir a
ChDir a
mycount = mycount + 1
ThisWorkbook.SaveCopyAs Filename:="Teller103-" & mycount & ".xls"
Else
ChDir a
mycount = mycount + 1
ThisWorkbook.SaveCopyAs Filename:="Teller103-" & mycount & ".xls"
End If
[red]ChDir ".."[/red]
Sheets("Head Sheet&quot.Range("O51&quot.Value = mycount
'---End of code


Again, this is a quick fix. You app will need to be started from the same parent directory every time. Otherwise it will reach the root and you may get an error.

--MiggyD

 

[aba104]

Thank you all..

I took Miggy's method, changed directory to "..", it was simple and worked perfectly. Again, thank you all very much!

JY

 

[MiggyD]

You're welcome.

Just remember that you are constantly creating NEW folders/files...eventually that directory will fill up (the virus-like activity I mentioned above) and you'll have to either delete really old ones OR compress and archive them elsewhere.

Good Luck,
--MiggyD

 

[aba104]

Thank you, Miggy. I will have the files deleted after it accumulates 1 month.

JY