Creating a Folder in Medium date Format

[mtrant]

Hi,

I am using a module right now to export a report into a folder right now using the "Medium date format to rename the file. I was wondering if at the same time I could also creae a folder by the days "medium date" for that file to go into. here is the code that I am using right now,

Public Function DoesFileExist(PathStrg As String) As Boolean
   On Error Resume Next
   If Len(Dir(PathStrg, 14)) > 0 Then DoesFileExist = True
   If Err <> 0 Then Err.Clear
End Function
Public Sub Inventory_Report_Save()
'By Matthew Trant
   Dim FilePathStrg As String
   Dim FileNameStrg As String
   
   FilePathStrg = "[URL="file://webserver1/Webs/Lori/Inventory"]\\Webserver1\Webs\Lori\Inventory[/URL] - "
   FileNameStrg = Format(Now, "Medium Date") & ".html"
   
   If DoesFileExist(FilePathStrg & FileNameStrg) = True Then
      If MsgBox("The file name '" & FileNameStrg & "' already exists." & vbCr & _
                "Do you want To Overwrite it?", vbExclamation + vbYesNo, _
                "File Already Exists...") = vbYes Then
         Kill FilePathStrg & FileNameStrg
      Else
         Exit Sub
      End If
   End If
   DoCmd.OutputTo acOutputReport, "InventoryValue", acFormatHTML, FilePathStrg & FileNameStrg, False
End Sub

I hope that this is possible

Thank you in Adavance,


Matthew Trant
The Eastern CT Business Consultants

 

[LPurvis]

You just need to create the folder if it's required (i.e. if it doesn't already exist).
OutPut to will fail if the folder path you specify doesn't already exist.
(Compare this with the simply file IO VBA commands which will create a file - and any required folders in the path).

Essentially all you need to do is call a function to make the folder path if it doesn't already exist. (Some folks like to have made their own iterative function which walks the path and creates each folder in turn if required).
You can achieve this with a single API call. (Whose declaration is...)

Private Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long

Calling MakeSureDirectoryPathExists passing the folder path you want creates any required directory levels if they don't exist.
You can then use the target folder.
For example - where you currently have

FilePathStrg = "\\Webserver1\Webs\Lori\Inventory - "

You could have

FilePathStrg = "\\Webserver1\Webs\Lori\" & Format(Now, "Medium Date")
MakeSureDirectoryPathExists FilePathStrg

Or however you're wanting to format it.

 

[mtrant]

I tried using your method above a few different ways and have yet to get it to work...any ideas? Maybe I am inserting it into my code wrong.

 

[LPurvis]

Well I don't know - how have you inserted it? ;-)

Can you give examples of how you want the folder path to look?
And explain what it is that's gone wrong for you thus far?

 

[mtrant]

well basically I want a new folder to be created upon opening of the database in teh form of the current "Medium Date" but I am also exporting a report as the current medium date which I want to be saved in the new folder everyday. The reason why I need the new folder is becuase some of these reports are 20 and 30 pages long.

So i need the folder path to be

\\Webserver1\Webs\Lori\"Current Date"

and I need the file to export to the folder above.

Is this possible?

 

[LPurvis]

I don't know what you're wanting to call the file exported into that folder, but the creation of the folder should be exactly as I described.

What isn't happening for you? What is happening? (i.e. errors etc)?

 

[mtrant]

the file is renaming and the folder is not being created.

 

[mtrant]

no errors, just the file being created in the main folder.

 

[LPurvis]

I'd have thought you'd want something like:

Private Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long
 
Public Function DoesFileExist(PathStrg As String) As Boolean
   On Error Resume Next
   If Len(Dir(PathStrg, 14)) > 0 Then DoesFileExist = True
   If Err <> 0 Then Err.Clear
End Function

Public Sub Inventory_Report_Save()
'By Matthew Trant
   Dim FilePathStrg As String
   Dim FileNameStrg As String
   
   FilePathStrg = "\\Webserver1\Webs\Lori\" & Format(Now, "Medium Date") & "\"
[COLOR=black]   MakeSureDirectoryPathExists FilePathStrg [/COLOR]
   FileNameStrg = "Inventory - " & Format(Now, "Medium Date") & ".html"
   
   If DoesFileExist(FilePathStrg & FileNameStrg) = True Then
      If MsgBox("The file name '" & FileNameStrg & "' already exists." & vbCr & _
                "Do you want To Overwrite it?", vbExclamation + vbYesNo, _
                "File Already Exists...") = vbYes Then
         Kill FilePathStrg & FileNameStrg
      Else
         Exit Sub
      End If
   End If
   DoCmd.OutputTo acOutputReport, "InventoryValue", acFormatHTML, FilePathStrg & FileNameStrg, False
End Sub

Make sure that the MakeSureDirectoryPathExists declaration is in the declarations section (the top part) of your module.

 

[CyberLynx]

Add this line of code to the Declarations section of a Database Code Module (not a Form code module):

Public Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As LongAnother Path

In the same Database Code Module, add this function, MakePath:

Public Function MakePath(ByVal PathStrg As String) As Boolean
   Dim Ret As Long

   If Right$(PathStrg, 1) <> "\" Then PathStrg = PathStrg & "\"
   Ret = MakeSureDirectoryPathExists(PathStrg)
   If Ret = 0 Then
      MsgBox "Error Creating Path!", vbExclamation, "Path Creation Error"
   Else
      MakePath = True
   End If
End Function

Now change your Inventory_Report_Save procedure to look like this:

Public Sub Inventory_Report_Save()
'By Matthew Trant
   Dim FilePathStrg As String
   Dim FileNameStrg As String
   
   FilePathStrg = "\\Webserver1\Webs\Lori\Inventory - "
   FileNameStrg = Format(Now, "Medium Date") & ".html"
   
   If DoesFileExist(FilePathStrg & FileNameStrg) = True Then
      If MsgBox("The file name '" & FileNameStrg & "' already exists." & vbCr & _
                "Do you want To Overwrite it?", vbExclamation + vbYesNo, _
                "File Already Exists...") = vbYes Then
         Kill FilePathStrg & FileNameStrg
      Else
         Exit Sub
      End If
   [COLOR="Blue"]Else
      If MakePath(FilePathStrg) = False then Exit Sub[/COLOR]
   End If

   DoCmd.OutputTo acOutputReport, "InventoryValue", acFormatHTML, FilePathStrg & FileNameStrg, False
End Sub

.

 

[mtrant]

Thank you both so much, everything is perfect now.

 

[CyberLynx]

Whoops....LPurvis beat me to it...Sorry