Following is an extract of the scri

[MikeCDPQ]

Following is an extract of the script that I have. I want to create a path on network G drive for which I have all access and I get PATH NOT FOUND error message. That script used to work before and I can't get it to work again. Is there an other way of "rephrasing" the MKDIR portion of that script.

Anybody can tell me what's wrong.

Please also see thread707-665173



Dim CurrentVendorNo As String
Dim NextVendorNo As String
Dim FolderName As String

'Define the folder name
FolderName = Replace(ActiveWorkbook.Name, ".xls", "&quot

'Create directories
Do
'Compare both records
CurrentBuyerNo = Range("A" + CStr(i)).Value
NextBuyerNo = Range("A" + CStr(i + 1)).Value

If Not (CurrentBuyerNo = NextBuyerNo) Then
MkDir "G:\Department Shared Data\Purchasing\COMMON\" + FolderName + "\" + "BUYER " + CurrentBuyerNo

End If

i = i + 1

'Do until record is empty
Loop Until Range("A" + CStr(i)) = ""

The script does a whole lot more than just what up here but this is the first step. As mentioned before, I can run it in two passes, first one by deleting red portion of script and then reinserting it and running the code again.

Thanks for any suggestions.

 

[Bowers74]

You could try this:

Sub CreateFolder()
Dim fso
Dim fol As String
fol = "c:\MyFolder" ' change to match the folder path
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(fol) Then
    fso.CreateFolder (fol)
Else
    MsgBox fol & " already exists!", vbExclamation, "Folder Exists"
End If
End Sub

and take a look at my faq707-4116 - File and Folder Procedures

Good Luck!


Peace!

Mike

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

 

[xlbo]

You could use Mike's but that error usually means that one of the subfolders in the path you are spcifying doesn't exist. It looks to me like you are trying to create 2 folders in 1 go - Foldername & BUYER + CurrentBuyerNo

This won't work. To create a folder, all folders above it must already be in place so:

MkDir "G:\Department Shared Data\Purchasing\COMMON\" + FolderName + "\" + "BUYER " + CurrentBuyerNo

should be replaced with:
MkDir "G:\Department Shared Data\Purchasing\COMMON\" + FolderName
MkDir "G:\Department Shared Data\Purchasing\COMMON\" + FolderName + "\" + "BUYER " + CurrentBuyerNo

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MikeCDPQ]

Hi Bowers74,

Thanks for your suggestion but I am getting the exact same reaction (having to run script twice) and error message.

I am running this script on Windows XP.

It seems like there is some "refresh" not taking place after it should have created the first level:

fol = "G:\Department Shared Data\Purchasing\COMMON\" + FolderName

Once I have run code with the above I get a "FILE ALREADY EXIST" error message but that's fine because just below this script is a DO LOOP routine.

With the MKDIR command I get a "PATH NOT FOUND" error message instead of "FILE ALREADY EXIST".

However at this stage, if I go to Windows Explorer the folder created does not appear unless I manually go: VIEW REFRESH.

Then I can go back to my script, add the portion that I had to remove in the first run (+ "\" + "BUYER " + CurrentBuyerNo) to the end of fol= "G:\Department Shared data .... which will look like:
"G:\Department Shared Data\Purchasing\COMMON\" + FolderName + "\" + "BUYER " + CurrentBuyerNo
and then it works fine.

So basically, it reacts just the same as the MKDIR command.

Could the problem not lie within the script but rather within Windows ???

Thanks,


XLOB,

I tried your suggestion but then I get a "PATH/FILE ACCESS ERROR" error message.
Creates folder and only first subfolder and stops it at that when 11 more subfolders should be created.

Thanks anyway :-(

Michel

 

[xlbo]

I think the issue is that you have to 1st create the root folder on it's own and then create your subfolders. You can't do it all in one go so:

Dim CurrentVendorNo As String
Dim NextVendorNo As String
Dim FolderName As String

'Define the folder name
FolderName = Replace(ActiveWorkbook.Name, ".xls", "&quot
MkDir "G:\Department Shared Data\Purchasing\COMMON\" + FolderName
'Create directories
Do
'Compare both records
CurrentBuyerNo = Range("A" + CStr(i)).Value
NextBuyerNo = Range("A" + CStr(i + 1)).Value

If Not (CurrentBuyerNo = NextBuyerNo) Then
MkDir "G:\Department Shared Data\Purchasing\COMMON\" + FolderName + "\" + "BUYER " + CurrentBuyerNo

End If

i = i + 1

'Do until record is empty
Loop Until Range("A" + CStr(i)) = ""


should work

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MikeCDPQ]

Hi XLBO,

Thank you, thank you, thank you !!!

It works beautifully.

Now I can rest in peace

Take care.

 

[SpaaamCatcher]

Geoff, I am trying to do what Mike was: create a directory structure that may be longer than one folder. I was hoping to do it in one pass, but based on this trailer it looks like I won't be able to.

I tried to translate this code to be applicable to my situation, but haven't been able to. Can anyone help?

jem

So if the value in cell A1 = "C:\Folder1\Folder2" and Folder1 exists, the code will generate Folder2. HOWEVER, if Folder1 does NOT exist, I get a path not found error and nothing gets created...

Private Sub DirectoryExists()
Dim Msg, Style, Title, Response
Dim strFolderName As String

strFolderName = Worksheets("Test1&quot.Range("A1&quot

Set FSO = CreateObject("Scripting.FileSystemObject&quot
If Not FSO.FolderExists(strFolderName) Then
Msg = "This folder does not exist. Would you like to create it?"
Style = vbYesNoCancel
Title = "Create Folder?"
Response = MsgBox(Msg, Style, Title)
If Response <> vbYes Then End
Set CreatedFolder = FSO.CreateFolder(strFolderName)
End If

End Sub

 

[xlbo]

well what do you need to do ???
what's the problem with the code ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SpaaamCatcher]

IF the value in cell A1 = &quot;C:\Folder1\Folder2&quot; and Folder1 exists, the code will generate Folder2.

HOWEVER, if Folder1 does NOT exist, I get a 'PATH NOT FOUND' error and nothing gets created...

jem

 

[VBAjedi]

Here's a code snippet that should create a directory structure that's as deep as you care to make it. It does assume you have passed it a valid drive, though. Just set path (with or without a filename on the end) and the code will test each level of the directory structure, creating a folder if needed:

Path = "C:\My Documents\Temp\Test1\MyFile.xls"
For x = 4 To Len(Path) 
   If Mid(Path, x, 1) = "\" Then
      Set FSO = CreateObject("Scripting.FileSystemObject")
      If Not FSO.FolderExists(Left(Path, x)) Then
         Msg = Left(Path, x) & " does not exist. Would you like to create it?"
         Style = vbYesNoCancel
         Title = "Create Folder?"
         Response = MsgBox(Msg, Style, Title)
         If Response <> vbYes Then End
         Set CreatedFolder = FSO.CreateFolder(Left(Path, x))
      End If
   End If
Next x

Let me know if that does the trick for you!


VBAjedi

 

[SpaaamCatcher]

YES! Yes! It works, you rock. Thanks so much.

If you get a second, oh VBAjedi mastah, check out my other (more urgent, even) problem in thread707-697129. I've got a some code that works perfectly until the workbook gets shared...

Jamie