how can I create the folders based on month and date? For example, C:\Users\Users\download\0101,......\0102,...... \0103....etc... however, without any weekend....
@prakharaug
3 жыл бұрын
Thanks Marcus for the video helped a lot ❤❤
@thesmallman
3 жыл бұрын
Most welcome. :)
@taiwan4440
3 жыл бұрын
if I would like to download the excel files automatically via vba, how could I do? However, the pages of files can be downloaded manually with pull-down menu. Even with different files, they always show the same website address for each files....
@Norlen94
3 жыл бұрын
Hi Great video. I have a question. Is it possible instead of a predefined directory and range, the user gets to select where the folders would be created and the folders themselves would be based on the cells the user selected? more of a dynamic code.
@thesmallman
3 жыл бұрын
Thanks - I assume you mean you have a list of directory names and you want to have this work based on selection. Make a dummy file put Jul, Aug Sep in consecutive cells say in A2,A3 and A4. Put the following code in a regular module Sub test() Dim cell As Range For Each cell In Selection MkDir "C:\Test\" & cell.Value Next cell End Sub Make sure you have a folder called Test in your c drive. Or if you want to put the entire path in the cell in the cells have C:\Test\Jul in A2 C:\Test\Aug in A3 and C:\Test\Sep in A4 and the following: Sub test1() Dim cell As Range For Each cell In Selection MkDir cell.Value Next cell End Sub Be sure to select the cells before you start the process. It will work. :)
@Norlen94
3 жыл бұрын
@@thesmallman I used the code below which works perfectly. Sub MakeFolders() Dim diaFolder As FileDialog Dim cell As Range Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker) diaFolder.AllowMultiSelect = False diaFolder.Show fle = diaFolder.SelectedItems(1) Range("L1") = fle Set diaFolder = Nothing For Each cell In Application.Selection MkDir fle & "\" & cell.Value Next cell MsgBox "Folders created" Range("L1").Clear End Sub
@robertaamoribello3702
3 жыл бұрын
Hi Marcus, many thanks for your tips i found it very helful; actually I need a bit of an help because i need to add two more features: this is the code i create thanks to you: Sub MakeFolder() Dim i As Integer Dim str As String Dim fol As String For i = 1 To 1000 str = TextBox1.Text & Range("A" & i) & "\" fol = Dir(str, vbDirectory) If fol = "" Then MkDir str Next i End Sub The only difference is that i wanted to indicate the folder in a separate TextBox so i can always choose where to save the file. I changed the range to "1 to "1000" My question is: How i can manage to create a subfolder with the the data in column B? Many thanks Roberta
@jasonschmidt5534
3 ай бұрын
Hey Marcus, I have a code to create a file based on cell value, however I want to have a directory create by same cell value and the file in the directory. How would I integrate directory creation into the code. Private Sub CommandButton2_Click() 'Show the Save As dialog to allow folder to be chosen Dim FileName As Variant Dim ValCellB2 As String Dim Path As String ValCellB2 = Range("B2").Value Path = "B:\Blend Chemist Data\Profile Approval\" FileName = Application.GetSaveAsFilename(Path + ValCellB2 + ".xlsm", _ "Excel Workbook,*.xlsm", 1, "Confirm or Edit filename and folder!") 'Oops, the user backed out of this so don't save the file If TypeName(FileName) = "Boolean" Then MsgBox "You didn't save your file!" Else 'Ok, all is good, we have the details, so go ahead and save the workbook ActiveWorkbook.SaveAs FileName MsgBox "File Saved!" End If End Sub
@thesmallman
2 ай бұрын
Hi Jason I had a play round with it and got it working OK. What I did was put the new folder name and the new file name in cell B2. B2 is: MyFile Name\My Excel File So for example this might be: January\Jan Budget Data Then I just run the following and it produces a new folder called January with a file inside called Jan Budget data.xlsx All ever so good. Good luck with it - worked nicely at my end. Sub CreateFolder() Dim Path As String Dim Fname As String Dim ar() As String ar = Split([b2], "\") Path = "B:\Blend Chemist Data\Profile Approval\" Fname = Path & ar(0) If ar(0) "" And Not FolderExists(Fname) Then MkDir Fname End If ActiveWorkbook.SaveAs Path & ar(0) & ar(1) MsgBox "File Saved!" End Sub Function FolderExists(ByVal Path As String) As Boolean On Error Resume Next FolderExists = (GetAttr(Path) And vbDirectory) = vbDirectory On Error GoTo 0 End Function
Пікірлер: 10