Get Folder location from folder picker and save as string

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
89
Hi,

I'm trying to add the location that user picks as a string so I can use the same path to export reports to but having trouble doing so. Please look at the VBA below. I'm just trying to save the user selected location to 'sFolder' and then use rloc to have the same value. Please see line 18.

VBA Code:
Option Compare Database

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim rsSQL As String
Dim hsql As String
Dim bsql As String
Dim gid As String
Dim gbor As String
Dim rloc As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim fsql As String

DoCmd.SetWarnings False

DoCmd.OpenQuery "qRawMT"
fsql = "ALTER TABLE tRaw ADD COLUMN ****ingID COUNTER;"
DoCmd.RunSQL fsql


rsSQL = "SELECT DISTINCT tRaw.GroupID, tRaw.Borrower FROM tRaw;"
rloc = sFolder
Set rs = CurrentDb.OpenRecordset(rsSQL)
rs.MoveFirst

DoCmd.OutputTo acOutputReport, "rGroupBilling", acFormatPDF, rloc & gbor & " " & gdt & ".pdf"
   
    Pause (5)
   
    rs.MoveNext
Loop

Me.lblIntro.Visible = False
Me.lblReady.Visible = True
Me.lblReady2.Visible = False
'DoCmd.DeleteObject acTable, "tRaw"

DoCmd.SetWarnings True

End Sub


Private Sub Command12_Click()
'Declare a variable as a FileDialog object.
Dim sFolder As String
'Create a FileDialog object as a File Picker dialog box.
With Application.FileDialog(msoFileDialogFolderPicker)
'Declare a variable to contain the path of each selected item.

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then
        sFolder = .SelectedItems(1)
        End If
    End With
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JonXL

Active Member
Joined
Feb 5, 2018
Messages
497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I spot a few problems here - like trying to reference sFolder from two different routines - , but what is it exactly that isn't working about the code (do you get an error, etc.)?

Some more specifics on what's not happening as you expect will help narrow the troubleshooting effort.
 

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
89
I see..Thanks for spotting that. Let me give you some background on this.

There is a form with 2 buttons. Browse Folder and Generate Pdf's.

Users will click on the 'Browse folder' button, pick the folder location and click 'ok. This location is saved to the 'sFolder' string.
VBA Code:
sFolder = .SelectedItems(1)

I just would like that location to be saved to the 'rloc' string.
VBA Code:
rloc = sFolder

Then users would click Generate pdf's and it should generate to the location they selected.
VBA Code:
DoCmd.OutputTo acOutputReport, "rGroupBilling", acFormatPDF, rloc & gbor & " " & gdt & ".pdf"

Hope I was clear.

Thank you
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
VBA Code:
sFolder = .SelectedItems(1)

I just would like that location to be saved to the 'rloc' string.
VBA Code:
rloc = sFolder

Maybe there is a better way, but I handle this by putting the file location returned from the file selection dialogue into a field on the form (make it invisible if you want, but I don't recommend that...). Then you used the field's value for the variable in the other function.

VBA Code:
sFolder = .SelectedItems(1)
Me.txtField.Value = sFolder

VBA Code:
rloc = Me.txtField.Value
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,580
Messages
5,770,976
Members
425,654
Latest member
Kotika

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top