Make directory or folder

[thart21]

I'm so close with this one but need a little assistance in creating a new folder in my directory based on the value selected in the combo box.


Private Sub cmdSeason_Click()
On Error GoTo Err_cmdSeason_Click


Dim stDocName As String
stDocName = "qSourceData_AllSeasons"
Const DownloadPath As String = "FilePathName\"

MkDir DownloadPath & Left(Me![cboSeason].Value, 2)
I have also tried Left(Forms!frmRunDP_worksheets!cboSeason.Value, 2)

'On Error GoTo ErrorRoutine

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qSourceData_AllSeasons", _
DownloadPath & Left(Me![cboSeason].Value, 2) & "\" & Left(Forms!frmRunDP_worksheets!cboSeason.Value, 2) & Right(Forms!frmRunDP_worksheets!cboSeason.Value, 2) & "_" & "Download" & ".xls"


Exit_cmdSeason_Click:
Exit Sub
Err_cmdSeason_Click:
MsgBox Err.Description
Resume Exit_cmdSeason_Click

End Sub

I'm getting a file/path error when running this one. the TransferSpreadsheet part works perfectly, just need to get the folder created if there isn't one (Have the If Else code ready to go)
Would appreciate any tips on getting this work.

Thanks
Toni

[thart21]

Think I got it so time to share

Private Sub cmdSeason_Click()
On Error GoTo Err_cmdSeason_Click
Dim stDocName As String
stDocName = "qSourceData_AllSeasons"
Const DownloadPath As String = "FilePathName\"

'Check to see if there is a folder for the Season selected in the drop down menu
'If not, create the folder, run query and export to the new folder

If Dir "FilePathName\"
& Left(Me![cboSeason].Value, 2) & Right(Me![cboSeason].Value, 2), vbDirectory) = "" Then
MkDir "FilePathName\"
& Left(Me![cboSeason].Value, 2) & Right(Me![cboSeason].Value, 2)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qSourceData_AllSeasons", _
DownloadPath & Left(Me![cboSeason].Value, 2) & Right(Me![cboSeason].Value, 2) & "\" & Left(Forms!frmRunDP_worksheets!cboSeason.Value, 2) & Right(Forms!frmRunDP_worksheets!cboSeason.Value, 2) & "_" & "Download" & ".xls"

Else

'If folder already exists, run query and export to existing Season folder

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qSourceData_AllSeasons", _
DownloadPath & Left(Me![cboSeason].Value, 2) & Right(Me![cboSeason].Value, 2) & "\" & Left(Forms!frmRunDP_worksheets!cboSeason.Value, 2) & Right(Forms!frmRunDP_worksheets!cboSeason.Value, 2) & "_" & "Download" & ".xls"

End If

MsgBox "File has been exported, Open Excel file?", vbYesNo


'DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_cmdSeason_Click:
Exit Sub
Err_cmdSeason_Click:
MsgBox Err.Description
Resume Exit_cmdSeason_Click

End Sub

Cheers!