VBA create folder help

[Pinaceous]

Hi All,

I'm trying to create a folder from an excel in using the following code:

Sub Creaolders()

  Dim sPath As String, sMain As String, sFolder As String
  Dim i As Long
 
  sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
  sMain = sPath & "\" & Sheets(1).Range("A1").Value
    
  If Dir(sMain) = "" Then
    MkDir sMain

'Add file Test.pdf from "C:\MISC\Test.pdf"

  End If

 
End Sub

It used to work really great before I migrated over to the 365 Microsoft platform.

I'm now getting an error on line:

 If Dir(sMain) = "" Then

Can someone help me with the code?

Thanks!
Pinaceous

 

[Pinaceous]

The specific error from the VBA reads:

Run-time error '52':
Bad file name or number

 

[cmowla]

The specific error from the VBA reads:

This is a wild guess (it worked fine for me on Microsoft 365), but

Sheets(1).Range("A1").Value

is a very vague way to identify the sheet. Are you sure that whatever sheet has an index of 1 in your Workbook . . . that Cell A1 in it has contents that a folder can be named? (If it's blank, this sub does nothing. It doesn't give an error. Just does nothing.)

So perhaps check the value that's in that cell in that specific sheet?

EDIT:
Does that cell have a vertical space in it? (I just tested that, and that gave an error.)

 

[Pinaceous]

Hi *cmowla,

I've checked the cell to make sure but it still pops an error on that line.

Is it because of the cloud & Microsoft 365?

I'm stumped!

 

[cmowla]

I have no idea. I don't mess with the cloud!

 

[Pinaceous]

Sorry, my workplace has the 365 but not the cloud.

Just not sure how to debug my error at this point in time.

 

[Pinaceous]

Hey,

I just opened up another document and tried it out and the code works.

But now, I'm curious as to why it does not work with my original workbook?

Any suggestions as to the Excel settings?

Thanks!
-pin

 

[Marc L]

Hi,​

​

no as it's just a question of logic not respected according to the content of variables sPath & sMain and maybe a misuse of Dir function.​

But as I'm a very beginner to guess anything …​

​

Another way to create a folder via MakeSureDirectoryPathExists like in this thread :​

Create Folders & Sub-Folders as per cell of Excel

Dear Sir, This is my excel: Sr.No. Total Link loan Unique ID Loan_No Link Loan No. 2 Link Loan No. 3 Link Loan No. 4 8 4 9814829 1747399 13132286 5527073 2989781 10 4 9808897 9865324 3712775 13290211 1161743 1 2 524444 6477143 2 1 9353504 6431728 3 2 9808823 8386959...

www.mrexcel.com

​

 

[Pinaceous]

Hi MarcL!

I've tried out your link with the codes posted there and the

 Private Declare PtrSafe Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal DirPath$) As Boolean

but I'm still getting popped an error on the drive path.

My code in its entirety is as follows:

Sub CreateFolders()

    Dim FSO As Object, sourceFolder As Object, file As Object

  Dim sPath As String, sMain As String, sFolder As String
  Dim i As Long

    Set FSO = CreateObject("Scripting.FileSystemObject")
    sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    sMain = sPath & "\" & ThisWorkbook.Sheets(1).Range("B11").Value
     
  If Dir(sMain, vbDirectory) <> "" Then 'if the folder exists
  'Do nothing
    MsgBox "Duplicate Folders!"
   
  Exit Sub
 
  Else ' 'if the folder not exists
      MkDir sMain
        FSO.copyFile Source:="F:\MISC\1-2-STEP.WADS.SYS.DOCS SHARE DRIVE POSTED\IATS_FYS\FY4701ALS\IMPORT.pdf", Destination:=sMain & "\IMPORT.pdf"
     
        FSO.copyFile Source:="F:\MISC\1-2-STEP.WADS.SYS.DOCS SHARE DRIVE POSTED\IATS_FYS\FY4701ALS\als1.xlsx", Destination:=sMain & "\als1.xlsx"
     
        FSO.copyFile Source:="F:\MISC\1-2-STEP.WADS.SYS.DOCS SHARE DRIVE POSTED\IATS_FYS\FY4701ALS\Sending.pdf", Destination:=sMain & "\Sending.pdf"
     
        FSO.MoveFile Source:="C:\Users\ZGFFXW4\Desktop\FY 2022 WADS T-1 UTB" & Sheets(1).Range("E25").Value, Destination:=sMain & "\" & Sheets(1).Range("E25").Value

        FSO.MoveFile Source:="C:\Users\ZGFFXW4\Desktop\FY 2022 WADS T-1 UTB" & Sheets(1).Range("E31").Value, Destination:=sMain & "\" & Sheets(1).Range("E31").Value

        FSO.MoveFile Source:="C:\Users\ZGFFXW4\Desktop\FY 2022 WADS T-1 UTB" & Sheets(1).Range("E37").Value, Destination:=sMain & "\" & Sheets(1).Range("E37").Value


    End If
   
Set FSO = Nothing

 For i = 11 To 23
    sFolder = sMain & "\" & Sheets(1).Range("C" & i).Value
       
    If Dir(sFolder) = "" Then
      MkDir sFolder
    End If
  Next


End Sub

Here the code begins to create a folder and adds the files in just perfectly but it errors starting on line:

     FSO.MoveFile Source:="C:\Users\ZGFFXW4\Desktop\FY 2022 WADS T-1 UTB" & Sheets(1).Range("E25").Value, Destination:=sMain & "\" & Sheets(1).Range("E25").Value

where it is suppose to then create subfolders based upon a reference a cell in my workbook.

Any suggestions??

Thanks,
Pin!

 

[Pinaceous]

PS. Do you think or I thought it had to do with my FileSystemObjects Move Files with VBA FileSystemObject (MoveFile) - Automate Excel

But apparently it was not the case.