Connecting two path strings to get the final path?

Question

I'm trying to save excel file into a specific path. So basically, when I click the button, I'm creating a folder, and want to save the file inside that folder. The created folder has the current month as name. I'm trying to save into that current month folder.

    'Create folder as Month Name. Save filename as date inside "month".
    Dim sDate As String = DateTime.Now.ToString("yyyy-MM-dd") & "_" & DateTime.Now.ToString("HH-mm-ss")
    Dim sMonth As String = DateTime.Now.ToString("MMMM")
    Dim sFolder = Application.StartupPath & "\Resources\Excel\"


    My.Computer.FileSystem.CreateDirectory(sFolder & Format(sMonth))

    Dim sfinal = Path.Combine(sFolder, sMonth)
    xlSh.SaveAs(sfinal & Format(sDate) & ".xlsx")

    xlApp.Workbooks.Close()
    xlApp.Quit()

As it is, this code doesn't give me any errors. But instead of creating a folder named "March" <-current month and saving inside it, it saves the file in \Excel\ and it also creates folder in the same place.

Solution 1

After long hours of excruciating pain, I've finally did it! Apparently I was missing an "\" Since "sMonth" became dynamic name, which later I wanted to use as path, and save files in that folder. I needed to simply put that "\" after sMonth, to tell it to save inside it.

Before I realize this... I've broken down, simplified the code as much as I could so I can logically connect the pieces. What I ended up with, is something slightly different. Now the SaveAS properly saves the file inside the new folder.

    Dim sDate As String
    sDate = DateTime.Now.ToString("yyyy-MM-dd") & "_" & DateTime.Now.ToString("HH-mm-ss")

    Dim sMonth As String
    sMonth = DateTime.Now.ToString("MMMM")

    Dim sFileName As String
    sFileName = sDate + ".xlsx"

    Dim sFolder As String
    sFolder = Application.StartupPath & "\Resources\Excel\"


    Dim sfinal As String
    sfinal = (sFolder & sMonth & "\") '<- this thingie here o.O

    My.Computer.FileSystem.CreateDirectory(sFolder & Format(sMonth))

    xlSh.SaveAs(sfinal & Format(sFileName))

    xlApp.Workbooks.Close()
    xlApp.Quit()

Thanks for the help.

Solution 2

you could use the following function (similar to .NET System.IO.Path.Combine)

Function PathCombine(path1 As String, path2 As String)

    Dim combined As String

    combined = path1
    If Right$(path1, 1) <> Application.PathSeparator Then
        combined = combined & Application.PathSeparator
    End If
    combined = combined & path2
    PathCombine = combined

End Function

Hope this helps!

Comments

[Taffs]

Trust me, I've tried... I tried so many combinations while referencing to the line you posted, and I couldn't get it working...

[Robert Wertz]

Can you run it again and have it print out the values of each variable at the time each variable is used? That way we can see what is being input into the system.

[Taffs]

Don't know. We do know where the problem is... It's this code: Dim sfinal = Path.Combine(sFolder, sMonth) xlSh.SaveAs(sfinal & Format(sDate) & ".xlsx") I just don't know of any other way to SaveAs

[Robert Wertz]

Glad you got it working! I was asking for the output as I was wondering about that ending slash, but sounds like you got it on your own, congrats!

[Taffs]

Oh, on a another thingie.... How to check if directory exists simply save the file, if it doesn't, then create the folder and save the file inside it?

[Robert Wertz]

stackoverflow.com/questions/15480389/… Something like IF Dir(sFolder & Format(sMonth), vbDirectory) = "" Then My.Computer.FileSystem.CreateDirectory(sFolder & Format(sMonth)) xlSh.SaveAs(sfinal & Format(sFileName)) Else xlSh.SaveAs(sfinal & Format(sFileName)) End If Hopefully that makes sense, I can't seem to separate the lines for the code haha

[Taffs]

Thanks. It works, although I modified it a bit. If Dir(sFolder, vbDirectory) = sMonth Then xlSh.SaveAs(sfinal & Format(sFileName)) Else My.Computer.FileSystem.CreateDirectory(sFolder & Format(sMonth)) xlSh.SaveAs(sfinal & Format(sFileName)) End If

[Robert Wertz]

Awesome! Always great to tailor others' code to your own needs.

[BurnsBA]

Do note that the above is only valid for excel (which the question is tagged with), in case you end up on this page (like me) searching for VBA path separator in powerpoint