Thanks for the reply Steve.
I've copied your code into my project and set about testing whether it will work for me.
My plan is to use my file naming system - which I've already developed - with your user picks a folder code.
I've set up the two functions, and I've also created a new button for cmdExport_Click, however upon loading the form I get an error.
"The expression On Load you entered as the event property setting produced the following error: User-defined type not defined."
If I comment out the BrowseFolderExplorer function then the error is no longer there, I'm not sure what the issue is to be honest.
EDIT: Turns out I forgot to add reference for "Microsoft Office 16.0 Object Library".
EDIT2:
Alright, I've got it working, sort of. There are some issues. Let me describe what's happening.
1. I press the create report button.
2. "Select a Folder" dialog box pop's up. Current folder (where the project is saved) is already automatically filled in. For me this is "WIP" since my directory is
C:\Users\John.Doe\OneDrive - Company\Desktop\WIP
3. I press "OK" to save.
4. I get an error message pop-up. It says
"Select a Folder
WIP
Path does not exist.
Check the path and try again."
5. I press OK to dismiss the popup.
6. I use the explorer to manually travel to WIP folder.
7. I press OK to save
8. I get a message saying:
"Microsoft Access
Done!!
The fine was saved as: C:\Users\John.Doe\OneDrive - Company\Desktop\WIP[Test Lot Report] AOT - Lot 2 20220805_103917.xls"
9. I press OK to dismiss the popup message
10. I check the WIP folder, and there is no report saved there.
11. I check the desktop and the report is there named "WIP[Test Lot Report] AOT - Lot 2 20220805_103917.xls"
It seems that instead of assigning the file to WIP folder, it set that as the first part of the file name instead.
So the two issues here are that
A) It's not letting me save until I refresh the file path, even though it's entered by default.
B) It's saving the file to the parent folder, of the one selected.
C) It's appending the selected folder "WIP" to the start of the file name.
Any insight or suggestions on how to solve this?
Here is how I have utilized the code, perhaps it will offer some insight, perhaps not.
Code:
Function BrowseFolderExplorer(Optional DialogTitle As String, _
Optional ViewType As MsoFileDialogView = _
MsoFileDialogView.msoFileDialogViewdetails, _
Optional InitialDirectory As String) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' BrowseFolderExplorer
' This provides an Explorer-like Folder Open dialog.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
fDialog.InitialView = ViewType
With fDialog
If Dir(InitialDirectory, vbDirectory) <> vbNullString Then
.InitialFileName = InitialDirectory
Else
.InitialFileName = CurDir
End If
.Title = DialogTitle
If .Show = True Then
' user picked a folder
BrowseFolderExplorer = .SelectedItems(1)
Else
' user cancelled
BrowseFolderExplorer = vbNullString
End If
End With
End Function
Public Function UnqualifyPath(sPath As String) As String
'Qualifying a path involves assuring that its format
'is valid, including a trailing slash, ready for a
'filename. Since SHBrowseForFolder will not pre-select
'the path if it contains the trailing slash, it must be
'removed, hence 'unqualifying' the path.
If Len(sPath) > 0 Then
If Right$(sPath, 1) = "\" Then
UnqualifyPath = Left$(sPath, Len(sPath) - 1)
Exit Function
End If
End If
UnqualifyPath = sPath
End Function
Private Sub Command143_Click()
Dim sPath As String
Dim strSaveFileName As String
' Me.Text1.Value = CurrentProject.Path
'The call can not have a trailing slash, so
'strip it from the path if present
sPath = UnqualifyPath((CurrentProject.Path)) '<<-- initial folder path
sPath = BrowseFolderExplorer("Select a Folder", msoFileDialogViewPreview, sPath)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Report 1 hold the name of the query, used for assigning data source to the subforms - DC 1 June 2022
Dim Report1 As String
Report1 = "unionReport_TestLot_AllSw"
'Report1Desc holds the description aka the name of the file once the excel file is published
'here you want to add stuff like, ALL, Current, Non-Current, Date, SW Type, Parent system to make each excel file uniqely named and self descriptive
'DC 2 June 2022
Dim Report1Desc As String
'Report1Name is what you use in the button code to export the specific query to a location and assign it a name - DC 2 June 2022
'It is a concoctination of both File Location AND Report (1-4) description
Dim Report1Name As String
Dim FileLocation As String 'This is the SAVE LOCATION, it has to be defined in code if you want the reports to automatically be named via code - DC 2 June 2022
FileLocation = "C:\Users\John.Doe\OneDrive - Company\Desktop\REPORTS\"
'Here the code assigns multiple variables to the ReportDesc, which is the name of the excel file
'------------------------------------------------------------------------------- START
Dim editedDate As String 'This assigns todays date to the end of the filename
editedDate = Format$(Now(), "yyyymmdd_hhnnss")
Dim CurrentTestLot As String 'This assigns the name of the spec to the filename
CurrentTestLot = Me.ComboL1.Column(1)
Dim CurrentConfig As String 'this assigns All/Current/Non-Current to the filename, based on checkbox selection
If Me.OptionFrame = 3 Then
CurrentConfig = "Current AND Non-Current"
Else
If Me.OptionFrame = 2 Then
CurrentConfig = "Non-Current"
Else
CurrentConfig = "Current"
End If
End If
Dim CurrentAssetType As String 'This assigns SW Asset type to the filename (Such as SW, DC, EC, VM or ALL)
CurrentAssetType = "ALL-Software"
Report1Desc = "[Test Lot Report] " & CurrentTestLot & " " & CurrentConfig & " " & CurrentAssetType & " " & editedDate
Report1Name = FileLocation & Report1Desc & ".xlsx"
'------------------------------------------------------------------------------- END
'This is the code which selects the query, and exports it as an excel file. It actiates when you press the button
'This code also names the file, and assigns the file to a specific, predefined location - DC 3 June 2022
Dim selectedReport As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
strSaveFileName = sPath & Report1Desc & ".xls"
DoCmd.OutputTo acOutputQuery, Report1, acFormatXLS, strSaveFileName, False
'DoCmd.OutputTo acOutputQuery, Report1, acFormatXLS, Report1Name, True
MsgBox "Done!!" & vbCrLf & vbCrLf & "The file was saved as: " & strSaveFileName
End Sub
EDIT3:
I Changed
Code:
strSaveFileName = sPath & Report1Desc & ".xls"
to
Code:
strSaveFileName = sPath & "\" & Report1Desc & ".xls"
Now the file saves in the correct selected folder, rather than the parent.
It seems a trailing slash was important in getting the file to save in the correct folder.
Problem B) and C) are solved.