Results 1 to 7 of 7

Thread: [RESOLVED] VB: Making folder and saving workbook into that folder?

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2017
    Posts
    11

    Resolved [RESOLVED] VB: Making folder and saving workbook into that folder?

    Hi all

    i am having some issues saving an Excel workbook after creating a folder, not sure how to save the workbook after the folder has been created

    At the moment I click save button and it will create the folder and save the file in the same destination. How do I get it to save the file in the folder it creates?

    So I need it to:
    Make folder here
    C:\Users\User\Documents\Shipments\2017\Jan01

    and

    Save file into Jan01


    Code:
    Sub Makefoldersaver()
    
    'Creates New Folder
    
    Dim FSO
    Dim sFolder As String
    Dim Ref
    Ref = Sheets("Input").Range("T4").Value
    
    sFolder = "C:\Users\User\Documents\Shipments\2017\" & Sheets("Input").Range("T4").Value
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    If Not FSO.FolderExists(sFolder) Then
    FSO.CreateFolder (sFolder) 'Checking if the same Folder already exists
    
    Else
    MsgBox Ref & " Folder Already Exists", vbExclamation, "Folder Already Exists!"
    End If
    
    Dim Filename As String
    Dim Path As String
    
    Application.DisplayAlerts = False
    
    Path = "C:\Users\User\Documents\Shipments\2017\"
    Filename = Range("T6").Value & ".xlsx"
    ActiveWorkbook.SaveAs Path & Filename, xlOpenXMLWorkbook
    
    Application.DisplayAlerts = True
    
    ActiveWorkbook.Close
    
    End Sub
    A guy made some of the this before me who left so I am just adding to it, think I need a course!

    Thanks guys

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VB: Making folder and saving workbook into that folder?

    try like
    Code:
    ActiveWorkbook.SaveAs sfolder & Filename, xlOpenXMLWorkbook
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2017
    Posts
    11

    Re: VB: Making folder and saving workbook into that folder?

    Quote Originally Posted by westconn1 View Post
    try like
    Code:
    ActiveWorkbook.SaveAs sfolder & Filename, xlOpenXMLWorkbook
    Thanks, I needed to add another line so it would save in the folder created

    sFolder2 = "C:\Users\User\Documents\Shipments\2017\" & Sheets("Input").Range("T4").Value & "\" & Sheets("Input").Range("T5").Value

    and

    ActiveWorkbook.SaveAs FileName:=sFolder2 & Ref

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: VB: Making folder and saving workbook into that folder?

    Thread moved to Office Development, since the question regards VBA.
    My usual boring signature: Nothing

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VB: Making folder and saving workbook into that folder?

    sfolder was already the name of the new folder, but i should have put a \ between

    Code:
    ActiveWorkbook.SaveAs sfolder & "\" & Filename, xlOpenXMLWorkbook
    if you have a satisfactory answer, pls mark thread resolved
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    New Member
    Join Date
    Jan 2017
    Posts
    11

    Re: [RESOLVED] VB: Making folder and saving workbook into that folder?

    Hi all,, following on from the aboce

    I have a macro to create a folder, name it from a certain cell and save the workbook to it. How can I get the folder to be named and read from another cell to include another name in the folder title IF that other cell has a name in it?

    At the moment it does....
    Read Cell (e.g.) Cell A1 (Shipment1)
    Make folder with name from A1
    Save Workbook into Toby folder
    Done

    What I would like... IF Cell A2 contains information....

    Read Cell (e.g.) Cell A1 (Shipment1) & A2 (Tom)
    Make folder with name from A1 & A2 - Shipment1 Tom
    Save Workbook into Toby folder

    How do I make the IF info?

    Thanks

  7. #7
    New Member
    Join Date
    Dec 2016
    Posts
    7

    Re: [RESOLVED] VB: Making folder and saving workbook into that folder?

    From how it sounds, you can actually always use A1 & A2 without If clause - if A2 is empty, A1 & A2 will default to A1.

    If you still want the IF clause, it would be quite simple.

    Code:
    If Range("A2").Value = "" Then
        sFolderName = Range("A1").Value
    Else
        sFolderName = Range("A1").Value & Range("A2").Value
    End If
    Unless I'm missing something?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width