Use a text value as a variable in VBA

[danniemcq]

Is it possible to have a text box on a form that can be referenced as a variable in a strpath command?

at the minute i have this in my module

strPath = "C:\Users\USER\ Documents\Datab ase Creations\CTR\"

what i would like is
strPath = "inputform.fold erlocation.valu e"

this would mean the user wouldn't have to mess with code and i wouldn't have to fix issues in the future.

I have considered using browse to folder but this is only a sample, there are many other values that might need updating.

Below is the full code i am using, any questions or suggestions would be great!

Thanks

Function DoImport()


Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
Dim strWorksheets(1 To 7) As String

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file (this code assumes that each worksheet
' with the same name is being imported into a separate table
' for that specific worksheet name)
Dim strTables(1 To 7) As String

' Replace generic worksheet names with the real worksheet names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
strWorksheets(1) = "Audit"
strWorksheets(2) = "ERROR"
strWorksheets(3) = "FAILED"

' Replace generic table names with the real table names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
strTables(1) = "Audittable"
strTables(2) = "ERRORtable"
strTables(3) = "FAILEDtable"


' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users\USER\Documents\Database Creations\CTR\"

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
For intWorksheets = 1 To 7



      strFile = Dir(strPath & "*.xlsx")
      Do While Len(strFile) > 0
            strPathFile = strPath & strFile
                On Error Resume Next
            DoCmd.TransferSpreadsheet acImport, _
                  acSpreadsheetTypeExcel12, strTables(intWorksheets), _
                  strPathFile, blnHasFieldNames, _
                  strWorksheets(intWorksheets) & "$"
                      On Error GoTo 0
            strFile = Dir()
      Loop

Next intWorksheets

End Function

[Seth Schrock]

You can assign the value in a textbox to a variable, which I think is what you are asking.

If code is in the form's module that contains the textbox

strFile = Me.Textbox_Name

If the code is in some other module or class module

strFile = Forms!Form_Name!Textbox_Name