Pass Argument?
 

I need to pass the file path and name select by the cmdBrowse_Click sub into
the Open_Data_File sub when the cmdOK_Click sub is fired.

How do I fix the code below to accomplish this?

Thanks,
Hal

Option Explicit

Private Sub cmdBrowse_Click()

Dim FileToOpen As Variant
FileToOpen = Application _
.GetOpenFilename("DAT Files (*.dat), *.dat")
If FileToOpen = False Then
txtFileToOpen.Value = ""
Else
txtFileToOpen.Value = FileToOpen
End If
cmdOK.Enabled = True
cmdOK.SetFocus

End Sub


Private Sub cmdOK_Click()
Open_Data_File
End Sub



Sub Open_Data_File()

Workbooks.OpenText Filename:=FileToOpen, Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:A").Select
Selection.ColumnWidth = 8.43
Range("A1").Select

End Sub

Pass Argument?
 

You can create a global variable to hold the file name, just define your
FileToOpen variable at the top of the module, before any Subs or Functions,
like so:
Private FileToOpen as String

If the code you have is in more than one module, though, you need to make it
Public:
Public FileToOpen as String

Private variables keep their values and can be used by any code in the
module they are in; Public allows them to be used in any module in your
project.
--
- K Dales


"Hal" wrote:

I need to pass the file path and name select by the cmdBrowse_Click sub into
the Open_Data_File sub when the cmdOK_Click sub is fired.

How do I fix the code below to accomplish this?

Thanks,
Hal

Option Explicit

Private Sub cmdBrowse_Click()

Dim FileToOpen As Variant
FileToOpen = Application _
.GetOpenFilename("DAT Files (*.dat), *.dat")
If FileToOpen = False Then
txtFileToOpen.Value = ""
Else
txtFileToOpen.Value = FileToOpen
End If
cmdOK.Enabled = True
cmdOK.SetFocus

End Sub


Private Sub cmdOK_Click()
Open_Data_File
End Sub



Sub Open_Data_File()

Workbooks.OpenText Filename:=FileToOpen, Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:A").Select
Selection.ColumnWidth = 8.43
Range("A1").Select

End Sub