Cancel code based on cancel in browser window

[Design by Sue]

Here is what I am getting if I select a folder.

 

[Gasman]

Ok, and what are you going to do with that?
Why not show what it is when you get back to the Excel sub?

 

[Design by Sue]

Ok, and what are you going to do with that?
Why not show what it is when you get back to the Excel sub?

I don't know what to do --- that is as always my problem. I just know that the code works to save it to the correct folder but will not cancel when cancel is clicked. I do not understand at all how it is working because there is nothing showing in the Excel sub that displays the folder name.

 

[Gasman]

Yes, I told you that.
You need to check what is returned by the GetFolderName.
If it is empty, then do not try and output the report.
If it is not empty, then you have a folder you can use. You still need to check that the outfilename AND path is correct as well.

Take it step by step with F8 and breakpoints, so you can understand what the code is doing.

 

[Design by Sue]

I am not sure how to get the information returned from the GetFolderName. If I understand what you are saying I would thinks something like this but I can't figure out the If statement. Am I getting closer?



Private Sub ExportExcel_Click()

If GetFolderName() = Null Then
Exit Sub
Else

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

End Sub

 

[Gasman]

StrPath = GetFolderName() in your excel sub. ???

 

[Design by Sue]

Thank you (again!) I think this works now


Private Sub ExportExcel_Click()

Dim strPath As String

strPath = GetFolderName()
If strPath = "" Then
Exit Sub
Else

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

 

[Gasman]

Almost there. You are still not using it in the outputto command.
Also you need to check the path AND filename are what you think they are.

 

[Design by Sue]

Yeah in testing it I am finding the path is not where I think it is! It is in a directory one level up. Dang it - Will see what I can find on outputto command, but can you give me a hint on how to check the path and file name. The Excel code has a export name within it (or at least that is what it is saving as.)

 

[Gasman]

The path coming back from the function does not have a trailing backslash, so you need to add that.
You then need to concatenate that with your report name in a similar manner as you do with the date, but obviously goes before the report name.

If unsure, set it all up in a string variable that you can debug.print to see what you have and then just use that in the OutputTo command.

 

[Gasman]

You can also supply a starting path, by putting that path in the function call.

 

[Design by Sue]

The path coming back from the function does not have a trailing backslash, so you need to add that.
You then need to concatenate that with your report name in a similar manner as you do with the date, but obviously goes before the report name.

If unsure, set it all up in a string variable that you can debug.print to see what you have and then just use that in the OutputTo command.

Excellent help - I am leaving for the day so will be back to this on Wednesday. Will research how to do this, nice to know what I need to look up. Have a great evening and thanks for hanging in there with me.

 

[The_Doc_Man]

I will add a side comment. You have a FOR loop screen-shot in post #21 and it looks at a property of selected items. However, you should be aware that it starts from 1 to the .Count and the problem is that Access collections (such as but not limited to .SelectedItems) start counting from 0, i.e. zero-based numbering. If you start from .SelectedItems(1), you have SKIPPED the item you wanted to catch, which is .SelectedItems(0).

 

[Design by Sue]

I will add a side comment. You have a FOR loop screen-shot in post #21 and it looks at a property of selected items. However, you should be aware that it starts from 1 to the .Count and the problem is that Access collections (such as but not limited to .SelectedItems) start counting from 0, i.e. zero-based numbering. If you start from .SelectedItems(1), you have SKIPPED the item you wanted to catch, which is .SelectedItems(0).

Thanks will look at this when I get back - I copied the code from a website so it is the way it came to me. Appreciate the comment!

 

[Gasman]

I will add a side comment. You have a FOR loop screen-shot in post #21 and it looks at a property of selected items. However, you should be aware that it starts from 1 to the .Count and the problem is that Access collections (such as but not limited to .SelectedItems) start counting from 0, i.e. zero-based numbering. If you start from .SelectedItems(1), you have SKIPPED the item you wanted to catch, which is .SelectedItems(0).

That was not the case when I tested it Doc?
Then again there was only one item, as I set multiselect to false, but found you still need the loop, or as I saw on the links, hard code as 1.

Just changed to hardcoded. I selected T2 in the Temp folder

 

[Design by Sue]

Found a little time to poke a this.
The

Dim strPath As String
Dim strGoToPath As String

strPath = GetFolderName()
Debug.Print strPath
strGoToPath = "strPath\SOP30DayRPT - " & Format(Date, "mmddyyyy") & ".xls"
Debug.Print strGoToPath


strPath in the debug does show the correct path but I am not clear how to get the correct code for strGoToPath
the results are
(strPath)\SOP30DayRPT - 08262024.xls

Can you help me with how to refer to strPath in the strGoToPath? Not sure how to format this. I have tried all kinds of combinations but can't find the solution and can't find the answer onlne.

thanks

 

[Design by Sue]

Found a little time to poke a this.
The

Dim strPath As String
Dim strGoToPath As String

strPath = GetFolderName()
Debug.Print strPath
strGoToPath = "strPath\SOP30DayRPT - " & Format(Date, "mmddyyyy") & ".xls"
Debug.Print strGoToPath


strPath in the debug does show the correct path but I am not clear how to get the correct code for strGoToPath
the results are
(strPath)\SOP30DayRPT - 08262024.xls

Can you help me with how to refer to strPath in the strGoToPath? Not sure how to format this. I have tried all kinds of combinations but can't find the solution and can't find the answer onlne.

thanks

Sorry - I think I found the solution

Here is the final excel sub as I have it now and it seems to be working

Private Sub ExportExcel_Click()

Dim strPath As String
Dim strGoToPath As String

strPath = GetFolderName()
'Debug.Print strPath
strGoToPath = strPath & "\" & "SOP30DayRPT - " & Format(Date, "mmddyyyy") & ".xls"
'Debug.Print strGoToPath

If strPath = "" Then
Exit Sub
Else


DoCmd.OutputTo acOutputReport, "SOP30DayRPT", acFormatXLS, strGoToPath
End If


Once again thanks for your help. If you see anything wrong please if you could let me know I would appreciate it.

 

[Design by Sue]

I know that there is a way to put the GetFolderFuncton somewhere so that it is available and can be referenced on all reports so it does not have to be included on each report but I can't remember where to put it. Module? Macro?

 

[Gasman]

Sorry - I think I found the solution

Here is the final excel sub as I have it now and it seems to be working

Private Sub ExportExcel_Click()

Dim strPath As String
Dim strGoToPath As String

strPath = GetFolderName()
'Debug.Print strPath
strGoToPath = strPath & "\" & "SOP30DayRPT - " & Format(Date, "mmddyyyy") & ".xls"
'Debug.Print strGoToPath

If strPath = "" Then
Exit Sub
Else


DoCmd.OutputTo acOutputReport, "SOP30DayRPT", acFormatXLS, strGoToPath
End If


Once again thanks for your help. If you see anything wrong please if you could let me know I would appreciate it.

I would not even try to assemble strGoToPath until I know strPath has a value.
So swap around the test and the setting of strGoToPath.

 

[Gasman]

I know that there is a way to put the GetFolderFuncton somewhere so that it is available and can be referenced on all reports so it does not have to be included on each report but I can't remember where to put it. Module? Macro?

Module as Public.