I have been trying to get the below code to work with no success. The idea is to have a file "Form 41" saved in a centralized onedrive location and for it to be copied and saved to a location of a user's choice, selected with Application.FileDialog(msoFileDialogFolderPicker)
the file location picker works fine, but upon trying to execute the copy and save function, the file consistently errors out with an "Object Required" error, highlighting the line FileSystemObject.CopyFile File_Location, Destination_Location
as the issue. When i hover over the File_Location string and the Destination_Location string both appear to have successfully obtained a full, valid path. For example c:/users/test_user/downloads/41_form_25.pdf as the destination path and save name. I have even replaced the strings with the proper path locations pulled straight from the file start and end locations and placed those into the fso line and it still failed.
Full code below:
Private Sub Form_41_Download()
Dim File_Location As String
File_Location = Sheet2.Cells(1, 22) & "\Form_41\41_form_2025.pdf"
Dim Destination_Location As String
Dim GetFolder As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
'.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Destination_Location = sItem
FileSystemObject.CopyFile File_Location, Destination_Location
Set fldr = Nothing
End Sub
Thank you for all the help!
I have tried multiple configurations of the code, I have researched the issue online with no success, I have tried replacing variables with direct references to the ideal location.