Cancel code based on cancel in browser window

[Design by Sue]

I am using the following function found on the web to open a browser window to allow the user to select the location for a download of an excel file. All is working correctly EXCEPT if the user clicks the cancel button on the browser window the code that follows runs anyway. How can I cancel the following code if the cancel button is selected in the browser window?



Public Function GetFolderName(Optional OpenAt As String) As String
Dim lCount As Long

GetFolderName = vbNullString

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = OpenAt
.Show
For lCount = 1 To .SelectedItems.Count
GetFolderName = .SelectedItems(lCount)
Next lCount
End With
End Function

Private Sub ExportExcel_Click()
GetFolderName

DoCmd.OutputTo acOutputReport, "SOP30DayRPT", acFormatXLS, "SOP30DayRPT - " & Format(Date, "mmddyyyy") & ".xls"

End Sub


The screenshot shows the cancel button in the browser window that I am talking about.

 

[Gasman]

Not sure why you are looping, but Getfolder name will either be Null or ZLS if Cancel is selected.
Why not walk the code and find out what is returned?
I do not see where you are using it for the OutputTo anyway?

 

[Design by Sue]

Not sure why you are looping, but Getfolder name will either be Null or ZLS if Cancel is selected.
Why not walk the code and find out what is returned?
I do not see where you are using it for the OutputTo anyway?

I have to admit I don't know why this works but by putting the GetFolderName in the output code this seems to work. I did not write the code for these but adapted what I found on line.

So if I understand your reply I can put an if statement in the GetFolderName function and if Null or ZLS then exit sub else continue?

 

[The_Doc_Man]

Your code uses .SHOW as a trigger, but it actually does more than that. It is a function that returns TRUE (or -1) if the user clicked a file. Test the value of .SHOW before doing anything else.

 

[Gasman]

Well I checked on Google.

FileDialog.Show method (Office)

Office VBA reference topic

learn.microsoft.com

How do I get a single file name out of a File Dialog object in VBA (for MS Access 2007)?

How do I change my code to get the file name instead of the directory name? openDialog.InitialFilename gives me the directory name. openDialog.FileName gives me the error "Method or data member not...

stackoverflow.com

 

[Design by Sue]

Your code uses .SHOW as a trigger, but it actually does more than that. It is a function that returns TRUE (or -1) if the user clicked a file. Test the value of .SHOW before doing anything else.

 

[Design by Sue]

That is a screenshot of what is displayed when I select Show - and it stays that way no matter what I do, cancel or select the downloads folder

 

[Gasman]

Public Function GetFolderName(Optional OpenAt As String) As String
    Dim lCount As Long
    Dim strFolderName As String
    'GetFolderName = vbNullString

    strOpenAt = "F:\Temp\"
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = OpenAt

        '.Show
        If .Show = 0 Then
            MSGBOX "Cancel was selected"
            Exit Function
        End If

        For lCount = 1 To .SelectedItems.Count
            strFolderName = .SelectedItems(lCount)
        Next lCount
    End With
    GetFolderName = strFolderName
End Function

 

[Design by Sue]

Well I checked on Google.

FileDialog.Show method (Office)

Office VBA reference topic

learn.microsoft.com

How do I get a single file name out of a File Dialog object in VBA (for MS Access 2007)?

How do I change my code to get the file name instead of the directory name? openDialog.InitialFilename gives me the directory name. openDialog.FileName gives me the error "Method or data member not...

stackoverflow.com

Tried that code and it does not work, it does not end the code if the cancel is pushed.

 

[Design by Sue]

Public Function GetFolderName(Optional OpenAt As String) As String
    Dim lCount As Long
    Dim strFolderName As String
    'GetFolderName = vbNullString

    strOpenAt = "F:\Temp\"
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = OpenAt

        '.Show
        If .Show = 0 Then
            MSGBOX "Cancel was selected"
            Exit Function
        End If

        For lCount = 1 To .SelectedItems.Count
            strFolderName = .SelectedItems(lCount)
        Next lCount
    End With
    GetFolderName = strFolderName
End Function

Thanks - getting closer now.

 

[Gasman]

You still have to use it in your output file?

 

[Design by Sue]

Thanks - getting closer now.

Thank you for providing the code - that is really helpful but one more question and I am sure this is something I should be able to see but how do I end the code at the MsgBox. My DoCmd.OutputTo acOutputReport, "SOP30DayRPT", acFormatXLS, "SOP30DayRPT - " & Format(Date, "mmddyyyy") & ".xls" still runs

 

[Gasman]

I would test to see if anything is returned by GetFolderName.
No need for msgbox, that was just there to show the logic.

 

[Design by Sue]

Tried adding the strCancel and referencing it in my ExcelReport code - both locations it shows True but still skips over the exit sub and runs the code... Not sure what I need to do.


Public Function GetFolderName(Optional OpenAt As String) As String
Dim lCount As Long
Dim strFolderName As String
Dim strCancel As String
'GetFolderName = vbNullString

strOpenAt = "F:\Temp\"
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = OpenAt

'.Show
If .Show = 0 Then
'MsgBox "Cancel was selected"
strCancel = True
Exit Function

End If

For lCount = 1 To .SelectedItems.Count
strFolderName = .SelectedItems(lCount)
Next lCount
End With
GetFolderName = strFolderName


End Function




Private Sub ExportExcel_Click()
GetFolderName

If strCancel = True Then
Exit Sub
End If
DoCmd.OutputTo acOutputReport, "SOP30DayRPT", acFormatXLS, "SOP30DayRPT - " & Format(Date, "mmddyyyy") & ".xls"

End Sub

 

[Design by Sue]

Tried adding the strCancel and referencing it in my ExcelReport code - both locations it shows True but still skips over the exit sub and runs the code... Not sure what I need to do.


Public Function GetFolderName(Optional OpenAt As String) As String
Dim lCount As Long
Dim strFolderName As String
Dim strCancel As String
'GetFolderName = vbNullString

strOpenAt = "F:\Temp\"
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = OpenAt

'.Show
If .Show = 0 Then
'MsgBox "Cancel was selected"
strCancel = True
Exit Function

End If

For lCount = 1 To .SelectedItems.Count
strFolderName = .SelectedItems(lCount)
Next lCount
End With
GetFolderName = strFolderName


End Function




Private Sub ExportExcel_Click()
GetFolderName

If strCancel = True Then
Exit Sub
End If
DoCmd.OutputTo acOutputReport, "SOP30DayRPT", acFormatXLS, "SOP30DayRPT - " & Format(Date, "mmddyyyy") & ".xls"

End Sub

I tried changing the strCancel=0 and thought it was working but now I can't get the code to run, it only exits sub,

 

[Design by Sue]

I would test to see if anything is returned by GetFolderName.
No need for msgbox, that was just there to show the logic.

If the user selects a folder the GetFolderName does show that folder

I am just not getting referencing the GetFolderName correctly in the ExportExcel code

 

[Gasman]

StrCancel is a string not boolean, so how can it be True? Plus it is a local variable so out of scope in excel proc. Plus you have not even declared it there, so looks like you do not have Option Explicit set?
Try what I suggested.

 

[Design by Sue]

StrCancel is a string not boolean, so how can it be True? Plus it is a local variable so out of scope in excel proc. Plus you have not even declared it there, so looks like you do not have Option Explicit set?
Try what I suggested.

Yeah - I know I am picking at this because I don't know what to do. What are you referring to as "Try what I suggested" I think I have been doing that but I am sure i missed something

 

[Design by Sue]

Yeah - I know I am picking at this because I don't know what to do. What are you referring to as "Try what I suggested" I think I have been doing that but I am sure i missed something

If you are referring to your statement
"I would test to see if anything is returned by GetFolderName.
No need for msgbox, that was just there to show the logic."

I know that the GetFolderName "GetFolderName = strFolderName" shows the folder I selected. And if I don't do anyting about the cancel the file is saved to the correct location. (sorry I did added the Option Explicit)

 

[Gasman]

Walk the code with F8 and see what is returned from GetFolder.
It is a Function, so should return the Folder selected or nothing?