Setting a variable to equal whats in a textbox

[rstanton1]

I have a User form that I primary use as criteria for a variety of queries, but now I need to export a file.

I currently use a texbox to tell the TransferSpreasheet method through a Macro what name to give the spreadsheet as it gets exported. I would like to now open the newly-created spreadsheet in Excel.

The problem is that the variable strFileName never captures the .text property.

Public Function OpenMacroFromExcel()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Call GetFileName

Set xlApp = New Excel.Application

With xlApp
.Visible = True
Set xlWB = .Workbooks.Open(strfilename)
End With
Workbooks.Open
xlWB.Application.Run "Automate_Regression"

Set xlWB = Nothing
Set xlApp = Nothing

End Function

Sub GetFileName()
dim strFileName as String
strFileName = forms!test!txtfilename.text
End Sub

Can anyone assist me?

 

[bat17]

assuming that forms!test!txtfilename is an Access form you probably don't need the .text.


GetFileName() is a sub not a function so It wont return anything anyway.

To return a value from a function you need to refer back to its name
GetFileName = forms!test!txtfilename

and you can do this without the function in the first place


Set xlWB = .Workbooks.Open(forms!test!txtfilename)

Though I would probably put it into a variable first so that it could be checked for validity as a file name.

Peter