Create new folder if folder does not exist

[DanSMT]

I'm struggling with this one. It continually errors out when attempt to create the dir.

Location of error is last line MKDir partsubfolder

Any assistance would be greatly appreciated. Thanks in advance!

Code below;

Dim sourceDir As String
Dim folder_exists As String
Dim partSubfolder As String

    'source directory
    sourceDir = "C:\Users\dans\documents\" & CustomerTB.Value & "\"
    partSubfolder = sourceDir & PartNoCB.Value & "\"
    
    'check if year subfolder exists, if it doesn't then create a new folder
    folder_exists = Dir(partSubfolder, vbDirectory)
    If folder_exists = vbNullString Then MkDir partSubfolder

 

[cmowla]

TY!

I would assume both of these subs can be ran under the same function?

Yes because Sub CreateDirectory is not "sensitive" to if there is a "\" at the end of the path or not. So to encompass both subs, you can do this:

If you mean "called" (with a Call statement . . . . sorry for the technicality), then absolutely.

        Sub Test__Move_This_File_From_This_Dir_to_This_Dir()

Dim sourcePath As String
Dim destinationPath As String
Dim fileName As String
sourcePath = "something"
destinationPath = "something"
fileName = "something" 'Include file extension, of course

Dim sourceDir As String
Dim folder_exists As String
Dim partSubfolder As String

'source directory
sourceDir = "C:\Users\dans\documents\" & CustomerTB.Value & "\"
partSubfolder = sourceDir & PartNoCB.Value & "\"

Call CreateDirectory(partSubfolder)
Call Move_This_File_From_This_Dir_to_This_Dir("ff.txt", sourcePath, destinationPath)
End Sub

Sub Move_This_File_From_This_Dir_to_This_Dir(fileName As String, source_Directory As String, destination_Directory As String)
'https://stackoverflow.com/a/16943127

Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
Call fso.MoveFile(source_Directory & "\" & fileName, destination_Directory & "\" & fileName)

End Sub

I get an error on the last line.

What is the ff.txt for?

Sorry, that should be fileName.

And I am working to answer your previous question. I will be a few more minutes.

 

[cmowla]

In this (generalized) example, I have "Start Folder" and "End Folder" (they are technically subfolder names):

Sub Test__2()

'File Name
    Dim fileName As String
    fileName = "something" 'Include file extension, of course

'Source directory
    Dim sourceDir As String
    Dim partSubfolder As String
    sourceDir = "C:\Users\dans\documents"
    partSubfolder = sourceDir & "\" & "Start Folder" 'There is no & "\" at the end

'Destination directory
    Dim destinationSubfolder As String

    sourceDir = "C:\Users\dans\documents"
    destinationSubfolder = sourceDir & "\" & "End Folder" 'There is no & "\" at the end

'Create a folder (and all subfolders implied to exist by partSubfolder) (And do the same for destinationSubfolder)
Call CreateDirectory(partSubfolder)
Call CreateDirectory(destinationSubfolder)

'Move
Call Move_This_File_From_This_Dir_to_This_Dir(fileName, partSubfolder, destinationSubfolder)

End Sub

 

[DanSMT]

figured out the issue.

I am using a listbox, not a textbox. ?

 

[cmowla]

figured out the issue.

I am using a listbox, not a textbox. ?

Do you mean Excel Table (Ctrl T) instead of normal range (cell)?

If so, then you just need ListBox (table) name.Range("cell address")

For example, if you have a table named "CustomerTB." from a sheet named "sheetName"

Excel Formula to LaTeX Print Converter 4.1.xlsb
	A	B	C	D	E
1	Column1	Column2	Column3	Column4	Column5
2
3
4
5
6
7
8
9
10
11
12			a
Sheet1

Then the following will display/get the text "a" from the table.

Sub Show_A_Cell_In_The_Specified_Table_In_The_Specified_SheetName()

Dim tbl As ListObject
Set tbl = Sheets("sheetName").ListObjects("CustomerTB.")

MsgBox tbl.Range(12, 3)

End Sub

 

[DanSMT]

yes that is correct. TY again.

 

[cmowla]

I can't see why you can't just replace CustomerTB (I wrote "CustomerTB." in the last post but I meant to write "CustomerTB", sorry, but it is okay to have "."'s in table names) with Sheets("sheetName").Range("C12").value (where "C12" is the position of where "a" is in the previous example) to get "a" (still reusing the previous example's Excel table) instead of having to do Sheets("sheetName").ListObjects("CustomerTB").Range(12,3).value (I forgot to put the .value in the last post too, but if you don't put that, it's the default property of Range().), but:

If you must based on what your code is doing, here is a little function I just wrote so that you can still input the original (individual/single) cell address and it will retrieve it from the table, no matter where the top-left corner of that table is located in the sheet. (If you offset the table in the previous example, you still have to have the (12,3) to still give you the value of "a", because the (12,3) is relative to the position of the table in the spreadsheet!)

(Again, in this example, the code will MsgBox "a".)

Excel Formula to LaTeX Print Converter 4.1.xlsb
	A	B	C	D	E	F	G	H
1
2
3
4
5
6				Column1	Column2	Column3	Column4	Column5
7
8
9
10
11
12
13
14
15
16
17						a
sheetName

Sub Test__Get_Val()
MsgBox Get_Val("F17", "CustomerTB", "sheetName")
End Sub
Function Get_Val(rangeAddress As String, tableName As String, sheetName As String)

Dim cellAddressRow As Long
cellAddressRow = Sheets(sheetName).Range(rangeAddress).Row

Dim cellAddressColumn As Integer
cellAddressColumn = Sheets(sheetName).Range(rangeAddress).Column

With Sheets(sheetName).ListObjects(tableName)
    Dim startRow As Long
    startRow = .DataBodyRange(1, 1).Row

    Dim startColumn As Integer
    startColumn = .DataBodyRange(1, 1).Column

    Get_Val = .Range(cellAddressRow - startRow + 2, cellAddressColumn - startColumn + 1).Value
End With

End Function