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