Creating Browse File Input Box

[nick83]

I have just finished a fairly simple VB code to convert data sourced from a txt. file into a predefined format in order for it to be run through a secondary program.
Currently I am simply pasting the data in the the first box and then running the macro, however I would like to create a user form that enables me to either click on a button and browse for the txt. file or a drop down menu that lists the txt. files in a fixed directory. With the selected txt. file, I would like it to be directly pasted into the active work sheet and the macro to start. Is any of this possible? This is my first successfully script in VB, but just wanna keep adding parts to make it even better! I have pasted it below if it is needed:

Cheers



Sub data_import()

'Variables

Dim VDCount As Integer

Dim ECount As Integer

Dim ICount As Integer

Application.ScreenUpdating = False

'Code

ActiveWorkbook.Sheets("data-sheet").Activate

Range("A2").Select

Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1))

Range("M1").Select

Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

DCount = Worksheets("Processing").Range("A2").Value
ECount = Worksheets("Processing").Range("A5").Value
ICount = Worksheets("Processing").Range("A8").Value

'this deletes the rows with D

Rows("2:" & (DCount + 1)).Select
Selection.Delete Shift:=xlUp

'select es

Range("A2:T" & (ECount + 1)).Select

Selection.Copy
Sheets("UPDATE").Select
Range("A2").Select
ActiveSheet.Paste

CutCopyPasteMode = False


'Check right number of rows are selected here + insert formulas at top
Range("U2:AA2").Select
Selection.Copy
Range("U2:AA" & (ECount + 1)).Select
ActiveSheet.Paste

Application.Calculate

CutCopyPasteMode = False

Range("U2:AA" & (ECount + 1)).Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:T").Select
Range("T1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select

CutCopyPasteMode = False

Cells.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Selection.NumberFormat = "@"

'Processing I's

Worksheets("data-sheet").Activate

Range("A" & (ECount + 2) & ":T" & ((ECount + 1) + (ICount))).Select
Selection.Copy

Worksheets("CREATE").Activate
Range("A2").Select
ActiveSheet.Paste

CutCopyPasteMode = False

Range("U2:AJ2").Select
Selection.Copy
Range("U3:AJ" & (ICount + 1)).Select
ActiveSheet.Paste

Application.Calculate

CutCopyPasteMode = False

Range("U2:AJ" & (ICount + 1)).Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:T").Select
Range("T1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

Cells.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Selection.NumberFormat = "@"

Range("A1").Select

MsgBox ("Processing Completed for: " & (DCount + ECount + ICount) & "rows")

'Workbooks("--theworkbook'").Worksheets("--yousheet--").Range("--therange--").Value = (DCount + ECount + ICount)

End Sub

 

[Norie]

Why not use Application.GetOpenFileName? No need to go and create your own userform.

 

[nick83]

Thanks for the tip have done following and appears to work - really simple question - when the first part (opening the file) is finished how do you get the macro to continue to complete the rest?



Sub Autpen()

MsgBox ("Click OK to continue when Ready")

NewFN = Application.GetOpenFilename(FileFilter:="txt.Files (*.txt), *.txt", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "No File Selected"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If

?????????????????

End Sub

Sub data_import()

'Variables

Dim VDCount As Integer

Dim ECount As Integer

Dim ICount As Integer

Application.ScreenUpdating = False

'Code

ActiveWorkbook.Sheets("data-sheet").Activate